Skip to main content

Search

Welcome to Tibero GTS!

Index Fast Full Scan Occurrence (When Index Name is Not Specified)

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" &gt; 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" &gt; 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)
Caution
Apply the patch through technical support provided by Tmax Tibero.
Note
As 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.
 
 
 

Related to