Oracle 11g创建Interval分区表
来源:互联网 发布:广州淘宝运营工资 编辑:程序博客网 时间:2024/05/20 08:42
1.
interval使用之后,就不用手工增加分区,来适应数据的增长。
2.
根据年月 INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天 INTERVAL(NUMTODSINTERVAL(1,'DAY'))
3.
CREATE TABLE hr.int_par(
id number primary key,
join_date date )
partition by range(join_date) interval (numtoyMinterval (1,'YEAR'))
--store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par010,par011,par012)
(
partition inte_par1 VALUES LESS THAN (to_date('2000-9-9','YYYY-MM-DD'))
)
4.
BEGIN
FOR i IN 1..10 LOOP
EXECUTE IMMEDIATE 'INSERT INTO hr.int_par VALUES (:1,:2)'
USING i, trunc(to_date('2000-9-9','YYYY-MM-DD'),'yyyy')+366*i ;
END LOOP;
COMMIT;
END;
/
5.
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION
------------------------------------------------------------------------------------------------------------ ------------------- --
INT_PAR SYS_P50 TO_DATE(' 2010-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 11
INT_PAR SYS_P49 TO_DATE(' 2009-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 10
INT_PAR SYS_P48 TO_DATE(' 2008-09-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
2.
3.
4.
5.
TABLE_NAME
------------------------------------------------------------------------------------------------------------ ------------------- --