|
안녕하세요.
버전 (11.9.2)
같은 쿼리인데 아큐먼트에 따라서 index가 다른걸 탈 수 있나요?
예를 들어서
saa010ms 이 테이블에는 index 가 3개 있습니다. saa010ms_pk(clustered, unique) agency_cd, saa010ms_nk1(nonclustered) headquarter, team_major
select *
from sca010ts a, sca010ds b, saa010ms d
where a.order_no = b.order_no
and a.del_order_sign_yn = 'N'
and d.headquarter = 'A'
and d.team_major like 'A%'
and a.agency_cd = d.agency_cd
Query Plan
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
sca010ts
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
saa010ms
d
Nested iteration.
Using Clustered Index.
Index : saa010ms_pk
Forward scan.
Positioning by key.
Keys are:
agency_cd ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
sca010ds
b
Nested iteration.
Using Clustered Index.
Index : sca010ds_pk
Forward scan.
Positioning by key.
Keys are:
order_no ASC
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
--------------------------------------------------------------
select *
from sca010ts a, sca010ds b, saa010ms d
where a.order_no = b.order_no
and a.del_order_sign_yn = 'N'
and d.headquarter = 'J'
and d.team_major like 'A%'
and a.agency_cd = d.agency_cd
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
FROM TABLE
saa010ms
d
Nested iteration.
Index : saa010ms_nk1
Forward scan.
Positioning by key.
Keys are:
headquarter ASC
team_major ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
sca010ts
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
sca010ds
b
Nested iteration.
Using Clustered Index.
Index : sca010ds_pk
Forward scan.
Positioning by key.
Keys are:
order_no ASC
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
두번째 쿼리가 많이 늦어서 확인해 보니깐, index를 틀리게 타고 있드라구요.
이런게 되는 원인이 무엇인지 궁금합니다.
|
