Re: Index Rebuilds

Thanks Christain and Alex. I didn't know index_ffs would scan all the blocks in freelists. Now it makes sense.

Yes, regular coalesce after rebuild would solve the problem, but is there a permanent solution for these ever growing index other than regular coalesce.

Thanks again.

On 7/30/06, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:
> Stalin
>
> > LBLKS: 66653
> > BlVL: 3
> > CF: 679426
> > NROWS: 829734
> > AVG_DATA_BLK/KEY: 2
> >
> > Coalescing the index got the lblks to 11k and the desired plan
> > however, the performance is still under water.
>
> Coalesce reuse the index structure and puts the freed block in the free
> list. I.e. I guess you have 55k blocks on the free list (a dump of the
> segment can be used to confirm this point). Since the FFS reads all the
> blocks up to the high water mark, also the free blocks are read.
>
> > Any suggestions as to what i should be looking further. I really don't
> > want to rebuild the index :)
>
> To decrease the index size you have to rebuild it. In the future, if the
> number of leaf blocks increase constantly (in the way you described),
> regular coalesce may "solve" the problem, i.e. let reuse the already
> allocated index blocks instead of allocating new ones.
>
>
> HTH
> Chris
>

--
http://www.freelists.org/webpage/oracle-l