Oracle: 10.2.0.5.7
This question is not about whether I should rebuild an index. I was basically just messing around with some things in a test database to see what would happen. Just to reiterate, I was just messing around to see what would happen when I rebuilt an index. I do not have any real reason to rebuild it. This is a question about the result of my test. The test was done because I wanted to learn something new.
Note that my table and index are not really called TableA and PkA.
TableA is range partitioned with 10 partitions.
PkA: 2 column primary key on TableA that is locally partitioned. The first field in the key is the partition field
-- note: not real field names
Partition Key: MyKey
Primary Key Fields: MyKey,MyCounter
Rebuild Process: Did not add/remove compression, change tablespace, change pctfree, pctincrease, etc...
Note: The table is not balanced and the amount of data in each partitioned is very different. Note, this post is not about the merits of having partitions of equal size.
See the byte size of PKA below. Just out of curiosity I rebuilt the local index on each partition. After the rebuild, the local index was 8gb per partition. So the local index grew in size by 20 gbs after a rebuild. After the byte size of the index is larger than the amount of bytes in most of the partitions. The table has about 20 fields. The first partitions index shrunk in size, but the rest grew in size.
I am guessing this is normal behavior, but why?
Table Size in Bytes
TABLEA_P000 524,288,000
TABLEA_P001 19,398,656,000
TABLEA_P002 6,291,456,000
TABLEA_P003 7,864,320,000
TABLEA_P004 14,680,064,000
TABLEA_P005 9,961,472,000
TABLEA_P006 7,340,032,000
TABLEA_P007 15,204,352,000
TABLEA_P008 8,388,608,000
TABLEA_P009 9,437,184,000
index size in bytes
RRC_PK_I_P000 524,288,000
RRC_PK_I_P001 16,864,320,000
RRC_PK_I_P002 2,097,152,000
RRC_PK_I_P003 2,621,440,000
RRC_PK_I_P004 4,718,592,000
RRC_PK_I_P005 3,145,728,000
RRC_PK_I_P006 2,621,440,000
RRC_PK_I_P007 4,718,592,000
RRC_PK_I_P008 2,621,440,000
RRC_PK_I_P009 3,145,728,000
Index Size after rebuild
RRC_PK_I_P000 8 gb
RRC_PK_I_P001 8 gb
RRC_PK_I_P002 8 gb
RRC_PK_I_P003 8 gb
RRC_PK_I_P004 8 gb
RRC_PK_I_P005 8 gb
RRC_PK_I_P006 8 gb
RRC_PK_I_P007 8 gb
RRC_PK_I_P008 8 gb
RRC_PK_I_P009 8 gb