Space Used by Index after a Rebuild

From: Daniel Roy <danielroy10_at_hotmail.com>
Date: 16 Dec 2002 12:42:07 -0800
Message-ID: <1b061893.0212161242.5aa71430_at_posting.google.com>


Hi everyone,
  I just ran an index rebuild on a bunch of indexes (a couple of hundreds of them), and noticed with dbms_space.unused_space that the indexes take more space after the rebuild! They are about 40% bigger. I notice that the column "Unused Bytes" of dbms_space.unused_space is about 5 times bigger than before. I just used "alter index --- rebuild", and therefore no index storage parameter should be different from before. The underlying tables had a lot of deletes recently (about 70-80% of their rows), which certainly gave me hope that the rebuilt indexes would be smaller. PCT_FREE is 10% only, and therefore can't explain the increased size. I'm using Oracle 8.1.6 on AIX 4.3.3. Anyone has an idea what happened?

Thank you

Daniel Roy
IBM Canada
OCP 8i Received on Mon Dec 16 2002 - 21:42:07 CET

Original text of this message