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. ]

- Tony.