Partition-方案二：通过 Insert with a subquery 方法

方案二：通过 Insert with a subquery 方法
还是使用方案一中的普通大表WZ_REAL_DATA来进行实验。此种方式需要应用。步骤如下：
1) 创建一个分区表：

SQL>CREATE TABLE WZ_REAL_DATA_tmp   (STIME DATE NOT NULL,    PI_AI_POINT VARCHAR2(30) NOT NULL,    FVALUE NUMBER(16,4),     CONSTRAINT PK_WZ_REAL_DATA2 PRIMARY KEY (STIME,PI_AI_POINT))  partition by range (stime) (partition p1_2012 values less than (to_date('2013-01-01','yyyy-mm-dd')),  partition p1_2013 values less than (to_date('2014-01-01','yyyy-mm-dd')),  partition p1_2014 values less than (to_date('2015-01-01','yyyy-mm-dd')),  partition p1_2015 values less than (to_date('2016-01-01','yyyy-mm-dd')),  partition p_max values less than (maxvalue)) tablespace XDWZJS;

2) 将原来非分区表中的数据通过子查询 insert 到新创建的分区表中：

SQL>alter session enable parallel dml;SQL>insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA;

注意直接插入全部数据容易报undo表空间不足

ERROR at line 1:ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

改成分组提交:

SQL> insert into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2013-01-01','yyyy-mm-dd');insert into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2013-01-01','yyyy-mm-dd')            *ERROR at line 1:ORA-01555: snapshot too old: rollback segment number 19 with name"_SYSSMU19_4086232787$" too small

快照过旧了。。。。再分细一点：

SQL> conn xdwz/qizhengtaoConnected.SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2012-06-01','yyyy-mm-dd');4099797 rows created.SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2013-01-01','yyyy-mm-dd') and stime>to_date('2012-06-01','yyyy-mm-dd');insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2013-01-01','yyyy-mm-dd') and stime>to_date('2012-06-01','yyyy-mm-dd')                                 *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallel

开启并行模式后需先commit才能继续insert

SQL> commit;Commit complete.SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2013-01-01','yyyy-mm-dd') and stime>to_date('2012-06-01','yyyy-mm-dd');8438605 rows created.SQL> commit;SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2013-01-01','yyyy-mm-dd') and stime<to_date('2013-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2013-06-01','yyyy-mm-dd') and stime<to_date('2014-01-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2014-01-01','yyyy-mm-dd') and stime<to_date('2014-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2014-06-01','yyyy-mm-dd') and stime<to_date('2015-01-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2015-01-01','yyyy-mm-dd') and stime<to_date('2015-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2015-06-01','yyyy-mm-dd') and stime<to_date('2016-01-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime>to_date('2016-01-01','yyyy-mm-dd') and stime<to_date('2017-01-01','yyyy-mm-dd');commit;6201127 rows created.SQL> Commit complete.SQL> 7816526 rows created.SQL> Commit complete.SQL> 5485377 rows created.SQL> Commit complete.SQL> 8525824 rows created.SQL> Commit complete.SQL> 4939345 rows created.SQL> Commit complete.SQL> 0 rows created.SQL> Commit complete.SQL> 0 rows created.SQL> Commit complete.SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> select count(*) from WZ_REAL_DATA_tmp;  COUNT(*)----------  45506888SQL> select count(*) from WZ_REAL_DATA_tmp;  COUNT(*)----------  45506601

数据不一致了，发现条件忘了加=号；插入丢失的数据：

insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2012-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2013-01-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2013-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2014-01-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2014-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2015-01-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2015-06-01','yyyy-mm-dd');commit;insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime=to_date('2016-01-01','yyyy-mm-dd');commit;SQL> select count(*) from WZ_REAL_DATA_tmp;  COUNT(*)----------  45506888

3) 如果您想让新建的分区表与原表名相同，那么 drop 掉原来的非分区表然后重命名新表：

SQL> drop table WZ_REAL_DATA purge;SQL> alter table WZ_REAL_DATA_tmp rename to WZ_REAL_DATA;SQL> select table_name,partition_name,partition_position from user_tab_partitions where table_name='WZ_REAL_DATA';TABLE_NAME               PARTITION_NAME              PARTITION_POSITION------------------------------ ------------------------------ ------------------WZ_REAL_DATA               P1_2012                           1WZ_REAL_DATA               P1_2013                           2WZ_REAL_DATA               P1_2014                           3WZ_REAL_DATA               P1_2015                           4WZ_REAL_DATA               P1_2016                           5WZ_REAL_DATA               P_MAX                           66 rows selected.SQL> 

至此实验完成
补充：
可以通过 direct path insert 和利用并行来改善 insert 的性能：
Direct load insert 方式

SQL> insert /*+APPEND*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA;

Direct load insert 并且在查询部分开启并行

SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp  select * from WZ_REAL_DATA;

Direct load insert 并且在查询部分和 insert 部分都开启并行

SQL>alter session enable parallel dml;SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp  select * from WZ_REAL_DATA;