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/qizhengtao
Connected.
SQL> insert /*+APPEND PARALLEL*/ into WZ_REAL_DATA_tmp select * from WZ_REAL_DATA where stime<to_date('2012-06-01','yyyy-mm-dd'</