11g分区表按时间自动创建

 

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

 

Interval Partitioning（MONTH、YEAR)

 

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>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 fmismain quota unlimited on par01;

alter user fmismain quota unlimited on par02;

alter user fmismain quota unlimited on par03;

alter user fmismain quota unlimited on par04;

alter user fmismain quota unlimited on par05;

alter user fmismain quota unlimited on par06;

alter user fmismain quota unlimited on par07;

alter user fmismain quota unlimited on par08;

alter user fmismain quota unlimited on par09;

alter user fmismain quota unlimited on par010;

alter user fmismain quota unlimited on par011;

alter user fmismain quota unlimited on par012;

 

 

 

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'))

);

 

 

insert into sales12 values (1,'01-jun-07');

 

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

 

 

 

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                FMISMAIN                       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