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.文章来源：https://www.toymoban.com/news/detail-499876.html
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文章来源地址https://www.toymoban.com/news/detail-499876.html
到了这里，关于parallel dml enable的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
