Re: Tunning Indexes

see my previous reply ...I meant physical ordering within Table and not Index !!..

"Quarkman" <quarkman_at_myrealbox.com> wrote in message news: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.
>
> >
> > 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 - 21:56:55 CEST