Document Type | Troubleshooting
Category | Tuning
Applicable Product Versions | 6FS01, 6FS02, 6FS03, 6FS04, 6FS05, 6FS06, 6FS07, 7FS01, 7FS02
Document Number | TTUTS005
Issue
When the index name is not specified separately, and an index range scan hint is given, even if there is an Index capable of performing an index range scan, the process is done through an index fast full scan by another Index. This is the phenomenon.
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)
Cause
- When generating iscan inside the optimizer, if an Index that cannot perform index range scan on dd appears first, and an Index that can perform index range scan appears after, this phenomenon occurs because the optimizer generates other methods such as index fast full scan for the Index that cannot perform range scan.
- If create index IX is executed first and then create index PK is executed, this phenomenon does not occur. However, if create index PK is executed first and then create index IX is executed, a fast full scan on PK is performed once, and after applying the rs hint on IX, PK must be removed, but a situation arises where cost competition occurs.
Solutions
Apply the patch to resolve the issue. (Applied patch: 309150)
CautionApply the patch through technical support provided by Tmax Tibero.
NoteAs a workaround, if you specify the index name as well, such as INDEX_RS(A(IX_COL_1)), the intended Index execution is possible, and you can control it by the order of index creation.