|
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 ? |
|
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_idx4COST 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 onlineCOST 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