使用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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27036311/viewspace-1302473/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27036311/viewspace-1302473/