The Oracle (tm) Users' Co-Operative FAQ

When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ?


Author's name: Mark D. Powell

Author's Email: Mark Powell@eds com [add periods for spaces]

Date written: 27 August 2003

Revised        26 February 2004

Oracle version(s): 9.2

When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ?

Back to index of questions


In the absence of the online option Oracle usually does read the existing index, but rather than read the index in key order which would use single block index IO, which in turn could be very slow for a large index, a design decision was made to perform the rebuild operation by reading the index blocks sequentially using multi-block reads.  This logic is a fast full index scan, FFS, and can be seen by running an explain plan on the alter index rebuild command.  My tests on 8.1.7.4 and 9.2.0.1 produced the same plan for unique and non-unique indexes of very small (16K) to reasonably large size (2.6G).  Oracle access plans are subject to change with each dot release as Oracle improves its code, but the basic answer that the reason appears to be a design decision based on overall performance considerations probably will not change.

 
        Explain plan for: alter index rebuild xxx.item_trans_log_idx4
 
              COST CARDINALITY QUERY_PLAN
        ---------- ----------- --------------------------------------------------
              1204     2919826  ALTER INDEX STATEMENT
                                 2.1 INDEX BUILD NON UNIQUE ITEM_TRANS_LOG_IDX4
                       2919826     3.1 SORT CREATE INDEX
              1204     2919826      4.1 INDEX FAST FULL SCAN ITEM_TRANS_LOG_IDX4
                                 NON-UNIQUE
 
 
When the online option is added to the rebuild command my testing shows that Oracle implements the rebuild using a full table scan.
 
        Explain plan for: alter index rebuild xxx.item_trans_log_idx4 online
 
              COST CARDINALITY QUERY_PLAN
        ---------- ----------- --------------------------------------------------
              5879     2919826  ALTER INDEX STATEMENT
                                2.1 INDEX BUILD NON UNIQUE ITEM_TRANS_LOG_IDX4
                       2919826     3.1 SORT CREATE INDEX
              5879     2919826       4.1 TABLE ACCESS FULL ITEM_TRANS_LOG
 
 

Another consideration is that an Index Fast Full Scan can only be used on an index where at least one of the columns is defined as NOT NULL.  Otherwise it is up to the CBO to choose the access plan that it believes is best.  In versions 8i and 9i it would appear that a full table scan is the preferred method when a FFS is not available.  Also a full table scan has to be used in situations where the index is invalid such as immediately after an alter table move is performed.

I would like to thank Mark J. Bobak for reviewing this article.

Lewis, J. (2001). Practical Oracle 8i, Chapter 7: Enhanced Indexing. Upper Saddle River: NJ. Addison Wesley. pp. 128-129.

Oracle Corporation (2003, December). Oracle Database Performance Tuning Guide 10g, Ch 16, Using Indexes and Clusters, Section Label: Re-creating Indexes and Ch 14, The Query Optimizer, Section Label: Fast Full Index Scans.

For those with access to metalink at metalink.oracle.com the following notes might be of interest:

How to Rebuild and Index                                                                   Note 232819.1

SCRIPT: Move indexes from one tablespace to another                       Note 131959.1

Guidelines on When to Rebuild a B-Tree Index                                    Note 77574.1


Back to top

Back to index of questions