参考http://www.oradbca.com/326.html
对时间类型和数字类型的支持:
1.对于采用date类型或TIMESTAMP类型的Interval Partitioning可以支持按year、month、day、hour、minute、second六种类型的定长宽度分区,分别通过如下函数转换
numtoyminterval ( n, { 'YEAR'|'MONTH'})
numtodsinterval ( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})
2. Interval (number) 即多少值一个分区
区间分区仅限于一个单一的分区键必须是一个数值或日期范围。
在表被创建时,必须要初始化一个分区
区间分区不支持索引组织表
不能在区间分区表上创建domain索引
区间分区能用于按逐渐自动增长的机制分区,但是不能设置分区级别
不能再去见分区表上设置maxvalue
partition 列不能为空
时间分区1:
create table WEB_AD_DATA
(
AID VARCHAR2(4000),
URL VARCHAR2(4000),
REFERRER_URL VARCHAR2(4000),
IP_EX VARCHAR2(4000),
WEBSITE_ID VARCHAR2(4000),
NDS VARCHAR2(1000),
DESCRIPTION VARCHAR2(4000),
ACTION INTEGER,
ACTION_TIME DATE,
WINDOWS VARCHAR2(512),
SYS_DATE DATE default sysdate,
PID VARCHAR2(200),
SID VARCHAR2(64),
CID VARCHAR2(200),
)
tablespace web_ad_TBS
partition by range (SYS_DATE)
interval(numtodsinterval(1,'day'))
(partition p1 values less than(to_date('2014-05-01','yyyy-mm-dd')));
时间分区2:
create table WEB_SURVEY_DATA
(
USER_ID VARCHAR2(4000),
URL VARCHAR2(4000),
REFERRER_URL VARCHAR2(4000),
IP_EX VARCHAR2(4000),
WEBSITE_ID VARCHAR2(4000),
TITLE VARCHAR2(1000),
DESCRIPTION VARCHAR2(4000),
ACTION INTEGER,
ACTION_TIME DATE,
WINDOWS VARCHAR2(512),
SYS_DATE DATE default sysdate,
DATA_TYPE INTEGER,
USER_AGENT VARCHAR2(512),
SCREEN_RESOLUTION VARCHAR2(100),
TRACKER_ID VARCHAR2(200),
AD_GROUP_ID VARCHAR2(64),
AID VARCHAR2(200),
)
tablespace SURVEY_TBS
partition by range (sys_date)
interval (numtoyminterval(1,'MONTH'))
(
partition p1401 values less than (to_date('2014-05-01','yyyy-mm-dd'))
);
数字分区:
SQL> CREATE TABLE oradbca.intervalpart2 (c1 NUMBER, c2 DATE) 2 PARTITION BY RANGE (c1) 3 INTERVAL (100) 4 (PARTITION part1 5 VALUES LESS THAN (100) 6 );