Oracle普通表轉(zhuǎn)分區(qū)表的幾種方式

**轉(zhuǎn)載自 MOS 文檔1070693.6 ：How to Partition a Non-partitioned / Regular / Normal Table (Doc ID 1070693.6)?? ?**

Partitioning a regular/non-partitioned table can be done in five ways:

A. Export/import method B. Insert with a subquery method C. Partition Exchange method D. DBMS_REDEFINITION E. MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)

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 / 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 / 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

E. MODIFY clause added to the ALTER TABLE SQL Statement. (From 12.2)

From 12.2, Alter Table MODIFY clause can be used to convert?non-partitioned table to a partitioned table.

For More details, Please see?https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/evolve-nopartition-table.html#GUID-5FDB7D59-DD05-40E4-8AB4-AF82EA0D0FE5

Oracle

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。