如何将一个普通的非分区表进行分区2 Insert with a subquery 方法
参考文档：
如何将一个普通的非分区表进行分区 (Doc ID 1985005.1)
第二种方式： 通过 Insert with a subquery 方法
- 创建一个分区表：
SQL>
CREATE TABLE tt (
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/05/2015’, ‘DD/MM/YYYY’)),
PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE(‘15/05/2015’, ‘DD/MM/YYYY’)),
PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));
- 将原来非分区表中的数据通过子查询 insert 到新创建的分区表中（也可以抽取部分列）：
SQL> insert into tt select * from unpar_table;
- 如果您想让新建的分区表与原表名相同，那么 drop 掉原来的非分区表然后重命名新表：
SQL> drop table unpar_table;
SQL> alter table tt rename to unpar_table;
您可以通过 direct path insert 和利用并行来改善 insert 的性能。如下的例子演示了如何实现并且如何从执行计划中来验证。
方式1 :传统的 insert
SQL> set autotrace on
SQL> insert into tt select * from unpar_table;
1002111 rows created.
Execution Plan
Plan hash value: 3460395448
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | INSERT STATEMENT | | 1002K| 25M| 1395 (1)| 00:00:01 | | |
| 1 | LOAD TABLE CONVENTIONAL | TT | | | | | | |
| 2 | PARTITION RANGE ALL | | 1002K| 25M| 1395 (1)| 00:00:01 | 1 | 3 |
| 3 | TABLE ACCESS FULL | UNPAR_TABLE | 1002K| 25M| 1395 (1)| 00:00:01 | 1 | 3 |
方式2 : Direct load insert 方式
insert /+APPEND/ into tt select * from unpar_table;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ‘ALLSTATS LAST’));
insert /+APPEND/ into tt select * from unpar_table
Plan hash value: 686678525
| Id | Operation | Name | ERows | OMem | 1Mem | UsedMem |
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | TT | | 2070K| 2070K| 2070K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1002K| 256K| 256K| |
| 3 | PARTITION RANGE ALL | | 1002K| | | |
| 4 | TABLE ACCESS FULL | UNPAR_TABLE | 1002K| | | |
方式3 :Direct load insert 并且在查询部分开启并行
SQL> insert /+APPEND PARALLEL/ into tt select * from unpar_table;
insert /+APPEND PARALLEL/ into tt select * from unpar_table
Plan hash value: 1661748588
| Id | Operation | Name | ERows | OMem | 1Mem | UsedMem |
| 0 | INSERT STATEMENT | | | | | |
| 1 | LOAD AS SELECT | TT | | 2070K| 2070K| 2070K (0)|
| 2 | PX COORDINATOR | | | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1002K| | | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 1002K| 256K| 256K| 512K (0)|
| 5 | PX BLOCK ITERATOR | | 1002K| | | |
|* 6 | TABLE ACCESS FULL | UNPAR_TABLE | 1002K| | | |
注意以上执行计划中 LOAD AS SELECT 在 PX COORDINATOR 的上面。
方式4 :Direct load insert 并且在查询部分和 insert 部分都开启并行
SQL> alter session enable parallel dml;
SQL> insert /+APPEND PARALLEL/ into tt select * from unpar_table;
insert /+APPEND PARALLEL/ into tt select * from unpar_table
Plan hash value: 139931203
| Id | Operation | Name | ERows | OMem | 1Mem | UsedMem |
| 0 | INSERT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1002K| | | |
| 3 | LOAD AS SELECT (HIGH WATER MARK BROKERED)| TT | | 2070K| 2070K| 2070K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 1002K| 256K| 256K| 512K (0)|
| 5 | PX BLOCK ITERATOR | | 1002K| | | |
|* 6 | TABLE ACCESS FULL | UNPAR_TABLE | 1002K| | | |
注意在以上执行计划中 LOAD AS SELECT 在 PX COORDINATOR 的下面。
方式5 :
另外一种可选的方式是直接通过 select 来创建新的分区表：一次性创建新的分区表并且加载数据。
执行计划同时显示 direct path load 并且 dml 以及 select 部分全部并行。
SQL> alter session enable parallel dml;
CREATE TABLE ttt ( a , y , name ,date_used ) PARTITION BY RANGE
(date_used) (PARTITION unpar_table_12 VALUES LESS THAN
(TO_DATE(‘10/05/2015’, ‘DD/MM/YYYY’)), PARTITION unpar_table_15 VALUES
LESS THAN (TO_DATE(‘15/05/2015’, ‘DD/MM/YYYY’)), PARTITION
unpar_table_MX VALUES LESS THAN (MAXVALUE)) as select /+PARALLEL/ *
from unpar_table
Plan hash value: 139931203
| Id | Operation | Name | ERows | OMem | 1Mem | UsedMem |
| 0 | CREATE TABLE STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1002K| | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TTT | | 2070K| 2070K| 2070K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 1002K| 256K| 256K| 128K (0)|
| 5 | PX BLOCK ITERATOR | | 1002K| | | |
|* 6 | TABLE ACCESS FULL | UNPAR_TABLE | 1002K| | | |
