comp.databases.oracle.server

Re: How many people here rebuid index regularly?

joel garry <joel-ga...@home.com>

On Oct 8, 1:37 pm, "Jonathan Lewis" <jonat...@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