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:
In addition to these index structures we also see interesting use of indexes at runtime. Here is a sample of index-based access plans:
Here is the execution plan that shows the index combine process:
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:
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
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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:
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:
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.
