Exchange partitions with local unique and primary keys

The following steps are a typical approach for bulk loading a large set of data into a partitioned table;

1. SQL Loader or bulk INSERT into non partitioned table with no indexes
2. Create indexes and/or primary key constraints (if need be, in parallel)
3. Exchange partitions
4. Rebuild local and/or global indexes
5. Gather optimizer statistics

Exchanging partitions with a non-partitioned tables requires a little care when dealing with the partitioned tables indexes.

Firstly, create a sample partitioned table:

SQL> create table Trade_Account
  2  (
  3     Acc_ID           number not null
  4    ,Date_Created     date   not null
  5    ,is_Joint_Account varchar2(1) default 'N' not null
  6    ,Acc_Type_ID      number
  7  )
  8  partition by range (date_created)
  9  (
 10  partition p131109 values less than(to_date('2013-11-10', 'YYYY-MM-DD')),
 11  partition p131110 values less than(to_date('2013-11-11', 'YYYY-MM-DD')),
 12  partition p131111 values less than(to_date('2013-11-12', 'YYYY-MM-DD')),
 13  partition p131112 values less than(to_date('2013-11-13', 'YYYY-MM-DD')),
 14  partition p131113 values less than(to_date('2013-11-14', 'YYYY-MM-DD')),
 15  partition p131114 values less than(to_date('2013-11-15', 'YYYY-MM-DD')),
 16  partition pmax values less than (maxvalue)
 17  );

Table created.

Next, create a sample loading table:

SQL> create table trade_account_load as (select * from trade_account);

Table created.

Create sample data in both tables:

SQL> insert into Trade_Account values
  2  (1,to_date('20131112','yyyymmdd'),'Y',1);

1 row created.

SQL> insert into Trade_Account values
  2  (2,to_date('20131113','yyyymmdd'),'N',1);

1 row created.
SQL> insert into Trade_Account_load values
  2  (3,to_date('20131113','yyyymmdd'),'Y',1);

1 row created.

SQL> insert into Trade_Account_load values
  2  (4,to_date('20131113','yyyymmdd'),'N',1);

1 row created.

SQL> commit;

Gather stats and show how many rows exist in each partition:

SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows
  2  from user_tab_partitions
  3  where table_name = 'TRADE_ACCOUNT';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P131109                                 0
P131110                                 0
P131111                                 0
P131112                                 1
P131113                                 1
P131114                                 0
PMAX                                    0

7 rows selected.
 
Create a global index first and then attempt an exchange:

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id);

Table altered.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION


The reason for  the exception is that the unique index that is used to enforce the primary key constraint is not local. Retry with a local index:

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id) using index local;
alter table trade_account
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

  

Attempting to create a local unique index, regardless of whether the index is used to enforce a primary key constraint, without the partition key generates an exception. Another exception will occur if the primary key on the non-partitioned table does not exist, for example:

 SQL> alter table trade_account
  2  add constraint trade_account_pk
  3  primary key (acc_id,date_created) using index local;

Table altered.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;
alter table trade_account
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

 The partitioning alignment will be shown as either PREFIXED or NON-PREFIXED, for example:

PREFIXED

SQL> alter table trade_account
  2  add constraint trade_account_pk
  3  primary key (date_created, acc_id) using index local;

Table altered.

SQL> select upi.table_name
  2        ,upi.index_name
  3        ,upi.partitioning_type
  4        ,upi.locality,alignment
  5  from
  6         user_part_indexes upi
  7  where
  8         upi.table_name = 'TRADE_ACCOUNT';

TABLE_NAME      INDEX_NAME        PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT   TRADE_ACCOUNT_PK  RANGE     LOCAL  PREFIXED

NON-PREFIXED

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> alter table trade_account
  2  add constraint trade_account_pk
  3  primary key (acc_id, date_created) using index local;

Table altered.

It is worth creating unqiue/primary key constraints separately to the unique index, since one can disable and re-enable the constraint without dropping the index. When loading a large volume of rows the constraint can be disabled, but the index will be maintained. There are several ways to create them seperately;

Simplest way:

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key(acc_id, date_created)
  3  using index
  4  (create unique index trade_account_pk
  5   on trade_account(acc_id,date_created) local
  6  );

