Re: Local Index Size
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2012 09:03:43 -0700 (PDT)
Which release of Oracle are you using?
David Fitzjarrell
________________________________
From: Dba DBA <oracledbaquestions@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, July 10, 2012 9:49 AM
Subject: Local Index Size
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
