Exchange Partition – Exchanging Table Partitions

Recently I came across a requirement of moving table to new tablespace. The table had 12 range based partitions and each partition had ~8K list subpartitions and was storing historic data. After few discussion, it was decided to use exchange partition instead of dbms_redefinition.

As I did not have much practical experience in exchange partition, I thought to test it and I am just posting my test here in the blog

CREATE TABLE composite_rng_list (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY LIST (cust_state)
SUBPARTITION TEMPLATE(
SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE demo,
SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE demo,
SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE demo) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2014','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2016','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN(MAXVALUE));

Table created.

SQL> insert into composite_rng_list values (1,'A','NY',to_date('01/02/2015','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.06
SQL> insert into composite_rng_list values (2,'B','CT',to_date('09/09/2014','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.06
SQL> insert into composite_rng_list values (3,'C','TX',to_date('10/10/2015','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (4,'D','WA',to_date('11/11/2013','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (5,'E','OK',to_date('21/02/2014','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.01
SQL> insert into composite_rng_list values (6,'F','OR',to_date('07/07/2013','DD/MM/YYYY'));

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('ANAND','COMPOSITE_RNG_LIST');

PL/SQL procedure successfully completed.


SQL>  select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST'
;

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- --------------- ------------------ ---------- ------------------------------
ANAND     COMPOSITE_RNG_LIST     FUTURE                           3          0 DEMO
ANAND     COMPOSITE_RNG_LIST     PER1                             3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER2                             3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER3                             3          2 DEMO

SQL> select table_owner,table_name,partition_name,subpartition_count from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT
--------- ---------------------- --------------- ------------------
ANAND     COMPOSITE_RNG_LIST     FUTURE                           3
ANAND     COMPOSITE_RNG_LIST     PER1                             3
ANAND     COMPOSITE_RNG_LIST     PER2                             3
ANAND     COMPOSITE_RNG_LIST     PER3                             3

SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows from dba_tab_subpartitions  where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_NA HIGH_VALUE      NUM_ROWS
--------- ---------------------- --------------- --------------- --------------- ----------
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_CENT     'OK', 'TX'          0
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_EAST     'NY', 'CT'          0
ANAND     COMPOSITE_RNG_LIST     FUTURE          FUTURE_WEST     'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_CENT       'OK', 'TX'          1
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_EAST       'NY', 'CT'          1
ANAND     COMPOSITE_RNG_LIST     PER3            PER3_WEST       'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_CENT       'OK', 'TX'          1
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_EAST       'NY', 'CT'          1
ANAND     COMPOSITE_RNG_LIST     PER2            PER2_WEST       'OR', 'WA'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_CENT       'OK', 'TX'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_EAST       'NY', 'CT'          0
ANAND     COMPOSITE_RNG_LIST     PER1            PER1_WEST       'OR', 'WA'          2

Created a local and a global index on the table

SQL> create index i_crl_state on composite_rng_list(cust_state);

Index created.

SQL> create index i_crl_time on composite_rng_list(time_id) local;

Index created.

Index details

SQL> @index
Enter value for owner: ANAND
Enter value for table_name: composite_rng_list

TABLE_OWNER     TABLE_NAME            INDEX_OWNER     INDEX_NAME            TABLESPACE_NAME   NUM_ROWS      CLUST STATUS   INDEX_TYPE
--------------- --------------------- --------------- --------------------- --------------- ---------- ---------- -------- ------------
ANAND           COMPOSITE_RNG_LIST    ANAND           I_CRL_STATE           DEMO                     6          6 VALID    NORMAL
ANAND           COMPOSITE_RNG_LIST    ANAND           I_CRL_TIME                                     6          5 N/A      NORMAL

SQL> @partition_index
Enter value for index_name: I_CRL_TIME
Enter value for owner: ANAND

INDEX_NAME            PARTITION_NA STATUS   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS
--------------------- ------------ -------- --------------- -------------- ----------- ------------ ----------
I_CRL_TIME            PER1         N/A                                                                    2
I_CRL_TIME            PER2         N/A                                                                    2
I_CRL_TIME            PER3         N/A                                                                    2
I_CRL_TIME            FUTURE       N/A                                                                    0

Now lets create a table with which we would be exchanging the partition. Below I have created EXP_TEMP_PER1 table, which will exchange partition PER1 from COMPOSITE_RNG_LIST table. Note table is created as list partition (based on subpartition of original table). In case you create the table same as source table (range-list partition) and try exchange partition you would be hit with

ORA-14292: Partitioning type of table must match subpartitioning type of composite partition


SQL> create table ANAND.EXP_TEMP_PER1 (
   CUST_ID NUMBER(10)
   , CUST_NAME VARCHAR2(25)
   , CUST_STATE VARCHAR2(2)
   , TIME_ID DATE
 ) tablespace TEST
 partition by LIST (CUST_STATE)
 (
     partition PER1_WEST values ('OR', 'WA')
   , partition PER1_EAST values ('NY', 'CT')
   , partition PER1_CENT values ('OK', 'TX')
 )
 ;

Table created.

Insert the data, into the newly created table.

SQL> insert into EXP_TEMP_PER1 select * from composite_rng_list partition (per1);

2 rows created.

Elapsed: 00:00:00.26
SQL> commit;

Commit complete.

SQL> select * from composite_rng_list partition (per1);

   CUST_ID CUST_NAME                 CU TIME_ID
---------- ------------------------- -- ---------
         4 D                         WA 11-NOV-13
         6 F                         OR 07-JUL-13
SQL> select * from EXP_TEMP_PER1 ;

   CUST_ID CUST_NAME                 CU TIME_ID
---------- ------------------------- -- ---------
         4 D                         WA 11-NOV-13
         6 F                         OR 07-JUL-13

Note, the table created has partitions, no subpartitions

SQL> select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='EXP_TEMP_PER1';

TABLE_OWN TABLE_NAME             PARTITION_NAME  SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- --------------- ------------------ ---------- ---------------
ANAND     EXP_TEMP_PER1          PER1_WEST                        0          2 TEST
ANAND     EXP_TEMP_PER1          PER1_EAST                        0          0 TEST
ANAND     EXP_TEMP_PER1          PER1_CENT                        0          0 TEST

Elapsed: 00:00:00.10
SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows from dba_tab_subpartitions where table_owner='ANAND' and table_name='EXP_TEMP_PER1';

no rows selected

Lets create local indexes similar to source table on the new table

SQL> create index i_exptper1_time on EXP_TEMP_PER1 (time_id) local;

Index created.

SQL> @partition_index
Enter value for index_name: i_exptper1_time
Enter value for owner: ANAND

INDEX_NAME            PARTITION_NA STATUS   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS
--------------------- ------------ -------- --------------- -------------- ----------- ------------ ----------
I_EXPTPER1_TIME       PER1_WEST    USABLE   TEST                   8388608     1048576                       2
I_EXPTPER1_TIME       PER1_EAST    USABLE   TEST                                                             0
I_EXPTPER1_TIME       PER1_CENT    USABLE   TEST                                                             0

Lets try the exchange partition

SQL> alter table COMPOSITE_RNG_LIST exchange partition per1 with table EXP_TEMP_PER1 including indexes with validation update global indexes;

Table altered.

SQL> select table_owner,table_name,partition_name,subpartition_name,high_value,num_rows,tablespace_name from dba_tab_subpartitions  where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST' and subpartition_name='PER1';

TABLE_OWN TABLE_NAME             PARTITION_NA SUBPARTITION HIGH_VALUE   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- ------------ ------------ ---------- ---------- ---------------
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_WEST    'OR', 'WA'          2 TEST
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_EAST    'NY', 'CT'          0 TEST
ANAND     COMPOSITE_RNG_LIST     PER1         PER1_CENT    'OK', 'TX'          0 TEST

SQL> select index_name,partition_name,subpartition_name,tablespace_name,status from dba_ind_subpartitions where index_owner='ANAND' and index_name='I_CRL_TIME';

INDEX_NAME            PARTITION_NA SUBPARTITION TABLESPACE_NAME STATUS
--------------------- ------------ ------------ --------------- --------
I_CRL_TIME            FUTURE       FUTURE_CENT  DEMO            USABLE
I_CRL_TIME            FUTURE       FUTURE_EAST  DEMO            USABLE
I_CRL_TIME            FUTURE       FUTURE_WEST  DEMO            USABLE
I_CRL_TIME            PER3         PER3_CENT    DEMO            UNUSABLE
I_CRL_TIME            PER3         PER3_EAST    DEMO            UNUSABLE
I_CRL_TIME            PER3         PER3_WEST    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_CENT    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_EAST    DEMO            UNUSABLE
I_CRL_TIME            PER2         PER2_WEST    DEMO            UNUSABLE
I_CRL_TIME            PER1         PER1_CENT    TEST            USABLE
I_CRL_TIME            PER1         PER1_EAST    TEST            USABLE
I_CRL_TIME            PER1         PER1_WEST    TEST            USABLE

SQL> select table_owner,table_name,partition_name,subpartition_count,num_rows,tablespace_name from dba_tab_partitions where table_owner='ANAND' and table_name='COMPOSITE_RNG_LIST';


TABLE_OWN TABLE_NAME             PARTITION_NA SUBPARTITION_COUNT   NUM_ROWS TABLESPACE_NAME
--------- ---------------------- ------------ ------------------ ---------- ---------------
ANAND     COMPOSITE_RNG_LIST     PER1                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER2                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     PER3                          3          2 DEMO
ANAND     COMPOSITE_RNG_LIST     FUTURE                        3          0 DEMO

As we can see above the local indexes status is “USABLE”. For partitions PER3 and PER2 we see status “UNUSABLE” because I did not use “including indexes” attribute in exchange partition for them.

From Oracle Documentation

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX
Then, enable the constraints after the exchange.

If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.

Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

DBA_TAB_SUPARTITIONS shows TEST tablespace, but still the view dba_tab_partitions, show DEMO tablespace for the partition. What needs to be done, so dba_tab_partitions show TEST tablespace for the partition? Please do respond 🙂

Reference

Exchanging Partitions

http://psoug.org/reference/partitions.html

Advertisements

One thought on “Exchange Partition – Exchanging Table Partitions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s