아래의 두가지 쿼리는 같은 쿼리인데 from절의 테이블 순서만 바꾼것인데요.
query plan은 다르게 풀려서 문의드립니다. 아시는 데로 설명해 주시면 업무에 도움이 될 것
같네요.
참고)
총 테이블 수는 4개이며, 3개는 Proxy Table이고 1개는 Local Table입니다.
첫번째 테이블이 메인테이블이며, 나머지 3개 테이블은 Reference 성격의 테이블 입니다.
== query1 ==
SELECT A.mgt_no
, A.pln_dt
, A.recp_dt
, A.int_from
, A.int_to
, A.int_rate
, A.cur_unit
, A.apl_exch_rate
, A.int_amt
, A.int_amt_won
, A.cop_tax
, A.cop_tax_won
, A.inc_int
, A.inc_int_won
, A.slip_no
, C.slip_no
, C.dept_cd
, D.sosok_nm dept_nm
, B.depo_no
, B.unit_cd
, isnull(A.work_ty,'hand') work_ty
FROM
om_u_fu_intpln A
, om_u_fu_depom B
, u_ac_sliph C
, om_u_c_sosok D
WHERE A.pln_dt between '20060101' and '20060630'
AND A.recp_dt is not null
AND A.recp_dt <> ''
AND A.mgt_no = B.mgt_no
AND A.slip_no = C.slip_no
AND C.dept_cd = D.sosok_cd
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
....... Executing Partial Query Remotely
....... Server Name: SYBASE17
....... Text of generated SQL:
....... SELECT T1."mgt_no", T1."pln_dt", T1."recp_dt", T1."int_from", T1."int_to", T1."int_rate
........... ", T1."cur_unit", T1."apl_exch_rate", T1."int_amt", T1."int_amt_won", T1."cop_tax
........... ", T1."cop_tax_won", T1."inc_int", T1."inc_int_won", T1."slip_no", T1."work_ty",
........... T2."mgt_no", T2."depo_no", T2."unit_cd" FROM account.dbo."u_fu_intpln" T1 , account
........... .dbo."u_fu_depom" T2 WHERE T1."mgt_no" = T2."mgt_no" AND T1."recp_dt" <> @p1 AND
........... T1."recp_dt" <> @p2 AND T1."pln_dt" <= @p3 AND T1."pln_dt" >= @p4
FROM TABLE
u_ac_sliph
C
Nested iteration.
Index : pk_ac_sliph
Forward scan.
Positioning by key.
Keys are:
slip_no ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
....... Executing Partial Query Remotely
....... Server Name: SYBASE17
....... Text of generated SQL:
....... SELECT "sosok_cd", "sosok_nm" FROM gongtong.dbo."u_c_sosok" WHERE "sosok_cd" = @p1
[0 row(s) returned (application specific)]
(0 row(s) affected)
== query 2 ==
SELECT A.mgt_no
, A.pln_dt
, A.recp_dt
, A.int_from
, A.int_to
, A.int_rate
, A.cur_unit
, A.apl_exch_rate
, A.int_amt
, A.int_amt_won
, A.cop_tax
, A.cop_tax_won
, A.inc_int
, A.inc_int_won
, A.slip_no
, C.slip_no
, C.dept_cd
, D.sosok_nm dept_nm
, B.depo_no
, B.unit_cd
, isnull(A.work_ty,'hand') work_ty
FROM
om_u_fu_intpln A
, om_u_fu_depom B
, om_u_c_sosok D
, u_ac_sliph C
WHERE A.pln_dt between '20060101' and '20060630'
AND A.recp_dt is not null
AND A.recp_dt <> ''
AND A.mgt_no = B.mgt_no
AND A.slip_no = C.slip_no
AND C.dept_cd = D.sosok_cd
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
....... Executing Partial Query Remotely
....... Server Name: SYBASE17
....... Text of generated SQL:
....... SELECT T1."mgt_no", T1."pln_dt", T1."recp_dt", T1."int_from", T1."int_to", T1."int_rate
........... ", T1."cur_unit", T1."apl_exch_rate", T1."int_amt", T1."int_amt_won", T1."cop_tax
........... ", T1."cop_tax_won", T1."inc_int", T1."inc_int_won", T1."slip_no", T1."work_ty",
........... T2."mgt_no", T2."depo_no", T2."unit_cd", T3."sosok_cd", T3."sosok_nm" FROM account
........... .dbo."u_fu_intpln" T1 , account.dbo."u_fu_depom" T2 , gongtong.dbo."u_c_sosok" T3
........... WHERE T1."mgt_no" = T2."mgt_no" AND T1."recp_dt" <> @p1 AND T1."recp_dt" <> @p2
........... AND T1."pln_dt" <= @p3 AND T1."pln_dt" >= @p4
FROM TABLE
u_ac_sliph
C
Nested iteration.
Index : pk_ac_sliph
Forward scan.
Positioning by key.
Keys are:
slip_no ASC
Using I/O Size 4 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
|