非分区表进行成分区的方法，From Oracle support：(文档 ID 1985005.1)
A. 通过 Export/import 方法
B. 通过 Insert with a subquery 方法
C. 通过 Partition Exchange 方法
D. 通过 DBMS_REDEFINITION 方法
A. 通过 Export/import 方法
这种方法的实现是先 export 一个非分区表，创建一个新的分区表，然后 import 数据到新创建的分区表中。
1) Export 您的非分区表：
$ exp usr/pswd tables=numbers file=exp.dmp
2) Drop 掉该非分区表：
SQL> drop table numbers;
3) 重新创建该表成为一个分区表：
SQL> create table numbers (qty number(3), name varchar2(15)) partition by range (qty)(partition p1 values less than (501),
partition p2 values less than (maxvalue));
4) 通过 import 的 ignore=y 方式来还原备份的数据：
$ imp usr/pswd file=exp.dmp ignore=y
ignore=y 语句会让 import 忽略掉表的创建，直接加载所有数据。
如果使用 Data Pump export/import（expdp/impdp）您可以采用 impdp 的 table_exists_action 选项，例如 table_exists_action = APPEND 或者 table_exists_action = REPLACE。
您也可以参考 Note 552424.1 Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce a Method of Loading and Unloading Data？
B. 通过 Insert with a subquery 方法
1) 创建一个分区表：
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 到新创建的分区表中：
SQL> insert into partbl (qty, name) select * from origtbl;
3) 如果您想让新建的分区表与原表名相同，那么 drop 掉原来的非分区表然后重命名新表：
SQL> drop table origtbl;
SQL> alter table partbl rename to origtbl;
您可以通过 direct path insert 和利用并行来改善 insert 的性能。如下的例子演示了如何实现并且如何从执行计划中来验证。
传统的 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 并且在查询部分开启并行
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 |
------------------------------------------
注意以上执行计划中 LOAD AS SELECT 在 PX COORDINATOR 的上面。
Direct load insert 并且在查询部分和 insert 部分都开启并行
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 |
------------------------------------------
注意在以上执行计划中 LOAD AS SELECT 在 PX COORDINATOR 的下面。
另外一种可选的方式是直接通过 select 来创建新的分区表：一次性创建新的分区表并且加载数据。
执行计划同时显示 direct path load 并且 dml 以及 select 部分全部并行。
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 方法
ALTER TABLE EXCHANGE PARTITION 可以通过交换数据和索引 segment 来将一个分区（或子分区）转换成一个非分区表，也可以将一个非分区表转换成一个分区表的分区（或子分区）。 除了需要更新索引以外，ALTER TABLE ... EXCHANGE PARTITION 命令是一个字典操作不需要数据移动。更多关于此方法的信息参见 Oracle 联机文档（比如 11.2）和 Note 198120.1。
此方法简要步骤如下：
1) 根据所需的分区来创建新的分区表
2) 保持需要交换的非分区表与分区表的分区有相同的结构，并且确保您需要交换的非分区表具有您想要交换的内容
3) 执行：Alter table exchange partition partition_name with table exchange table
注意在交换过程中，所有交换的数据必须满足分区表的分区定义，否则如下错误将抛出：ORA-14099: all rows in table do not qualify for specified partition.
这是因为默认情况下分区交换是有校验的。
例子（基于 SCOTT 示例 schema）
---------
本例创建了与分区表 p_emp 的分区相同结构的交换表。
SQL> CREATE TABLE p_emp
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.
--- 官方这里应该是有错误的，应该将where后的sal（加粗部分） 改成 * 才能实现与原emp表一致的分区----
SQL> alter table p_emp exchange partition emp_p1 with table exchtab1;
Table altered.
SQL> alter table p_emp exchange partition emp_p2 with table exchtab2;
Table altered.
D. 通过 DBMS_REDEFINITION 方法
(文档 ID 1481558.1)
PURPOSE
This Note has been written to provide some volume details and timings for the process
to convert a normal table to a partition table using the DBMS_REDEFINITION package.
The note will also include details of objects used by the process (Fast Refresh) and how
these become populated during the procedure.
The procedure is broken down into the following sections:
1. Create the Partition Table structure required, known as the Interim table.
2. Execute DBMS_REDEFINITION.can_redef_table...
3. Execute DBMS_REDEFINITION.start_redef_table...
4. Execute DBMS_REDEFINITION.sync_interim_table...
5. Execute DBMS_REDEFINITION.finish_redef_table...
6. Drop the interim table.
DETAILS
Worked example under 11.2.0.3
-- Initial setup of table to be partitioned.
CREATE TABLE unpar_table (
a NUMBER, y number,
name VARCHAR2(100), date_used date);
alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y));
-- load table with 1,000,000 rows
begin
for i in 1 .. 1000
loop
for j in 1 .. 1000
loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
commit;
PL/SQL procedure successfully completed.
Elapsed: 00:01:56.90
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats(user, 'unpar_table', cascade => TRUE);
SELECT num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE';
NUM_ROWS
----------
1000000
Elapsed: 00:00:00.01
SQL> CREATE TABLE par_table (
a NUMBER, y number,
name VARCHAR2(100),date_used DATE)
PARTITION BY RANGE (date_used)
(PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/07/2012', 'DD/MM/YYYY')),
PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/07/2012', 'DD/MM/YYYY')),
PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));
SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
-- This procedure (DBMS_REDEFINITION.start_redef_table) creates a materialized view based on a CTAS, as we can see below with
-- the PREBUILT container table.
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.69
select mview_name,container_name, build_mode from user_mviews;
MVIEW_NAME CONTAINER_NAME BUILD_MOD
------------------------------ ------------------------------ ---------
PAR_TABLE PAR_TABLE PREBUILT
Elapsed: 00:00:00.13
-- Insert 1000 rows into the master table while the DBMS_REDEF is active.
-- This will use the mview log created by the DBMS_REDEFINITION.start_redef_table.
-- Check the MLOG$_<table name> table to confirm these online updates have been recorded.
SQL> begin
for i in 1001 .. 1010
loop
for j in 1001 .. 1100
loop
insert into unpar_table values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
commit;
Elapsed: 00:00:00.24
SQL> select count(*) from MLOG$_UNPAR_TABLE;
COUNT(*)
----------
1000
-- Run the dbms_redefinition.sync_interim_table to populate the new table structure (implements a MVIEW FAST REFRESH)
-- with the online updates. This will purge the mview log of each record applied.
-- This can be run many times and should be, before we do the Finish_REDEF_TABLE.
SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.01
ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y));
EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);
-- Finish_redef_table swaps the table names so the interim table becomes the original table name.
-- After completing this step, the original table is redefined with the attributes and data of the interim table.
-- The original table is locked briefly during this procedure.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/
-- Note, both tables will now be synchronised.
select count(*) from par_table ;
COUNT(*)
----------
1001000
select count(*) from unpar_table ;
COUNT(*)
----------
1001000
-- Dictionary views to confirm the change of strructure of our original table "UNPAR_TABLE".
SELECT partitioned FROM user_tables WHERE table_name = 'UNPAR_TABLE';
PAR
---
YES
SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNPAR_TABLE';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
UNPAR_TABLE_12 980000
UNPAR_TABLE_15 5000
UNPAR_TABLE_MX 16000
-- At this point the Interim table can be dropped.
drop TABLE par_table cascade constraints;
Note for previous versions.
For tests done under 11.1.0.7 the timings for the sync_interim_table where the interim table is large (1,000,000 in our case) the timings for this are considerably longer. This was not reviewed in depth, but initial investigation looks like the mview refresh for DBMS_REDEFINITION has been updated during the MERGE cycle.
example:
11.1.0.7
SYNC of 1000 rows -- Elapsed: 00:01:36.30 (1.5 minutes)
4359
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
