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
