如何将一个普通的非分区表进行分区2 Insert with a subquery 方法

## 参考文档：

如何将一个普通的非分区表进行分区 (Doc ID 1985005.1)

第二种方式： 通过 Insert with a subquery 方法

1) 创建一个分区表：

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));

2) 将原来非分区表中的数据通过子查询 insert 到新创建的分区表中（也可以抽取部分列）：

SQL> insert into tt  select * from unpar_table;

3) 如果您想让新建的分区表与原表名相同，那么 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        | E*Rows |  OMem |  1Mem | Used*Mem |

********************************************************************************************

|   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 | E*Rows |  OMem |  1Mem | Used*Mem |

**********************************************************************************************

|   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   | E*Rows |  OMem |  1Mem | Used*Mem |

*******************************************************************************************************

|   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 | E*Rows |  OMem |  1Mem | Used*Mem |

**********************************************************************************************

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

**********************************************************************************************