Important 2007 update: The
rules for identification of candidates for index rebuilding are
changing. Please see my
updated notes on 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;
Overall, an excellent and informative presentation.
See my related notes on index rebuilding:
Update: (November 1, 2007)
When can we "prove" a benefit from an index
rebuild? Here, Robin Schumacher
proves that an index that is rebuilt in a larger tablespace will
contain more index entries be block, and have a flatter structure:
"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."
In an OracleWorld 2003
presentation titled “Oracle Database 10g: The Self-Managing
Database” by Sushil Kumar of Oracle Corporation, Kumar
states that the new Automatic Maintenance Tasks (AMT) Oracle10g
feature will "automatically detect and re-build sub-optimal
indexes.“
This
Kim Floss article shows the Oracle 10g segment advisor
recommending a rebuild of an index:
“The page lists all
the segments (table, index, and so on) that constitute the
object under review. The default view ("View Segments
Recommended to Shrink") lists any segments that have free space
you can reclaim.”
Oracle index rebuild advisor (Source: Oracle Corporation)
See my related notes on index rebuilding:
|
and include the URL for the page.
