On 9 Ott, 00:08, joel garry <joel-ga..._at_home.com> wrote:
> 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
Hi joel,
i'm very surprised for this approach.
i'm sorry but do you make these operation for know if your indexes
must be rebuild?
Tell more please.
Regards
Alberto
Received on Tue Oct 09 2007 - 01:58:33 CDT
