Local Index Size

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Tue, 10 Jul 2012 11:49:38 -0400
Message-ID: <CAE-dsOK4nynj0oxVzZU_+7aFL_kznTYZdkX8sg9CsAeVEmB1sQ_at_mail.gmail.com>

I have a range partitioned table where each partition has a vastly different size. We recently ran a purge (over several months) to remove alot of old data. So it makes sense to explore rebuilding the tables to shrink them. When we rebuild the Table below, we shrink the size of the table. However, we have a local index that is the primary key. When we rebuild this index, it becomes much larger in size. All of the partitioned indexes become the same size. In many cases the size of the index in a partition is larger than the amount of data.
Why does oracle does? I am not sure why local indexes need to be the same size. I am guessing the 8gb size is based on the largest partition.

Note that the table name below has been changed.

TABLEA is range partitioned and has 10 partitions. TABLEA is not the real name of the table.
The partition sizes have a huge difference in size. This is a legacy system, so this was designed years ago. I can't just change this on the fly.
TABLEA has a Primary key local index. Fields: Partition Key, Sequence based counter

We have been running a purge process for several months that is deleting older data based on a complex set of logic. It makes sense to look at rebuilding the table to save space.
Here are the current sizes of each partition before rebuilding.

I copied the table to a test database. I rebuilt the partitions and they shrunk considerable.
PARTITION TABLEA_P000 is down to about 50mbs, but partition TABLEA_P001 is still 20 gbs.

I then rebuilt the primary key index, partition by partition. The overall size of the index increased. The index after rebuilding is 8gbs for each partition. In many cases the index
is larger than the data in the partition.

*So it looks like when you rebuild a local index, oracle makes each partition index the exact same size. Does anyone know why that is? *

so segment: TABLEA_PK_I_P000 (index segment) grows from: 524,288,000 to 8gbs after the rebuild, but we do not have much data in that partition. This index is much larger than the segment that houses it. All of the index segments are 8 gbs.

Table Size by Partition before Rebuild

PARTITION_NAME                            BYTES


------------------------------ ----------------

TABLEA_P000                       524,288,000
TABLEA_P001                    33,554,432,000
TABLEA_P002                     7,864,320,000
TABLEA_P003                     9,961,472,000
TABLEA_P004                    18,350,080,000
TABLEA_P005                    12,582,912,000
TABLEA_P006                     9,437,184,000
TABLEA_P007                    18,874,368,000
TABLEA_P008                    10,485,760,000
TABLEA_P009                    12,058,624,000

Index Size before rebuild:

PARTITION_NAME                            BYTES


------------------------------ ----------------

TABLEA_PK_I_P000                       524,288,000
TABLEA_PK_I_P001                    16,252,928,000
TABLEA_PK_I_P002                     3,145,728,000
TABLEA_PK_I_P003                     3,670,016,000
TABLEA_PK_I_P004                     6,815,744,000
TABLEA_PK_I_P005                     4,718,592,000
TABLEA_PK_I_P006                     3,670,016,000
TABLEA_PK_I_P007                     7,340,032,000
TABLEA_PK_I_P008                     4,194,304,000
TABLEA_PK_I_P009                     4,718,592,000

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 10 2012 - 10:49:38 CDT