Table altered.

Or, as two seperate DDL statements:

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> create unique index trade_account_pk
  2  on trade_account(acc_id,date_created) local;

Index created.

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id,date_created)
  3  using index trade_account_pk;

Table altered.

The following indicates how all the local partitioned indexes remain usable even though the constraint has been disabled.

SQL> alter table trade_account drop constraint trade_account_pk;

Table altered.

SQL> create unique index trade_account_pk
  2  on trade_account(acc_id,date_created) local;

Index created.

SQL> alter table trade_account
  2  add constraint trade_account_pk primary key (acc_id,date_created)
  3  using index trade_account_pk;

Table altered.

SQL> alter table trade_account disable constraint trade_account_pk;

Table altered.

SQL>
SQL> select uip.index_name
  2        ,uip.partition_name
  3        ,uip.status
  4  from   user_ind_partitions uip
  5  where  uip.index_name = 'TRADE_ACCOUNT_PK'
  6  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.

SQL> select status from user_constraints
  2  where constraint_name = 'TRADE_ACCOUNT_PK';

STATUS
--------
DISABLED


The index will now appear as NON_PREFIXED in the data dictionary.

 SQL> select upi.table_name
  2        ,upi.index_name
  3        ,upi.partitioning_type
  4        ,upi.locality,alignment
  5  from
  6         user_part_indexes upi
  7  where
  8         upi.table_name = 'TRADE_ACCOUNT';

TABLE_NAME      INDEX_NAME        PARTITION LOCALI ALIGNMENT
--------------- ----------------- --------- ------ ------------
TRADE_ACCOUNT   TRADE_ACCOUNT_PK  RANGE     LOCAL  NON_PREFIXED

Be aware that there are performance implications for non-prefixed local indexes. Refer to the 11gR2 docuementaion here for more detail.

Create a primary key on the non-partitioned table that matches the primary key on the partitioned table in terms of the columns used and then retry the exchange:

SQL> alter table trade_account_load add constraint trade_account_load_pk
  2  primary key (acc_id,date_created);

Table altered.

SQL> alter table trade_account
  2  exchange partition P131113 with table trade_account_load;

Table altered.

Rebuild the unusable indexes. If the indexes are local, then only the index local to the partition that was exchanged would have to be rebuilt. If the index is global then the entire global index requires rebuilding, which could take a considerable amount of time, so it might be worth investigating whether the rebuild can occur in parallel.

SQL> select uix1.index_name
  2        ,null as partition_name
  3        ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9        ,uip.partition_name
 10        ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                              and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        UNUSABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected. 

There are no global indexes, but a single local partition index that is unusable;

SQL> alter index TRADE_ACCOUNT_PK rebuild partition P131113;

Index altered.

SQL> select uix1.index_name
  2        ,null as partition_name
  3        ,uix1.status
  4  from   user_indexes uix1
  5  where  uix1.table_name  = 'TRADE_ACCOUNT'
  6  and    uix1.partitioned = 'NO'
  7  union all
  8  select uip.index_name
  9        ,uip.partition_name
 10        ,uip.status
 11  from   user_ind_partitions uip
 12  inner join user_indexes uix2 on uip.index_name = uix2.index_name
 13                              and uix2.table_name = 'TRADE_ACCOUNT'
 14  order by 2;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TRADE_ACCOUNT_PK               P131109                        USABLE
TRADE_ACCOUNT_PK               P131110                        USABLE
TRADE_ACCOUNT_PK               P131111                        USABLE
TRADE_ACCOUNT_PK               P131112                        USABLE
TRADE_ACCOUNT_PK               P131113                        USABLE
TRADE_ACCOUNT_PK               P131114                        USABLE
TRADE_ACCOUNT_PK               PMAX                           USABLE

7 rows selected.

Finally, run the stats gathering again to verify whether the rows were loaded:

SQL> exec dbms_stats.gather_table_stats(sys_context('userenv','current_schema'), 'TRADE_ACCOUNT');

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows
  2  from user_tab_partitions
  3  where table_name = 'TRADE_ACCOUNT';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P131109                                 0
P131110                                 0
P131111                                 0
P131112                                 1
P131113                                 2
P131114                                 0
PMAX                                    0

7 rows selected.