使用Oracle Database 11g建立Interval分割槽表

使用Oracle Database 11g可以建立新型別的Interval分割槽表，Interval型別分割槽表，可以根據載入資料，自動建立指定間隔的分割槽。
比如建立按月分割槽的分割槽表：

Create table intervalpart
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c3)
interval(numtoyminterval(1,'MONTH'))
(
partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
)
/

注意如果你的INTERVAL指定錯誤，則資料庫會報出如下錯誤：

ORA-14752: Interval expression is not a constant of the correct type

下面建立一個以天為間隔的分割槽表：

create table sales
  (
  sales_id    number,
  sales_dt    date
  )
  partition by range (sales_dt)
  INTERVAL (NUMTODSINTERVAL(1,'day'))
  (
  partition p1410101 values less than (to_date('2014-01-01','yyyy-mm-dd'))
 );
 
 create index id_local_index on sales(sales_id) local;
 
 
  begin     
    for i in 0 .. 5 loop
    insert into sales values(i,trunc(sysdate,'YYYY')+i);
    end loop;
    commit;
    end;
    /
    
    
    
  TEST@ sms> select table_name,partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
SALES                          SYS_P109
SALES                          SYS_P108
SALES                          SYS_P110
SALES                          SYS_P111
SALES                          SYS_P112
SALES                          SYS_P113
SALES                          P1410101

7 rows selected.

TEST@ sms> select INDEX_NAME,PARTITION_NAME from user_ind_partitions;

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ID_LOCAL_INDEX                 SYS_P108
ID_LOCAL_INDEX                 SYS_P109
ID_LOCAL_INDEX                 SYS_P110
ID_LOCAL_INDEX                 SYS_P111
ID_LOCAL_INDEX                 SYS_P112
ID_LOCAL_INDEX                 SYS_P113
ID_LOCAL_INDEX                 P1410101

7 rows selected.



TEST@ sms> select * from sales partition(P1410101);

no rows selected

TEST@ sms> select * from sales partition(SYS_P108);

  SALES_ID SALES_DT
---------- ------------------
         0 01-JAN-14

TEST@ sms> select * from sales partition(SYS_P109);

  SALES_ID SALES_DT
---------- ------------------
         1 02-JAN-14  
    
 
 這一特性極大簡化了分割槽表的維護工作。
 
    select to_char(sysdate,'yyyy-mm-dd') from dual;
    
    
     select table_name,partition_name from user_tab_partitions;

    
    
    select INDEX_NAME,PARTITION_NAME from user_ind_partitions;