Re: Tunning Indexes
Date: Mon, 21 Jul 2003 05:52:37 +1000
Message-ID: <oprsmj5zb8r9lm4d_at_haydn>
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.
>
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
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
> 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.