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
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;
Or, as two seperate DDL statements:
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.
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.
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.
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.
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.
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.
