ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用Oracle 11g创建Interval分区表(转)

使用Oracle 11g创建Interval分区表(转)

原创 Linux操作系统 作者:弦音有你 时间:2012-06-22 10:43:56 0 删除 编辑

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

下面创建一个以天为间隔的分区表:

SQL> create table sales
2 (
3 sales_id number,
4 sales_dt date
5 )
6 partition by range (sales_dt)
7 INTERVAL (NUMTODSINTERVAL(1,'day'))
8 (
9 partition p090101 values less than (to_date('2009-01-01','yyyy-mm-dd'))
10 );

Table created.

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES P090101
SYS_FBA_HIST_16585 HIGH_PART

插入测试数据:

SQL> begin
2 for i in 1 .. 35 loop
3 insert into sales values(i,trunc(sysdate,'YYYY')+i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

观察自动创建的分区:

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES SYS_P47
SALES SYS_P48
SALES SYS_P49
SALES SYS_P50
SALES SYS_P51
SALES SYS_P52
SALES SYS_P53
SALES SYS_P54
SALES SYS_P55
SALES P090101
SALES SYS_P21

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES SYS_P22
SALES SYS_P23
SALES SYS_P24
SALES SYS_P25
SALES SYS_P26
SALES SYS_P27
SALES SYS_P28
SALES SYS_P29
SALES SYS_P30
SALES SYS_P31
SALES SYS_P32

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES SYS_P33
SALES SYS_P34
SALES SYS_P35
SALES SYS_P36
SALES SYS_P37
SALES SYS_P38
SALES SYS_P39
SALES SYS_P40
SALES SYS_P41
SALES SYS_P42
SALES SYS_P43

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
SALES SYS_P44
SALES SYS_P45
SALES SYS_P46
SYS_FBA_HIST_16585 HIGH_PART

37 rows selected.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from sales partition (sys_p44) ;

SALES_ID SALES_DT
---------- -------------------
24 2009-01-25 00:00:00

SQL> select * from sales partition (sys_p33);

SALES_ID SALES_DT
---------- -------------------
13 2009-01-14 00:00:00

SQL> select * from sales partition (sys_p46);

SALES_ID SALES_DT
---------- -------------------
26 2009-01-27 00:00:00


这一特性极大简化了分区表的维护工作。

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

请登录后发表评论 登录
全部评论

注册时间:2012-05-16

  • 博文量
    39
  • 访问量
    510646