See also
Puget Sound Oracle User Group : Partition Examples and overview
Today it is time for some partition gym. How do we add, split, drop partitions ? When become the local indexes unusable ? When become the global indexes unusable ?
Note that
1. So far I haven' t done any composite partition gym
2. We speak about 10G R2 here. ( not yet 11G )
Let us take a look on what we have.
TABLE_NAME PARTITION PARTITION_COUNT
TABLE_T1 HASH 8
TABLE_T5 LIST 4
TABLE_T6 LIST 4
TABLE_T7 RANGE 2
TABLE_T8 RANGE 2
TABLE_T2 HASH 4
TABLE_T3 HASH 8
TABLE_T4 HASH 8
Table_t3 is hash partitioned, Which are the partitions for table_t3 ?
SQL> select partition_name from user_tab_partitions where table_name='TABLE_T3';
PARTITION_NAME
SYS_P2707
SYS_P2708
SYS_P2709
SYS_P2710
SYS_P2711
SYS_P2712
SYS_P2713
SYS_P2714
8 rows selected.
MOVING HASH PARTITIONS
Let us move a partition to another tablespace and update the global indexes
Table altered.
We haven' t invalidated the global primary key index
INDEX_NAME STATUS
T3_PK VALID
T3_IDX1 N/A
However the local index partition associated with the moved partition becomes invalid, we have to rebuild the unusable index partition
Table altered.
TRUNCATE TABLE PARTITION ( and update the global indexes )
Table truncated.
MODIFICATION OF DEFAULT STORAGE ATTRIBUTES
COALESCING A HASH PARTITIONED TABLE table removes a partition and redistributes the data among the other partitions
Table altered.
ADDING A HASH PARTITION
Table altered.
Table_t5 is list partitioned, Which are the partitions for table_t5 ?
SQL> select partition_name from user_tab_partitions where table_name='TABLE_T5';
name='TABLE_T5';
PARTITION_NAME TABLESPACE_NAME
BE USERS
FR USERS
NL USERS
OT USERS
MOVING LIST PARTITIONS
The update global indexes clause avoids the global indexes become unusable. ( very nice )
INDEX_NAME STATUS
PK_T5 VALID
T5_IDX1 N/A
However the local indexes become unusable
INDEX_NAME PARTITION_NAME STATUS
T5_IDX1 BE UNUSABLE
T5_IDX1 FR USABLE
T5_IDX1 NL USABLE
T5_IDX1 OT USABLE
Hence we have to rebuild the unusable local indexes
Table altered.
ADDING A LIST PARTITION
alter table table_t5 add partition uk values ('044')
*
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists
Adding a list partition is only possible when there is no default partition, whenever there is a default partition we have to split partitions
SPLIT LIST PARTITIONS
Table altered.
MERGE LIST PARTITIONS
We need to rebuild the unusable local indexes after merging and splitting.
INDEX_NAME PARTITION_NAME STATUS
T5_IDX1 BE USABLE
T5_IDX1 NL USABLE
T5_IDX1 FR USABLE
T5_IDX1 UK UNUSABLE
T5_IDX1 OT UNUSABLE
T5_IDX1 EG UNUSABLE
SQL> alter table table_t5 modify partition uk rebuild unusable local indexes;
Table altered.
SQL> alter table table_t5 modify partition ot rebuild unusable local indexes;
Table altered.
SQL> alter table table_t5 modify partition eg rebuild unusable local indexes;
TRUNCATE A LIST PARTITION
Table truncated.
DROP A LIST PARTITION
Table altered.
ADDING AND DROPPING VALUES TO LIST PARTITIONS
Table altered.
SQL> alter table table_t5 modify partition be drop values ('030','029') ;
Table altered.
SPLIT RANGE PARTITION
If the upper boundary of the range partition table has the maxvalue attribute then adding partitions is done with the split partition command
T7_P0308
TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN' )
T7_P0408
MAXVALUE
SQL> alter table table_t7 split partition T7_P0408? at (to_date('01/05/2008','DD/MM/YYYY')) into ( partition T7_P0408? , partition T7_P0508? ) update global indexes;
Table altered.
ADD RANGE PARTITION
If the upper boundary of the range partition table has a real value then adding partitions is done with the add partition command
Table altered.
MERGING RANGE PARTITIONS
We need to rebuild the unusable local indexes afterwards
Table altered.
SQL> alter table table_t8 modify partition T8_P030408? rebuild unusable local indexes;
Table altered.
TRUNCATE RANGE PARTITIONS
ADD AND REMOVE A RANGE PARTITION
/07/2008' , 'DD/MM/YYYY')) tablespace coconut_data;
Table altered.
SQL> alter table table_t8 drop partition T8_P0608? update global indexes;
Table altered.