--间隔分区-- 时间间隔函数-- 年,月 转换一个指定的 DEC 类型值到 INTERVAL YEAR TO MONTHSELECT NUMTOYMINTERVAL(N,INTERVAL_UNIT);SELECT SYSDATE+NUMTOYMINTERVAL(1,'YEAR');--按年 间隔分区CREATETABLE TYEAR(ID INT,NAME VARCHAR(20),BIR DATE)PARTITIONBY RANGE(BIR)INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))(PARTITION P1990 VALUES LESS THAN (TO_DATE('1991-01-01','YYYY-MM-DD')),PARTITION P1991 VALUES LESS THAN (TO_DATE('1992-01-01','YYYY-MM-DD')));INSERTINTO TYEAR VALUES(1,'AAAAA','1990-03-01');COMMIT;--存入P1990分区表中INSERTINTO TYEAR VALUES(2,'BBBBB','1995-03-01');COMMIT;--存入自动新建分区表中
2 按 月 - 间隔分区
SELECT SYSDATE+NUMTOYMINTERVAL(1,'MONTH');--按月 间隔分区CREATETABLE TMONTH(ID INT,NAME VARCHAR(20),BIR DATE)PARTITIONBY RANGE(BIR)INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))(PARTITION P9101 VALUES LESS THAN (TO_DATE('1991-02-01','YYYY-MM-DD')),PARTITION P9102 VALUES LESS THAN (TO_DATE('1991-03-01','YYYY-MM-DD')));INSERTINTO TMONTH VALUES(1,'AAAAA','1991-01-01');COMMIT;--存入P9101分区表中INSERTINTO TMONTH VALUES(2,'BBBBB','1991-05-01');COMMIT;--存入自动新建分区表中
3 按 日 - 间隔分区
SELECT NUMTODSINTERVAL(N,INTERVAL_UNIT);SELECT SYSDATE+NUMTODSINTERVAL(1,'DAY');--按 日 间隔分区-- 日 转换一个指定的 DEC 类型到 INTERVAL DAY TO SECONDSELECT NUMTODSINTERVAL(N,INTERVAL_UNIT);SELECT SYSDATE+NUMTODSINTERVAL(1,'DAY');CREATETABLE TDAY(ID INT,NAME VARCHAR(20),BIR TIMESTAMP)PARTITIONBY RANGE(BIR)INTERVAL(NUMTODSINTERVAL(1,'DAY'))(PARTITION P910101 VALUES LESS THAN (TO_DATE('1991-01-02 00:00:00','YYYY-MM-DD HH24:MI:SS')),PARTITION P910102 VALUES LESS THAN (TO_DATE('1991-01-03 00:00:00','YYYY-MM-DD HH24:MI:SS')));INSERTINTO TDAY VALUES(1,'AAAAA','1991-01-01 00:00:00');COMMIT;--存入P910101分区表中INSERTINTO TDAY VALUES(2,'BBBBB','1991-01-05 00:00:00');COMMIT;--存入自动新建分区表中
4 按 数值 - 间隔分区表
--间隔分区表--数值间隔CREATETABLE T_R11
(EMPLOYEE_ID INT,
EMPLOYEE_NAME VARCHAR(20),
BIR DATETIME)PARTITIONBY RANGE(EMPLOYEE_ID)INTERVAL(10)(PARTITION P1990 VALUES LESS THAN(10));insertinto SYSDBA.T_R11 VALUES(9,'AAA','1990-01-01');COMMIT;--存入P1990分区表中insertinto SYSDBA.T_R11 VALUES(10,'AAA','1990-01-01');COMMIT;--存入自动新建的分区表中insertinto SYSDBA.T_R11 VALUES(20,'AAA','1990-01-01');COMMIT;--存入再次新建的分区表中