Re: Tunning Indexes

On Sun, 20 Jul 2003 11:31:11 -0400, Thiru <t_at_t.com> wrote:

> Before rebuilding the indexes, you may need to analyze the indexes and
> look
> at the dictionary (ie dba_indexes,index_stats )
> for clustering
> factor,blevel(ie binary height) ,leaf nodes,deleted keys etc.
> Clustering factor indicates how well the data is physically organised
> within
> the index.

No it doesn't. It tells you whether the way things are ordered in the index is very similar or totally dissimilar to the way the rows are ordered in the table. That is, if visit one index leaf block, how many table blocks will I then have to visit? If table data is scattered all over the place in no apparent order, but the index is in (say) ascending numeric order, then one leaf node will likely point to dozens of table blocks.

> IF the clustering actor approaches the number of leaf_blocks in
> the index, then you are OK, but if it approaches the number of rows in
> the
> table, then it means that more I/O needs to be performed to fetch the
> index
> blocks ,unnecessarily

Wrong. If it approaches the number of rows in the table, it means that there is no correspondence whatsoever between the way stuff is stored in the table and the way it is ordered in the index. That's a good indication that the *table* should be re-organised, not the index (as in 'create table empcopy as select * from emp', 'truncate table emp', 'insert into emp select * from empcopy ordered by ename').

> and the index becomes a good choice for rebuild.

See above. Bad clustering means your table is disorganised, not your index. And if you've got multiple indexes on the one table, then no amount of table re-organisation is going to help you. (If you've an index on empno AND a separate one on ename, then you're unlikely to find an 'order by' clause when re-organising the table which gets both indexes' clustering factors down to 'good' levels).

> Blevel for most of the indexes,including large ones lies in the range 2-3
> which indicates the number of reads to be performed to get to the key
> value.

You'd have to have a completely deranged index before the height of the index becomes something that can be 'fixed' by a rebuild. It's unusual to find height becoming progressively bigger and bigger anyway.

> Also look for the ratio of number of deleted rows to the total number of
> rows. If you find that this is a considerable percentage and does not
> find
> that this is not getting reused by additional inserts,you may need to
> rebuild . In most cases,Oracle will reuse the space freed up the deletes.

Absolutely true. Fundamentally, it's rather unusual *ever* to really need to rebuild an index. There are always exceptions, of course, but generally it's a lot of old hard work for no discernible benefit, particularly since Oracle is generally pretty good at re-using index space as you describe.

>
> If you do decide to rebuild the index,then you have the option of
> rebuilding
> it online (ie doesnt lock simultaneous DML) and NOLOGGING to speedup the
> index creation. Also you may want to increase session SORT_AREA_SIZE to
> speed up the sorts during the rebuild.

Also mostly good advice here. Except that an online rebuild of an index isn't terribly "online". It requires an exlcusive lock on the table to start, after which it relinquishes it. And then it takes another exclusive lock to finish. If it can't take those exclusive locks, then the session where you're trying to rebuild the index will 'hang', and others will start queueing up behind it. And it won't be able to take the lock without this hanging behaviour if the table is subject to almost constant DML.

So you end up with the odd paradox: when would you like to be able to do an online rebuild? When the table is subject to lots of DML. When won't an online rebuild be a particularly "online" operation? Er, when the table is subject to lots of DML.

If "online-ness" is really a requirement, have you considered 'alter index xxxx coalesce'? Coalescing is inherently online, and there are no exclusive locks taken on the table at all.

~QM Received on Sun Jul 20 2003 - 14:52:37 CDT