현상
Index명을 별도로 명시하지 않은 경우, index range scan 힌트를 지정하고 index range scan이 가능한 Index가 있음에도 다른 Index를 통한 index fast full scan으로 처리되는 현상 입니다.
SQL> SELECT /*+INDEX_RS(A)*/ *
FROM TEST_RS A
WHERE 1=1
AND IX_COL_1 IN ('N','F')
AND ROWNUM <=5000
AND PK_COL_2 > TO_CHAR(SYSDATE-31,'YYYYMMDD');
0 row selected.
SQL ID: 6mu243bn13wb1
Child number: 1412
Plan hash value: 2008546442
Execution Plan
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Cost:7344, %%CPU:6, Rows:1)
2 PARTITION RANGE (SUBSET PART) (Cost:7344, %%CPU:6, Rows:1) (PS:KEY, PE:12)
3 TABLE ACCESS (ROWID): TEST_RS (Cost:7344, %%CPU:6, Rows:1)
4 FILTER (Cost:7343, %%CPU:6, Rows:1)
5 INDEX (FAST FULL SCAN): TEST_RS_PK (Cost:6858, %%CPU:0, Rows:902412)
Predicate Information
--------------------------------------------------------------------------------
3 - filter: (("A"."IX_COL_1") IN (('F'),('N'))) (1.000)
4 - filter: ("A"."PK_COL_2" > TO_CHAR((SYSDATE - 31),'YYYYMMDD')) (0.000)
NAME VALUE
------------------------------ ----------
db block gets 10
consistent gets 36
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 5
rows processed 0
Execution Stat
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Time:0. ms, Rows:0, Starts:1)
2 PARTITION RANGE (SUBSET PART) (Time:.02 ms, Rows:0, Starts:1)
3 TABLE ACCESS (ROWID): TEST_RS (Time:0. ms, Rows:0, Starts:5)
4 FILTER (Time:.01 ms, Rows:0, Starts:5)
5 INDEX (FAST FULL SCAN): TEST_RS_PK (Time:.1 ms, Rows:0, Starts:5)
SQL> SELECT /+INDEX_RS(A(IX_COL_1))/ *
FROM TEST_RS A
WHERE 1=1
AND IX_COL_1 IN ('N','F')
AND ROWNUM <=5000
AND PK_COL_2 > TO_CHAR(SYSDATE-31,'YYYYMMDD');
0 row selected.
SQL ID: fgv1msjfdbxb8
Child number: 1413
Plan hash value: 1845789918
Execution Plan
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Cost:731223, %%CPU:0, Rows:1)
2 TABLE ACCESS (ROWID): TEST_RS (Cost:731223, %%CPU:0, Rows:1)
3 INLIST ITERATOR (Cost:0, %%CPU:0, Rows:902412)
4 PARTITION RANGE (SUBSET PART) (Cost:8799, %%CPU:0, Rows:902412) (PS:KEY, PE:12)
5 INDEX (RANGE SCAN): TEST_RS_IX (Cost:8799, %%CPU:0, Rows:902412)
Predicate Information
--------------------------------------------------------------------------------
2 - filter: ("A"."PK_COL_2" > TO_CHAR((SYSDATE - 31),'YYYYMMDD')) (0.000)
5 - access: ("A"."IX_COL_1" = :0) (1.000)
NAME VALUE
------------------------------ ----------
db block gets 0
consistent gets 36
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 5
rows processed 0
Execution Stat
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 5001) (Time:0. ms, Rows:0, Starts:1)
2 TABLE ACCESS (ROWID): TEST_RS (Time:0. ms, Rows:0, Starts:1)
3 INLIST ITERATOR (Time:0. ms, Rows:0, Starts:1)
4 PARTITION RANGE (SUBSET PART) (Time:.01 ms, Rows:0, Starts:2)
5 INDEX (RANGE SCAN): TEST_RS_IX (Time:.02 ms, Rows:0, Starts:10)
SQL
복사
원인
•
optimizer 내부에서 iscan 생성 시 dd 상에서 index rs를 수행할 수 없는 Index가 먼저 나오고
그 뒤에 index rs를 수행할 수 있는 Index가 나온 경우, rs를 수행할 수 없는 Index로 index fast fullscan 등 다른 것도 생성하여 발생하는 현상 입니다.
•
create index IX를 우선적으로 수행하고 create index PK 수행 시 해당 현상은 발생하지 않습니다.
그러나 먼저 create index PK 수행 후 create index IX 수행 시, PK로 fast full scan이 한번 수행되고
IX로 rs 힌트 적용 후 PK를 지워야 하나 cost 경쟁을 시키는 상황이 존재하게 됩니다.
해결
패치를 적용해 해결합니다. (적용패치: 309150)
주의
티맥스티베로에서 제공하는 기술지원을 통해 패치를 적용합니다.
참고
회피책으로서, INDEX_RS(A(IX_COL_1))와 같이 인덱스 명까지 같이 지정하면 의도한 Index 수행이 가능하며, Index 생성 순서로 조절할 수 있습니다.