Re: When should one rebuild an index?
Date: Sun, 28 Dec 2008 13:50:09 -0800
Message-ID: <1230501008.751907@bubbleator.drizzle.com>
Bob Jones wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message > news:1230425754.597314_at_bubbleator.drizzle.com...
>> Bob Jones wrote:
>>
>>> Perhaps, instead of looking at stats, we just need an Ace Director to
>>> orchestrate DBAs what to use or not to use in all situations.
>> Cute Bob. Really cute. The advice you are hearing from Oakies, from
>> Aces, from internal Oracle resources is the same. If you wish to ignore
>> it that is certainly your choice.
>>
>> None of us make a dollar if you take our advice. None of us lose a wink
>> of sleep if you ignore it. The simple fact is that sooner or later your
>> employer will contact one of us to bail the bilge. Whether you will still
>> be there when that happens is irrelevant tous. But a cautionary
>> note to you and others who think attitude trumps aptitude. There is a
>> reason why Oracle is being so successful selling the "you need fewer
>> DBAs" mantra. CIOs and CTOs see too many dollars wasted on people that
>> don't read. Too many dollars wasted on people that go to classes and
>> still keep doing things the old way. Too many dollars wasted on people
>> that think they know better than Tom Kyte (still not using bind
>> variables), Richard Foote (still rebuilding indexes), Jonathan Lewis
>> (still tuning based on BHCR), etc.
>>
> > Hmmm, I have been here well over 10 years and still happily employed. In > fact, our company is still doing very well in this down economy. > There is nothing wrong with showing attitude or aptitude, just not by > throwing people's names around though. >
>> What I am telling you in simple terms is that you can run ANALYZE
>> INDEX from now until you are a member of the gray-hair-no-hair crowd
>> like me and you will not once see anything that will clearly indicate
>> that your database will benefit from an index rebuild. It is the wrong
>> tool. You can start figuring that out for yourself here if you wish:
>> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4005.htm#SQLRF01105.
>>
> > I see no part of this doc suggesting ANALYZE being useless in determining > index rebuild. Where?
You also see no part suggesting it is useful either do you? Are you able to acknowledge that fact?
But here: Knock yourself out explaining whether this index should or should not be rebuilt based on the information generated by ANALYZE.
SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space
2 FROM index_stats
3 WHERE name = 'IX_TEST';
no rows selected
SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space
2 FROM index_stats
3 WHERE name = 'IX_TEST';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ---------- ----------
----------- ----------
2 128 20863 97 96 1
784032 768604
SQL> delete from test;
20863 rows deleted.
SQL> commit;
SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space
2 FROM index_stats
3 WHERE name = 'IX_TEST';
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
BTREE_SPACE USED_SPACE
---------- ---------- ---------- ---------- ---------- ----------
----------- ----------
2 128 20863 97 96 1
784032 768604
SQL> As you can not the obvious inference is that ANALYZE does not, in and of itself, produce information of value in determining whether there will be a benefit from an index rebuild.
Now lets try it my way. I rebuild and repopulate that table.
SQL> SELECT rows_per_block, count(*) blocks
2 FROM (
3 SELECT /*+ cursor_sharing_exact
4 dynamic_sampling(0) 5 no_monitoring 6 no_expand 7 index_ffs(test,ix_test) 8 noparallel_index(test,ix_test) 9 */ 10 sys_op_lbid(90508, 'L', test.rowid) block_id, 11 COUNT(*) rows_per_block
12 FROM test
13 WHERE object_name IS NOT NULL
14 GROUP BY sys_op_lbid(90508, 'L', test.rowid)) 15 GROUP BY rows_per_block;
ROWS_PER_BLOCK BLOCKS
-------------- ----------
244 1
245 1
227 2
198 1
194 3
222 2
213 3
217 2
235 2
209 1
207 2
218 2
211 3
224 1
205 1
210 3
223 1
229 3
253 1
230 2
215 3
201 2
314 1
193 1
232 1
206 2
226 1
248 1
231 2
241 1
199 3
254 2
228 1
243 3
200 1
233 1
188 1
221 4
203 3
236 1
225 1
195 2
202 4
216 2
7 1
237 1
197 6
219 1
204 4
234 2
208 1
51 rows selected.
SQL> delete from test;
20863 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT rows_per_block, count(*) blocks
2 FROM (
3 SELECT /*+ cursor_sharing_exact
4 dynamic_sampling(0) 5 no_monitoring 6 no_expand 7 index_ffs(test,ix_test) 8 noparallel_index(test,ix_test) 9 */ 10 sys_op_lbid(90508, 'L', test.rowid) block_id, 11 COUNT(*) rows_per_block
12 FROM test
13 WHERE object_name IS NOT NULL
14 GROUP BY sys_op_lbid(90508, 'L', test.rowid)) 15 GROUP BY rows_per_block;
no rows selected
SQL> There are valid and invalid means of determining whether an index will benefit from a rebuild. The above demo of SYS_OP_LBID produces one useful data point. It too, by itself, is insufficient in most cases. But at least the numbers it generates are relevant.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sun Dec 28 2008 - 15:50:09 CST