Indexes, the good, the bad and the ugly - John Garmany

Important 2009 update: The rules for identification of candidates for index rebuilding are changing. Please see my updated notes on index rebuilding and note how to tell when an index will benefit from scheduled oracle index rebuilding.

Col. John Garmany, noted Oracle author and indexing expert, gave a great IOUG Live! 2005 presentation on the internals of Oracle indexing. In a repeat of the top-rated OracleWorld 2004 presentation, Garmany noted several important areas of Oracle indexing:

Detecting and removing un-used indexes
Managing large multi-column indexes
Index rebuilding
New Oracle 10g AWR index tables

The full presentation can be downloaded here. Col. Garmany makes several important notes:

Why Rebuilt Indexes are Problems

A tightly packed index is more efficient for the database as long as no changes are made in the underlying table. Once you start making changes, the index blocks start to split and reintroduce the fluff. Not only is fluff reintroduced, but there is redo created as blocks are added to the index. The higher the rate of change, the faster your nicely paced index will return to a steady state of fluff.

When to Rebuild Indexes

The only time you need to rebuild indexes is when the cost to rebuild is less that the performance gained. The first one is obvious. Pack your tables and indexes (rebuild into the soon to be read-only tablespace) tightly and they will stay that way. The second is much more difficult. First, it is a continuing process since the index will move toward fluff with use. Second, there is the cost of rebuilding the index AND the cost of the additional redo as the index changes. There is only one method to determine is rebuilding an indexes benefits your database, testing.

Oracle 10g Index Tables

The problem has always been that it is very difficult to know what indexes are the most popular. In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used. Here is a simple AWR query to plot index usage:

col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Index|Usage|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2

select
   p.object_name c1,
   p.operation c2,
   p.options c3,
   count(1) c4
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat s
where
   p.object_owner <> 'SYS'
and
   p.operation like ‘%INDEX%’
and
   p.sql_id = s.sql_id
group by
   p.object_name,
   p.operation,
   p.options
order by
   1,2,3;