分区(Partition)一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。在10g的Oracle版本中，要对分区表做调整，尤其是对RANGE分区添加新的分区都需要DBA手动定期添加，或都使用存储过程进行管理。在11G的版本中的Interval Partition不再需要DBA去干预新分区的添加，Oracle会自动去执行这样的操作，减少了DBA的工作量。Interval Partition是Range分区的一个扩展。
1、Extended Composite Partitioning
In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:
Range-Hash (available since 8i)
Range-List (available since 9i)
Range-Range
List-Range
List-Hash
List-List
Range-List (available since 9i)
Range-Range
List-Range
List-Hash
List-List
2、使用Interval Partition也有一些限制
You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval-partitioned table.
Interval partitioning is not supported at the subpartition level. ...
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval-partitioned table.
Interval partitioning is not supported at the subpartition level. ...
3、Interval Partition也可以创建复合分区
Interval-range
Interval-hash
Interval-list
Interval-hash
Interval-list
4、复合分区例子
1） Interval-list
-- Create table
--drop table ISMP.GWTRXS purge;
create table ISMP.GWTRXS
(
GWORDERS_ID VARCHAR2(22),
TRXNUM VARCHAR2(128) not null,
TRXTYPE VARCHAR2(4) not null,
CHANNEL VARCHAR2(2) not null,
PAYMENT_TYPE VARCHAR2(2) not null,
PAYMODE VARCHAR2(2) not null,
AMOUNT NUMBER not null,
CURRENCY VARCHAR2(4) not null,
SERVICECODE VARCHAR2(20),
ACQUIRER_CODE VARCHAR2(30) not null,
ACQUIRER_NAME VARCHAR2(400),
ACQUIRER_MERCHANT VARCHAR2(64) not null,
ACQUIRER_SEQ VARCHAR2(128),
ACQUIRER_DATE VARCHAR2(8),
ACQUIRER_MSG VARCHAR2(256),
SUBMITDATES VARCHAR2(20) not null,
PAYER_IP VARCHAR2(20),
REFNUM VARCHAR2(128),
AUTHCODE VARCHAR2(6),
FROMACCTID VARCHAR2(22),
FROMACCTNUM VARCHAR2(256) not null,
BUYER_ID VARCHAR2(22),
BUYER_NAME VARCHAR2(64),
PAYINFO VARCHAR2(64),
CREATEDATE DATE not null,
CLOSEDATE DATE not null,
TRXSTS VARCHAR2(3) not null,
OPERS VARCHAR2(128),
OPERDATE DATE not null,
VERSION NUMBER not null,
TRXDESC VARCHAR2(100),
ID VARCHAR2(22) not null,
ACQUIRER_ID VARCHAR2(20),
FEE_AMOUNT NUMBER,
TRADE_NO VARCHAR2(255 CHAR),
SUCCESS_TYPE VARCHAR2(255 CHAR),
CHANNEL_RESPONSE VARCHAR2(511),
CHANNEL_RESULT VARCHAR2(20),
CHANNEL_RESPONSE_TIME VARCHAR2(20),
DBTYPE VARCHAR2(30)
)
--drop table ISMP.GWTRXS purge;
create table ISMP.GWTRXS
(
GWORDERS_ID VARCHAR2(22),
TRXNUM VARCHAR2(128) not null,
TRXTYPE VARCHAR2(4) not null,
CHANNEL VARCHAR2(2) not null,
PAYMENT_TYPE VARCHAR2(2) not null,
PAYMODE VARCHAR2(2) not null,
AMOUNT NUMBER not null,
CURRENCY VARCHAR2(4) not null,
SERVICECODE VARCHAR2(20),
ACQUIRER_CODE VARCHAR2(30) not null,
ACQUIRER_NAME VARCHAR2(400),
ACQUIRER_MERCHANT VARCHAR2(64) not null,
ACQUIRER_SEQ VARCHAR2(128),
ACQUIRER_DATE VARCHAR2(8),
ACQUIRER_MSG VARCHAR2(256),
SUBMITDATES VARCHAR2(20) not null,
PAYER_IP VARCHAR2(20),
REFNUM VARCHAR2(128),
AUTHCODE VARCHAR2(6),
FROMACCTID VARCHAR2(22),
FROMACCTNUM VARCHAR2(256) not null,
BUYER_ID VARCHAR2(22),
BUYER_NAME VARCHAR2(64),
PAYINFO VARCHAR2(64),
CREATEDATE DATE not null,
CLOSEDATE DATE not null,
TRXSTS VARCHAR2(3) not null,
OPERS VARCHAR2(128),
OPERDATE DATE not null,
VERSION NUMBER not null,
TRXDESC VARCHAR2(100),
ID VARCHAR2(22) not null,
ACQUIRER_ID VARCHAR2(20),
FEE_AMOUNT NUMBER,
TRADE_NO VARCHAR2(255 CHAR),
SUCCESS_TYPE VARCHAR2(255 CHAR),
CHANNEL_RESPONSE VARCHAR2(511),
CHANNEL_RESULT VARCHAR2(20),
CHANNEL_RESPONSE_TIME VARCHAR2(20),
DBTYPE VARCHAR2(30)
)
partition by range (CREATEDATE) INTERVAL (numtodsinterval(1,'day'))
subpartition by list (DBTYPE)
SUBPARTITION TEMPLATE
(SUBPARTITION sub1 VALUES ('bailian'),
SUBPARTITION sub2 VALUES ('yisheng'),
SUBPARTITION sub3 VALUES ('mpayys'),
SUBPARTITION sub4 VALUES ('yixuan'),
SUBPARTITION sub5 VALUES ('yixuan17'),
SUBPARTITION sub6 VALUES ('xifupay'),
SUBPARTITION other VALUES (DEFAULT))
(partition P1 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
subpartition by list (DBTYPE)
SUBPARTITION TEMPLATE
(SUBPARTITION sub1 VALUES ('bailian'),
SUBPARTITION sub2 VALUES ('yisheng'),
SUBPARTITION sub3 VALUES ('mpayys'),
SUBPARTITION sub4 VALUES ('yixuan'),
SUBPARTITION sub5 VALUES ('yixuan17'),
SUBPARTITION sub6 VALUES ('xifupay'),
SUBPARTITION other VALUES (DEFAULT))
(partition P1 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
2）增加子分区模板
注意：新增的子分区模板，只有新的分区生成时，才会被使用到，旧的分区的子分区无法根据新的模板进行分裂，
因此，模板中要保留default分区。
ALTER table ismp.gworders SET
SUBPARTITION TEMPLATE (
SUBPARTITION sub1 VALUES ('bailian'),
SUBPARTITION sub2 VALUES ('yisheng'),
SUBPARTITION sub3 VALUES ('mpayys'),
SUBPARTITION sub4 VALUES ('yixuan'),
SUBPARTITION sub5 VALUES ('yixuan17'),
SUBPARTITION sub6 VALUES ('xifupay'),
SUBPARTITION sub7 VALUES ('nfooo'),
SUBPARTITION TEMPLATE (
SUBPARTITION sub1 VALUES ('bailian'),
SUBPARTITION sub2 VALUES ('yisheng'),
SUBPARTITION sub3 VALUES ('mpayys'),
SUBPARTITION sub4 VALUES ('yixuan'),
SUBPARTITION sub5 VALUES ('yixuan17'),
SUBPARTITION sub6 VALUES ('xifupay'),
SUBPARTITION sub7 VALUES ('nfooo'),
SUBPARTITION other VALUES (DEFAULT)
);
);
原文：https://blog.csdn.net/jolly10/article/details/82620802