Donald Burleson Click here for more Oracle News Headlines

Important 2007 update:  The rules for identification of candidates for index rebuilding are changing.  Please see my updated notes on index rebuilding.

The best of IOUG 2005

REBUILDING INDEXES – WHY, WHEN, HOW? - Jonathan Lewis

Jonathan Lewis, gave a presentation of Oracle index management. 

There has been some debate regarding rebuilding indexes, with experts claiming that Oracle indexes rarely need rebuilding, while acknowledging that high-DML (insert, update, delete) indexes can become sub-optimal very quickly and require rebuilding. 

Lewis' theme was that index rebuilding can be risky when performed improperly, using questionable tactics such as re-using index tablespace.  Lewis noted three index management techniques and his take on the pros and cons of each:

Feature                Pro                                           Con

The presentation then went-on to discuss how to detect when an index would benefit from rebuilding.  Lewis successfully argued that the index height is not a factor to consider when determining index fragmentation and Lewis argued that "index packing" might be a primary factor.  Lewis is noted for his skill at ferreting-out undocumented internals of Oracle, and this session was no exception.

In Oracle 9i we can get a very nice report showing the number of index entries per used leaf block, and this could improve the precision of our investigation. We hijack the undocumented function sys_op_lbid() that appeared for use with the dbms_stats package. There are several options built into this function, but one option can be used to count the number of index entries per leaf block. Consider the following SQL statement:

select
rows_per_block, count(*) blocks
from (
select
/*+
no_expand
index_ffs(t1,t1_i1)
noparallel_index(t,t1_i1)
*/
sys_op_lbid( {NNNNN} ,'L',t1.rowid) as block_id,
count(*) as rows_per_block
from
t1
where
v1 is not null
or small_pad is not null
group by
sys_op_lbid( {NNNNN} ,'L',t1.rowid)
)
group by rows_per_block;

Of course, there were some areas where other experts disagreed with some of his conclusions, most notably:

• "If an index needs constant care and attention, is this a clue that you really need to be fixing a design error."  Other experts have noted that high-DML environments require regular index maintenance and there is no work-around for having an index on highly-volatile tables.
   

• "Rebuilding indexes can be expensive, intrusive and risky. " - Lewis notes circumstances where rebuilding "can" be risky, but when done according to DBA best-practices, index rebuilding is very low-risk and well-worth the CPU cycles, especially on database with weekly downtime windows.

Lewis also helped to dispel the assertion that index height should be a factor to consider when deciding to rebuild an index:

 You have to be quite lucky (or unlucky) to be in the position where an index rebuild reduces the height of a B-tree index and a visible performance benefit. But that's why you should predict, then monitor, the effect.

If the rebuild does have a beneficial effect, and the effect seems to be due to an index rebuild there may be a better option available to you – for example doing the rebuild one more time at the correct pctfree, or (special case only) converting the table into a single table hash cluster. 

Lewis concluded with some warnings about flippant index rebuilding strategies:

• B-tree indexes almost always contain some empty space, and some B-tree indexes can end up holding a lot of empty space. Empty space can be eliminated – but empty space is not necessarily a performance issue, and attempts to eliminate empty space may be a waste of effort that could be applied more usefully elsewhere.
   

• It is easy to spot indexes with poor space utilization, which makes it easy to identify some of the indexes that may be good candidates for a rebuild – it is less easy to decide if the poor space utilization is actually causing a performance problem that is worth fixing.
   

• Rebuilding indexes can be expensive, intrusive and risky. In most cases, though, an index that really is a good candidate for rebuilding is likely to be susceptible to the index coalesce feature (especially in 10g).
   

• If you think you are going to get some benefit from doing an index rebuild, try to quantify the benefit beforehand, and measure it afterwards. If the rebuild was a good idea, then you have provided a justification for further investigation, and possibly a scheduled rebuild; if the rebuild was a waste of effort – or a bad idea – you can ensure that it doesn't happen again.

Overall, this was one of the best IOUG Live! 2005 presentations, and the sys_op_lbid tip was worth it weight in gold because it offers a method for bypassing the expensive "alter index validate structure" commands to gather index density information.

Update: (November 1, 2007)

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.