In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided fora table).
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not.
We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort.
For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
The optimizer may decide that selecting all the information from the index
and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting.
An Index full scan will perform single block i/o's and so it may prove to be
inefficient.
e.g.
Index BE_IX is a concatenated index on big_emp (empno,ename)
SQL> explain plan for
select empno,ename from big_emp order by empno,ename;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]
Partager