First page Back Continue Last page Graphics

Index Range Scan

Notes:

With an index range scan, multiple ROWID’s are retrieved within the range specified in the condition. Oracle will traverse the b-tree index, find the correct starting leaf block, and pass ROWID’s to the table access step.
The CBO cost calculation for index range scans is:
cost= blevel + (ff * leaf blocks) + (ff * cluf)
Where:
blevel is the number of levels in the index.
ff is the filter factor of the index. This can be seen in the 10053 event trace; see the IX_SEL field.
cluf is the clustering factor for the index. In addition to the data dictionary, this can be found in the 10053 event trace; see the CLUF field
As the second example above illustrates, the index range scan can be used to avoid a sort. Without the ORDER BY, CBO chooses a full scan:
SQL> select * from big2 where object_id between 0 and 15000;
Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=234 Card=5
1 0 TABLE ACCESS (FULL) OF 'BIG2' (Cost=234 Card=595