> > Since no-one else has volunteered any suggestion, I'd
> > be interested in seeing what results you get from
> > the code at:http://www.jlcomp.demon.co.uk/index_efficiency.html

> > Picking just one potentially interesting index, as it does
> > a fast full scan with aggregate of all entries.

> > Code changes needed:
> >     supply the index name in the 'select from user_objects'
> >     (which may have to be dba_objects)

> >     Where I have 'column is not null' change the column
> >     names (and add predicates if necessary) to cover the
> >     columns in the chosen index.

> > A note of the number of leaf_blocks in the index (from
> > dba_indexes/user_indexes) before and after would also
> > be useful, and the block size.

> I have a situation where the production schema had a major delete at
> the end of last year, and has slowly been adding since.  When I exp/
> imp the schema in question to a test db,  the size is a good 20%
> smaller.  I then look at the largest index that OEM tells me has
> significantly fewer blocks with your program, and I see a much broader
> distribution of number of rows per index blocks.

> >From production:

> [snippage]

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>            113       1877
>            114       4984
>            115       3167
>            116      28250
>            117       2096
>            118       4314
>            119       2301
>            120       5501
>            121       2526
>            122       9402
>            123       1803

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>            124       3403
>            125       1255
>            126       2421
>            127        919
>            128       2528
>            129        985
>            130       1775
>            131        714
>            132       2354
>            133        727
>            134       1589

> [snippage of more rows like this]

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>                ----------
> sum                172843

> 176 rows selected.

> SQL> select leaf_blocks from user_indexes where
> index_name='IC_MOVEMENTS_2';

> LEAF_BLOCKS
> -----------
>      172697

> >From test:

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>            113          1
>            121          1
>            131          1
>            160          1
>            165          1
>            188        210
>            189          2
>            192          1
>            193        697
>            197          1
>            198          2

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>            199      52109
>            200        230
>            201        288
>            202        468
>            203        692
>            204       2554
>            205      10419
>            206       8775
>            207       3780
>            208       1040
>            209        463

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>            210      32797
>            211          3
>            212          3
>            213          5
>            214          2
>            215          5
>            216          1
>            217       3012
>            218          1
>            219          1
>            220          1

> ROWS_PER_BLOCK     BLOCKS
> -------------- ----------
>            223        358
>            224          1
>            225          1
>            227          1
>            228          1
>            230          1
>            231        222
>            233          1
>            235          1
>                ----------
> sum                118154

> 42 rows selected.

> SQL> select leaf_blocks from user_indexes where
> index_name='IC_MOVEMENTS_2';

> LEAF_BLOCKS
> -----------
>      118334

> Therefore, exp/imp must indeed be the answer to life, indices and
> everything!  :-)

> The question that is begged:  Can-or-should I just compare the
> leaf_blocks on all indices between production and test and rebuild
> those with the largest difference?   Seems too simple.   (Especially
> since I know if I mention this to this customer they will just want me
> to exp/imp the whole db, MS-think abounds).  I've never felt any need
> to do anything to these indices, except due to the space issue -
> between hardware upgrades and such, this is the first time they've
> gone a couple of years with no maintenance.

> jg
> --
> @home.com is bogus.http://www.signonsandiego.com/uniontrib/20071006/news_1b6halo.html