Problem solve Get help with specific problems with your technologies, process and projects.

Oracle SQL and index internals: Index rebuilds

The following is the 11th part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization.

The following is the 11th part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss. Check back to the main series page for upcoming installments.

Index rebuilds

In an OLTP system, index space is often greater than the space allocated for tables, and fast row data access is critical for sub-second response time. Oracle offers a wealth of index structures:

  • B-tree indexes. This is the standard tree index that Oracle has been using since the earliest releases.
  • Bitmap indexes. Bitmap indexes are used where an index column has a relatively small number of distinct values (low cardinality). These are super-fast for read-only databases, but are not suitable for systems with frequent updates.
  • Bitmap join indexes. This is an index structure whereby data columns from other tables appear in a multi-column index of a junction table. This is the only create index syntax to employ a SQL-like from clause and where clause.
create bitmap index
     inventory( parts.part_type, supplier.state )
     inventory i,     parts         p,     supplier   s
In addition to these index structures we also see interesting use of indexes at runtime. Here is a sample of index-based access plans:
  • Nested loop joins. This row access method scans an index to collect a series of ROWIDs.

  • Index fast-full-scans. This is a "multi-block read" access where the index blocks are accessed via a "db file scattered read" to load index blocks into the buffers. Please note that this method does not read the index nodes.

  • Star joins. The star index has changed in structure several times, originally being a single-concatenated index and then changing to a bitmap index implementation. STAR indexes are super-fast when joining large read-only data warehouse tables.

  • Index combine access. This is an example of the use of the index_combine hint. This execution plan combines bitmap indexes to quickly resolve a low-cardinality Boolean expression:
select /*+ index_combine(emp, dept_bit, job_bit) */
     job = 'SALESMAN'
     deptno = 30
Here is the execution plan that shows the index combine process:
OBJECT_NAME                                       POSITION
------------------------------ ---------------------------- ----------
BY INDEX ROWID                                     EMP              
           BITMAP AND
               BITMAP INDEX
SINGLE VALUE                                     DEPT_BIT
               BITMAP INDEX
SINGLE VALUE                                     JOB_BIT

Inside Oracle b-tree indexes

There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing debate about rebuilding of indexes for improving performance. Some experts claim that periodic rebuilding of Oracle b-tree indexes greatly improves space usage and access speed, while other experts maintain that Oracle indexes should "rarely" be rebuilt. Interestingly, Oracle reports that the new Oracle10g Automatic Maintenance Tasks (AMT) will automatically detect indexes that are in need of re-building. Here are the pros and cons of the issue:

  • Arguments for Index Rebuilding. Many Oracle shops schedule periodic index rebuilding, and report measurable speed improvements after they rebuild their Oracle b-tree indexes.

  • Arguments against Index Rebuilding. Some Oracle in-house experts maintain that Oracle indexes are super-efficient at space re-use and access speed and that a b-tree index rarely needs rebuilding. They claim that a reduction in Logical I/O should be measurable, and if there were any benefit to index rebuilding, someone would have come up with "provable" rules.

Index information

The dba_indexes view is populated with index statistics when indexes are analyzed. The dba_indexes view contains a great deal of important information for the SQL optimizer, but there is still more to see. Oracle provides an analyze index xxx validate structure command that provides additional statistics into a temporary table called index_stats. But, the information needs to be saved, as each analyze validate structure command overlays the information.

To get the full picture, you need both pieces. Also, there are certainly some columns that are more important than others:

  • CLUSTERING_FACTOR. This is one of the most important index statistics because it indicates how well sequenced the index columns are to the table rows. If clustering_factor is low (about the same as the number of dba_segments.blocks in the table segment) than the index key is in the same order as the table rows and index range scan will be very efficient, with minimal disk I/O. As clustering_factor increases (up to dba_tables.num_rows), the index key is increasingly out of sequence with the table rows. Oracle's cost-based SQL optimizer relies heavily upon clustering_factor to decide whether or not to use the index to access the table.

  • HEIGHT. As an index accepts new rows, the index blocks split. Once the index nodes have split to a predetermined maximum level the index will "spawn" into a new level.

  • BLOCKS This is the number of blocks consumed by the index. This is dependent on the db_block_size. In Oracle9i and beyond, many DBAs create b-tree indexes in very large blocksizes (db_32k_block_size) because the index will spawn less. Robin Schumacher has noted in his book Oracle Performance Troubleshooting notes:
    "As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database."
  • PCT_USED. This metric is very misleading because it looks identical to the dba_indexes pct_used column, but has a different meaning. Normally, the pct_used threshold is the freelist unlink threshold, while in index_stats pct_used is the percentage of space allocated in the b-tree that is being used.

Are there criteria for index/table rebuilding?

The short answer is no, there is no 100% complete, definitive list. But, here are some things to start with:

  • Index levels > 3
  • Pct_used < 75%
  • More than 20% of the rows have been deleted (space is not automatically reused)
  • Index is becoming unclustered, and performance is degrading (causing increases in number of blocks to be read) – while unloading, resorting, and reloading the data in a table may provide better performance, this is an additional maintenance activity that needs to be performed, and can be difficult to keep the rows in their proper sequence, if the table gets a lot of insert/update/delete activity.

Go to the main series page.

About the author

Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.