parallel dml enable

A. Export/import method

This method involves exporting the non partitioned table, creating a partitioned table, and then importing data into the new partitioned table.

1) Export your table:

$ exp <user_name>/<pwd> tables=TEST_TABLE1 file=exp.dmp

2) Drop the table:

SQL> drop table TEST_TABLE1;

3) Recreate the table with partitions:

SQL> create table TEST_TABLE1 (qty number(3), name varchar2(15)) partition by range (qty)(partition p1 values less than (501),
partition p2 values less than (maxvalue));

4) Import the table with ignore=y:

$ imp <user_name>/<pwd> file=exp.dmp ignore=y

The ignore=y causes the import to skip the table creation and continues to load all rows.

With Data Pump export/import (expdp/impdp) you can use the  table_exists_action option of impdp e.g. table_exists_action = APPEND or table_exists_action = REPLACE.

Also review Note 552424.1 Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data ?

B. Insert with a subquery method

1) Create a partitioned table:

SQL> create table partbl (qty number(3), name varchar2(15)) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue));

2) Insert into the partitioned table with a subquery from the non-partitioned table:

SQL> insert into partbl (qty, name) select * from origtbl;

3) If you want the partitioned table to have the same name as the original table, then drop the original table and rename the new table:

SQL> drop table origtbl;
SQL> alter table partbl rename to origtbl;

You may improve the insert performance with direct path insert and utilize parallelism. Examples below show how this can be done and how it can be recognized in the execution plan.

Conventional insert

SQL> insert into partbl (qty, name) select * from origtbl;

--------------------------------------------
| Id  | Operation                | Name    |
--------------------------------------------
|   0 | INSERT STATEMENT         |         |
|   1 |  LOAD TABLE CONVENTIONAL |         |
|   2 |   TABLE ACCESS FULL      | ORIGTBL |
--------------------------------------------

Direct load insert

SQL> insert /*+APPEND*/ into partbl (qty, name) select * from origtbl;

--------------------------------------
| Id  | Operation          | Name    |
--------------------------------------
|   0 | INSERT STATEMENT   |         |
|   1 |  LOAD AS SELECT    |         |
|   2 |   TABLE ACCESS FULL| ORIGTBL |
--------------------------------------

Direct load insert with parallel query part

SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | INSERT STATEMENT      |          |
|   1 |  LOAD AS SELECT       |          |
|   2 |   PX COORDINATOR      |          |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |
|   4 |     PX BLOCK ITERATOR |          |
|*  5 |      TABLE ACCESS FULL| ORIGTBL  |
------------------------------------------

Note LOAD AS SELECT  is above PX COORDINATOR in the execution plan.

Direct load insert with parallel query and insert parts

SQL>alter session enable parallel dml;

SQL> insert /*+APPEND PARALLEL*/ into partbl (qty, name) select * from origtbl;

------------------------------------------
| Id  | Operation             | Name     |
------------------------------------------
|   0 | INSERT STATEMENT      |          |
|   1 |  PX COORDINATOR       |          |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |
|   3 |    LOAD AS SELECT     |          |
|   4 |     PX BLOCK ITERATOR |          |
|*  5 |      TABLE ACCESS FULL| ORIGTBL  |
------------------------------------------

Note LOAD AS SELECT  is below PX COORDINATOR in the execution plan.

An alternative to insert ... select is to use create table as select: creating the partitioned table and loading data into it in one go.
The execution plan shows direct path load with both dml and select parts parallel.

SQL>alter session enable parallel dml;

SQL> create table partbl (qty, name) partition by range (qty) (partition p1 values less than (501),partition p2 values less than (maxvalue))
  2  as select /*+PARALLEL*/ * from origtbl;

-------------------------------------------
| Id  | Operation              | Name     |
-------------------------------------------
|   0 | CREATE TABLE STATEMENT |          |
|   1 |  PX COORDINATOR        |          |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |
|   3 |    LOAD AS SELECT      |          |
|   4 |     PX BLOCK ITERATOR  |          |
|*  5 |      TABLE ACCESS FULL | ORIGTBL  |
-------------------------------------------

C. Partition Exchange method

ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or subpartition) into a non-partitioned table and a non-partitioned table into a partition (or subpartition) of a partitioned table by exchanging their data and index segments. Unless update indexes close the ALTER TABLE ... EXCHANGE PARTITION command is a dictionary operation with no data movement. Further information about this method can be found in the Oracle documentation (e.g. 11.2) and in Note 198120.1.

The steps involved briefly are the following:

1) Create the partitioned table with the required partitions
2) Have the exchange table with the same structure as the partitions of the partitioned table, and the exchange table having the content that you want to exchange with a partition of the partitioned table
3) Alter table exchange partition partition_name with table exchange table

Note that during the exchange all rows of the exchange table must qualify for the partition to be exchanged, otherwise the following error is thrown ORA-14099: all rows in table do not qualify for specified partition.

This is because by default the exchange is done with validation.

Example (based on SCOTT sample schema)
---------

This example creates the exchange table with the same structure as the partitions of the partitioned table p_test.

SQL> CREATE TABLE p_test
2 (sal NUMBER(7,2))
3 PARTITION BY RANGE(sal)
4 (partition emp_p1 VALUES LESS THAN (2000),
5 partition emp_p2 VALUES LESS THAN (4000));

Table created.


SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> CREATE TABLE exchtab1 as SELECT sal FROM emp WHERE sal<2000;

Table created.

SQL> CREATE TABLE exchtab2 as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999;

Table created.

SQL> alter table p_test exchange partition emp_p1 with table exchtab1;

Table altered.

SQL> alter table p_test exchange partition emp_p2 with table exchtab2;

Table altered.

D. DBMS_REDEFINITION

For details see

        Note 472449.1 How To Partition Existing Table Using DBMS_Redefinition
        Note 1481558.1  DBMS_REDEFINITION: Case Study for a Large Non-Partition Table to a Partition Table with Online Transactions occuring
        Note 177407.1 How to Re-Organize a Table Online