|
다음과 같은 쿼리가 있는데 order by 절을 줬을때와
주지 않았을때의 실행계획이 전혀 틀립니다.
단순히 생각해서는 order by 절을 주지 않으면 더 빠를것 같은데...
INF_TB_CDDL index IDX_1(REALNO)
CRD_TB_BRCH index CRD_TB_BRCH_IK1(CQNO)
-------------------------------------------------------------
* 개인번호를 이용해 해당 사업장 전체에 대한 연체
정보를 구하는 query 입니다.
SELECT ARR_OCCDAY , STAT, ARR_CLRDAY
FROM HInfo..INF_TB_CDDL
WHERE REALNO IN ( SELECT '999' + SAUPNO + '00'
FROM SIMSA..CRD_TB_BRCH
WHERE CQNO = '12345678912300')
order by ARR_OCCDAY
-------------------------------------------------------------
1. order by ARR_OCCDAY 을 줬을때 실행계획
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.
FROM TABLE
SIMSA..CRD_TB_BRCH
Nested iteration.
Index : CRD_TB_BRCH_IK1
Forward scan.
Positioning by key.
Keys are:
CQNO 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.
FROM TABLE
HInfo..INF_TB_CDDL
Nested iteration.
Index : IDX_1
Forward scan.
Positioning by key.
Keys are:
REALNO 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.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
This step involves sorting.
FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
The sort for Worktable1 is done in Serial
|ARR_OCCDAY|STAT|ARR_CLRDAY|
|----------|----|----------|
|20060905 |9 |20060906 |
Execution Time 0.
SQL Server cpu time: 0 ms. SQL Server elapsed time: 56 ms.
2. order by 절을 주지 않았을때
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
HInfo..INF_TB_CDDL
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 4 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
SIMSA..CRD_TB_BRCH
EXISTS TABLE : nested iteration.
Index : CRD_TB_BRCH_IK1
Forward scan.
Positioning by key.
Keys are:
CQNO 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.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
|ARR_OCCDAY|STAT|ARR_CLRDAY|
|----------|----|----------|
|20060905 |9 |20060906 |
Execution Time 2278.
SQL Server cpu time: 227800 ms. SQL Server elapsed time: 463290 ms.
위결과를 비교해 보면 order by 절을 주었을때 CRD_TB_BRCH 먼저 드라이빙 되면서 인덱스를 탄후
INF_TB_CDDL 테이블의 인덱스를 타서 결과가 빨리 나오는것 같은데
order by 절을 안주었을때는 INF_TB_CDDL 을 먼저 드라이빙 되면서 Table Scan 을 합니다.
여기서는 우연히 order by 절을 사용하게 되어 1번의 결과로
사용하고 있는데 2번째 결과에 대한 시간이 너무 많이 걸립니다.
다른곳에도 where 절의 in 을 사용할 경우 subquery 로 수행하게 되면 이런 현상이 자주 발생하는것 같은데, 고수님들의 설명 부탁드립니다.
1. order by 절을 주었을때와 안주었을테 index 타는게 틀리는지
2. where절의 in을 사용할 경우 index를 타는건지 안타는건지?
(subquery 를 사용할 경우 결과값이 2개 이상일경우
in 을 사용할경우)
꾸벅~~~
|
