Re: How many people here rebuid index regularly?

On Oct 8, 1:37 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> 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