1，分区功能扩展
1.1，interval分区
是对范围分区的扩展，限制如下：
*仅支持单列分区，且类型为NUMBER或DATE
*不支持索引组织表（IOT）
*不支持domain index

interval分区支持单层或如下组合分区：
*Interval-range
*Interval-hash
*Interval-list

--测试：
create table interval_part_t
(x int,
 d date
)
partition by range(d)
interval(numtoyminterval(1,'MONTH'))
(partition p2013q1 values less than (to_date('20130401','YYYYMMDD')),
partition p2013q2 values less than (to_date('20130701','YYYYMMDD')),
partition p2013q3 values less than (to_date('20131001','YYYYMMDD')),
partition p2013q4 values less than (to_date('20140101','YYYYMMDD')));

select table_name, partition_name, high_value,interval, segment_created
  from user_tab_partitions
 where table_name = 'INTERVAL_PART_T';
TABLE_NAME       PARTITION_NAME  HIGH_VALUE                                                                       INTERVAL SEGMENT_CREATED
---------------- --------------- -------------------------------------------------------------------------------- -------- ---------------
INTERVAL_PART_T  P2013Q1         TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q2         TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q3         TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q4         TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
==>原始分区并没有创建对应的segment

--插入数据
insert into interval_part_t values(1,to_date('20131201','yyyymmdd'));
commit;

select table_name, partition_name, high_value,interval, segment_created
  from user_tab_partitions
 where table_name = 'INTERVAL_PART_T';

TABLE_NAME       PARTITION_NAME  HIGH_VALUE                                                                       INTERVAL SEGMENT_CREATED
---------------- --------------- -------------------------------------------------------------------------------- -------- ---------------
INTERVAL_PART_T  P2013Q1         TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q2         TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q3         TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q4         TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       YES
==>原始分区创建对应的segment

--插入原始分区不能保存的数据
insert into interval_part_t values(1,to_date('20140501','yyyymmdd'));
commit;

select table_name, partition_name, high_value,interval, segment_created
  from user_tab_partitions
 where table_name = 'INTERVAL_PART_T';
TABLE_NAME       PARTITION_NAME  HIGH_VALUE                                                                       INTERVAL SEGMENT_CREATED
---------------- --------------- -------------------------------------------------------------------------------- -------- ---------------
INTERVAL_PART_T  P2013Q1         TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q2         TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q3         TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO
INTERVAL_PART_T  P2013Q4         TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       YES
INTERVAL_PART_T  SYS_P1061       TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES      YES
==>自动增加分区

--插入原始分区不能保存的数据
insert into interval_part_t values(1,to_date('20140201','yyyymmdd'));
commit;

select table_name, partition_name, high_value,interval, segment_created,partition_position
  from user_tab_partitions
 where table_name = 'INTERVAL_PART_T';
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                       INTERVAL SEGMENT_CREATED PARTITION_POSITION
------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------- --------------- ------------------
INTERVAL_PART_T                P2013Q1                        TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               1
INTERVAL_PART_T                P2013Q2                        TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               2
INTERVAL_PART_T                P2013Q3                        TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               3
INTERVAL_PART_T                P2013Q4                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       YES                              4
INTERVAL_PART_T                SYS_P1062                      TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES      YES                              5
INTERVAL_PART_T                SYS_P1061                      TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES      YES                              6
==>自动增加分区，位置在已有分区中间

1.2，参考分区
通过外键维护，好处：
*避免分区键冗余
*针对父表的分区操作，自动维护子表
*支持基本分区方式，不支持interval分区

--
CREATE TABLE ref_part_order
( x int not null,
  d date,
  constraint pk_ref_part_order primary key(x)
)
PARTITION BY RANGE (d)
( PARTITION p2013q1 VALUES LESS THAN (to_date('20130401','YYYYMMDD'))
, PARTITION p2013q2 VALUES LESS THAN (to_date('20130701','YYYYMMDD'))
, PARTITION p2013q3 VALUES LESS THAN (to_date('20131001','YYYYMMDD'))
, PARTITION p2013q4 VALUES LESS THAN (to_date('20140101','YYYYMMDD'))
, PARTITION pmax VALUES LESS THAN (maxvalue)
);

CREATE TABLE ref_part_order_item
( x int not null,
  xxx varchar2(1000),
  constraint fk_ref_part_order_item foreign key(x) references ref_part_order(x)
)
PARTITION BY REFERENCE (fk_ref_part_order_item);

select table_name, partition_name, high_value,interval, segment_created,partition_position
  from user_tab_partitions
 where table_name in ('REF_PART_ORDER','REF_PART_ORDER_ITEM')
 order by table_name,partition_position;
