Index Blocks/Extent Usage
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:
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. ]