I have an overnight batch schedule during which I drop bitmap indexes, then recreate them again afterwards.
I recently did some analysis and found some of the index statistics as follows:
E.g.
Typical_Index_BM
Size in bytes from dba_segments = 70 MB.
Size in bytes (unused above HWM) from dbms_space.unused_space = 0 MB
Size in bytes (on freelist) from dbms_space.free_blocks = 69 MB
It would appear to me that 70 megs of blocks get used at some point during the index build (shown by the amount from dba_segments), and the fact that 0 megs appear above the HWM from dbms_space.unused_space.)
How is it that 69 MB of these used blocks then become available on the freelist for the segment?
Why didn't the index, in it's 'build-phase', just use 1 MB instead?
[ The index does build in parallel (degree 10), into a dictionary managed tablespace whose default storage clauses are set to 10 MB initial/next extents. ]
First, the INITIAL is 10M. Second, I suspect something is limiting Oracle to 7 parallel processes instead of the intended 10. However, with a 1M index, it probably is created in about .42 seconds so it would be really hard to tell.
On a side issue, a more robust process would be to make the index unusable and set skip_unusable_indexes = true, then rebuild them after the batch process completes
I believe this index was somewhat bigger before, as it has been converted from a standard b-tree to bitmap at some point in time.
The rebuild process for indexes gets actioned from within a Korn shell script which passes in the same build parameters for each index. Some of them are quite sizeable - hence the degree 10 parallelism.
I'll evaluate some of the timings on these smaller indexes and maybe run them noparallel instead.
:-)
Many thanks.
Regarding the 'unusable' state for indexes. I'm a little unsure of what the benefit on that would be. Could I be enlightened?
1. How would one manually 'set' an index to unusable state? {I've only ever effected that during table moves before . . . .}
2. What exactly IS the benefit on a rebuild, as opposed to a build from scratch?
Originally posted by AJW_ID01 1. How would one manually 'set' an index to unusable state? {I've only ever effected that during table moves before . . . .}
2. What exactly IS the benefit on a rebuild, as opposed to a build from scratch?
- Tony.
1) Alter Index idx_nm unusable;
2) nothing.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Originally posted by AJW_ID01 2. What exactly IS the benefit on a rebuild, as opposed to a build from scratch?
Rebuild will use existing index, which is faster in many situations.
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
Originally posted by adewri But if the table has been moved then its same whether u build from a scratch or do a rebuild as it has to hit the table any way.
Yes, or if there has been heavy DML on table, it can also be faster using table.
I think he is going for a regular overnight index rebuild, so it's neither of these two.
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
Reply With Quote
Bookmarks