TABLE_NAME          PARTITION_NAME  HIGH_VALUE                                                                       INTERVAL SEGMENT_CREATED PARTITION_POSITION
------------------- --------------- -------------------------------------------------------------------------------- -------- --------------- ------------------
REF_PART_ORDER      P2013Q1         TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               1
REF_PART_ORDER      P2013Q2         TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               2
REF_PART_ORDER      P2013Q3         TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               3
REF_PART_ORDER      P2013Q4         TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               4
REF_PART_ORDER      PMAX            MAXVALUE                                                                         NO       NO                               5
REF_PART_ORDER_ITEM P2013Q1                                                                                          NO       NO                               1
REF_PART_ORDER_ITEM P2013Q2                                                                                          NO       NO                               2
REF_PART_ORDER_ITEM P2013Q3                                                                                          NO       NO                               3
REF_PART_ORDER_ITEM P2013Q4                                                                                          NO       NO                               4
REF_PART_ORDER_ITEM PMAX                                                                                             NO       NO                               5
 
10 rows selected

--父表的分区维护操作，在子表自动进行
alter table ref_part_order
split partition pmax at (to_date('20140401','YYYYMMDD')) into (partition p2014q1,partition pmax);

select table_name, partition_name, high_value,interval, segment_created,partition_position
  from user_tab_partitions
 where table_name in ('REF_PART_ORDER','REF_PART_ORDER_ITEM')
 order by table_name,partition_position;
TABLE_NAME           PARTITION_NAME HIGH_VALUE                                                                       INTERVAL SEGMENT_CREATED PARTITION_POSITION
-------------------- -------------- -------------------------------------------------------------------------------- -------- --------------- ------------------
REF_PART_ORDER       P2013Q1        TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               1
REF_PART_ORDER       P2013Q2        TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               2
REF_PART_ORDER       P2013Q3        TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               3
REF_PART_ORDER       P2013Q4        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               4
REF_PART_ORDER       P2014Q1        TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO       NO                               5
REF_PART_ORDER       PMAX           MAXVALUE                                                                         NO       NO                               6
REF_PART_ORDER_ITEM  P2013Q1                                                                                         NO       NO                               1
REF_PART_ORDER_ITEM  P2013Q2                                                                                         NO       NO                               2
REF_PART_ORDER_ITEM  P2013Q3                                                                                         NO       NO                               3
REF_PART_ORDER_ITEM  P2013Q4                                                                                         NO       NO                               4
REF_PART_ORDER_ITEM  P2014Q1                                                                                         NO       NO                               5
REF_PART_ORDER_ITEM  PMAX                                                                                            NO       NO                               6
 
12 rows selected

1.3 虚拟列分区
好处：
*分区键不在要求是表中的列，可以是基于多个列的表达式，并不实际存储。
*支持所有基本分区方式，包括interval分区/interval-*分区

--测试
CREATE TABLE virtual_col_part_t
( x int,
  d date,
  vc as (mod(x,5))
)
PARTITION BY LIST (vc)
( PARTITION p0 VALUES (0)
, PARTITION p1 VALUES (1)
, PARTITION p2 VALUES (2)
, PARTITION p3 VALUES (3)
, PARTITION p4 VALUES (4)
);

select column_name, segment_column_id, internal_column_id, virtual_column
  from user_tab_cols
 where table_name = 'VIRTUAL_COL_PART_T';
COLUMN_NAME                    SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID VIRTUAL_COLUMN
------------------------------ ----------------- ------------------ --------------
X                                              1                  1 NO
D                                              2                  2 NO
VC                                                                3 YES

select table_name, partition_name, high_value,interval, segment_created,partition_position
  from user_tab_partitions
 where table_name = 'VIRTUAL_COL_PART_T';
TABLE_NAME          PARTITION_NAME HIGH_VALUE  INTERVAL SEGMENT_CREATED PARTITION_POSITION
------------------- -------------- ----------- -------- --------------- ------------------
VIRTUAL_COL_PART_T  P0             0           NO       NO                               1
VIRTUAL_COL_PART_T  P1             1           NO       NO                               2
VIRTUAL_COL_PART_T  P2             2           NO       NO                               3
VIRTUAL_COL_PART_T  P3             3           NO       NO                               4
VIRTUAL_COL_PART_T  P4             4           NO       NO                               5
 

insert into virtual_col_part_t(x,d) values(1,sysdate);
insert into virtual_col_part_t(x,d) values(5,sysdate);
insert into virtual_col_part_t(x,d) values(8,sysdate);
commit;

select table_name, partition_name, high_value,interval, segment_created,partition_position
  from user_tab_partitions
 where table_name = 'VIRTUAL_COL_PART_T';
TABLE_NAME          PARTITION_NAME HIGH_VALUE  INTERVAL SEGMENT_CREATED PARTITION_POSITION
------------------- -------------- ----------- -------- --------------- ------------------
VIRTUAL_COL_PART_T  P0             0           NO       YES                              1
VIRTUAL_COL_PART_T  P1             1           NO       YES                              2
VIRTUAL_COL_PART_T  P2             2           NO       NO                               3
VIRTUAL_COL_PART_T  P3             3           NO       YES                              4
VIRTUAL_COL_PART_T  P4             4           NO       NO                               5