When is the last time you saw one with a level of 6? (and did it go down to 3..) Rebuilding an index is only recommended when it NEEDS IT. My point is, I see people BLINDLY rebuidling ALL indexes every day, week, month -- whatever. Just an utter waste of time for most (might even be counter productive!).

Find the 1% that you need to rebuild and do them when they need to. Not as a part of a standard operating procedure (time to rebuld all of the indexes.. what a waste)

Bitmaps are a little different -- they are seriously affected by incremental loads and in general stand to gain from a rebuild after some incremental loads.

watch statspack - look at the top sql, especially by buffer gets. If one starts creeping up there over time, could be an index that needs looking at.

1) if you need statistics, of course. is the original table having stats? then this one should.

2) no, you can gather index stats.

look in user_indexes after you analyze it.

I never say always -- rebuilding CAN be beneficial in some extreme cases. for B*trees it is rare rare rare. For bitmaps and text indexes -- less rare (i rebuild my text index on asktom every now and again -- when I notice the estimated counts getting way off)

collect statspack for a couple of days before the rebuild. do this during peak and make sure you see queries that would be affected by this rebuild.

then, do it again after the rebuild.

Now, compare the two and have the consultant show how much improvement they've provided.

Be really interesting to make their fee contigent on a positive result :)


Ask them for the metrics they've used to prove the indexes are "fragmented" -- being really interesting to see their thought process as well.

rebuild is better

a rebuild can read the exist index (avoid a sort)

a rebuild does not drop and create (two step), it is a single command. Hence, you will not accidently "lose" an index during a reorg (as happens frequently)

a rebuild does require about 2x the space (both indexes exist).


And then there is coalesce which uses the existing index, frees up space in leaf blocks, is a single command, and works in place as well.... (no 2x)

you can analyze the index to get average space utilization - but averages won't be a really "good thing" over all.

you sort of want to know "how is the index used". for example

a) index is on a column populated by a sequence.
b) sequence only increases
c) we delete 99% of the data from the low value to the high value over time (eg: sort of a queue table)

so at one point you had the values 1..1000 in the table -- over time you delete 99% of that leave 1, 101, 201, ... 901.

That would be a good candidate for a coalesce due to the nature of the way you use it. the left hand side would compact nicely -- the right hand side (still dense, have not processed rows 1001..100000000 yet) won't be touched (read but not modified).

Averages would not tell you this however, the average space would be "very well packed". Your knowledge would be the thing that tells you.

</code> http://asktom.oracle.com/pls/ask/search?p_string=sweeper <code>

the index, given you just insert, will almost certainly be just fine - forever.

presuming:

it is a b*tree index, not a bitmap index
OR
it is a bitmap index and you direct path load the 100/200k records in bulk, not slow by slow (row by row) in conventional path inserts.

if you delete and don't reinsert sure - but if you delete and then tend to put the data back in there - well, what then?

that is what I call a "sweeper"

http://asktom.oracle.com/pls/ask/search?p_string=sweeper

what kind of delete are you talking about here - you do not in general need to rebuild

no, there is not.

I just copied this link from above

http://asktom.oracle.com/pls/asktom/f?p=100:232:2240578479283811::::P232_QUESTIONID:3679582396999#78917554970273

http://asktom.oracle.com/pls/ask/search?p_string=sweeper

sorry, copy didn't copy I guess...

All the way back to version 2 if you like. There was no version 1 of Oracle.

almost certainly not (unless it was a bitmap index).

Assuming a b*tree index, a normal b*tree index, almost certainly not. There is one edge case where that would not be true, but almost always "no"

There are three cases to consider:

a) randomly inserted data, like last name. On an employee table, you do not hire everyone with the name starting with A, then B then C and so on - you hire people with random last names and the index is hit all over the place. That index will always be about 20-40% 'empty' as we do a 60/40 block split when a block fills up. If you rebuilt it - it would be "smaller" for a very very very short period of time - during which it would expend it's energies getting fat again (lots of splits). A rebuild would give you nothing - you would not reclaim space for more than a very short period, the index would spend a lot of time and energy getting fat again.

b) monotomically changing data (a date field populated by sysdate, a sequence populated field) Here we would do 90/10 block splits - the index would always be inserted into on the right hand side but the left hand side would be 90% full - well packed.


c) a variation on (B) - this is the edge case. The field is indexed, the values IN GENERAL are monotomic - but not always, not perfectly. So, instead of always increasing (or decreasing) in value as inserted, the arrive slightly out of order. Now instead of a 90/10 split - we do the 60/40 split. we leave the blocks on the left 40% empty - and will never use that space since the values in general are increasing.

This index MIGHT benefit from a coalesce, maybe.