数据库Oracle11g分区表按时间自动创建(Interval Partitioning)

11g新特性_分区表按时间自动创建(Interval Partitioning（MONTH、YEAR))

Interval Partitioning（MONTH、YEAR)

[color=darkred][i]create table sales6
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);

SQL> [color=darkred][i]insert into sales6 values (1,'01-jun-07');
1 row created.

SQL> select partition_name, high_value from user_tab_partitions where table_name = 'SALES6';

PARTITION_NAME HIGH_VALUE
-------------------- ----------------------------------------
P0701 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P23 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

注意新分区 SYS_P42，其上限为 6 月 1 日，因此该分区可以保留 2006 年 5 月的数据。该分区是通过拆分 SYS_P41 分区创建的（针对 6 月份）。因此，当您定义一个间隔分区方案时，Oracle 会自动创建和维护分区。

如果您希望将分区存储在特定表空间中，可以使用 store in 子句执行该操作：

interval (numtoyminterval(1,'MONTH'))
store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par10,par011,par012)

该子句以循环方式将分区存储在表空间 TS1、TS2 和 TS3 中。

如：

create tablespace par01 datafile '+DGDATGA' size 10m ;
create tablespace par02 datafile '+DGDATGA' size 10m ;
create tablespace par03 datafile '+DGDATGA' size 10m ;
create tablespace par04 datafile '+DGDATGA' size 10m ;
create tablespace par05 datafile '+DGDATGA' size 10m ;
create tablespace par06 datafile '+DGDATGA' size 10m ;
create tablespace par07 datafile '+DGDATGA' size 10m ;
create tablespace par08 datafile '+DGDATGA' size 10m ;
create tablespace par09 datafile '+DGDATGA' size 10m ;
create tablespace par010 datafile '+DGDATGA' size 10m ;
create tablespace par011 datafile '+DGDATGA' size 10m ;
create tablespace par012 datafile '+DGDATGA' size 10m ;

alter user fmics quota unlimited on par01;
alter user fmics quota unlimited on par02;
alter user fmics quota unlimited on par03;
alter user fmics quota unlimited on par04;
alter user fmics quota unlimited on par05;
alter user fmics quota unlimited on par06;
alter user fmics quota unlimited on par07;
alter user fmics quota unlimited on par08;
alter user fmics quota unlimited on par09;
alter user fmics quota unlimited on par010;
alter user fmics quota unlimited on par011;
alter user fmics quota unlimited on par012;

[color=darkred][i]create table sales12
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
store in (par01,par02,par03,par04,par05,par06,par07,par08,par09,par010,par011,par012)
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);

[color=darkred][i]insert into sales12 values (1,'01-jun-07');

select partition_name, high_value from user_tab_partitions where table_name = 'SALES12';

[color=darkred][i]insert into sales12 values (1,'01-may-07');

select table_name,partition_name,tablespace_name,high_value from user_tab_partitions where table_name = 'SALES12';

SQL> select table_name,partition_name,tablespace_name,high_value from user_tab_partitions where table_name = 'SALES12';

TABLE_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
-------------------- -------------------- ------------------------------ ----------------------------------------
SALES12 P0701 FMICS TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SALES12 SYS_P41 PAR05 TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SALES12 SYS_P25 PAR06 TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB SYS_P21 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB SYS_P22 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 5
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA