分区表 之 Interval分区 和 虚拟列 按星期分区表

今天朋友问我,说想建一个按星期分区的表，就是按星期几分区，没用过，就搜了下，可以用Oracle11g中虚拟列来实现。在11g之前分区表的partitionkey必须是物理存在的。11g开始提供了虚拟列，并且可以作为partitionkey。

关于分区表，之前也做个整理：

Oracle分区表

http://blog.csdn.net/xujinyang/article/details/6832735

Oracle11g新特性简介

http://blog.csdn.net/xujinyang/article/details/6829731

一．按星期分区表示例：

1.创建分区表：

CREATETABLEPT

(

getdatedateNOTNULL,

wdNUMBERGENERATEDALWAYSAS(TO_NUMBER(TO_CHAR(getdate,'D')))VIRTUAL

)

PARTITIONBYLIST(wd)

(PARTITIONSunVALUES(1),

PARTITIONMonVALUES(2),

PARTITIONTueVALUES(3),

PARTITIONWedVALUES(4),

PARTITIONThuVALUES(5),

PARTITIONFriVALUES(6),

PARTITIONSatVALUES(7)

);

2.插入测试数据

SQL>insertintopt(getdate)values(sysdate);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-1);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-2);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-3);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-4);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-5);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-6);

已创建1行。

SQL>insertintopt(getdate)values(sysdate-7);

已创建1行。

SQL>commit;

提交完成。

3.查看数据：

SQL>select*frompt;

GETDATEWD

------------------------

06-6月-101

07-6月-102

08-6月-103

09-6月-104

10-6月-105

03-6月-105

04-6月-106

05-6月-107

已选择8行。

SQL>altersessionsetnls_date_format='YYYY-MM-DD';

会话已更改。

SQL>select*frompt;

GETDATEWD

--------------------

2010-06-061

2010-06-072

2010-06-083

2010-06-094

2010-06-105

2010-06-035

2010-06-046

2010-06-057

已选择8行。

4.查看每个分区里的内容：

SQL>select*fromptpartition(sun);

GETDATEWD

--------------------

2010-06-061

SQL>select*fromptpartition(mon);

GETDATEWD

--------------------

2010-06-072

SQL>select*fromptpartition(tue);

GETDATEWD

--------------------

2010-06-083

SQL>select*fromptpartition(wed);

GETDATEWD

--------------------

2010-06-094

SQL>select*fromptpartition(fri);

GETDATEWD

--------------------

2010-06-046

SQL>select*fromptpartition(sat);

GETDATEWD

--------------------

2010-06-057

SQL>

注：如果是10g，就要添加一物理列。

二．Interval分区

通过以上操作，就完成了一个按星期分区的分区表。在OracleDatabase11g中还可以创建新类型的Interval分区表，Interval类型分区表，可以根据加载数据，自动创建指定间隔的分区。

创建按月分区的分区表：

1.创建分区表

/*Formattedon2010/6/1020:21:12(QP5v5.115.810.9015)*/

CREATETABLEintervalpart(c1NUMBER,c3DATE)

PARTITIONBYRANGE(c3)

INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

(PARTITIONpart1

VALUESLESSTHAN(TO_DATE('01/12/2010','MM/DD/YYYY')),

PARTITIONpart2

VALUESLESSTHAN(TO_DATE('02/12/2010','MM/DD/YYYY'))

)

注意：如果在建Interval分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区

2.查看现在表的分区：

SQL>selecttable_name,partition_namefromuser_tab_partitionswheretable_name='INTERVALPART';

TABLE_NAMEPARTITION_NAME

------------------------------------------------------------

INTERVALPARTPART1

INTERVALPARTPART2

3.插入测试数据：

SQL>begin

2foriin0..11loop

3insertintointervalpartvalues(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i));

4endloop;

5commit;

6end;

7/

PL/SQL过程已成功完成。

补充：add_months()函数获取前一个月或者下一个月的月份，参数中负数代表往前，正数代表往后。

--上一个月

selectto_char(add_months(trunc(sysdate),-1),'yyyymm')fromdual;

--下一个月

selectto_char(add_months(trunc(sysdate),1),'yyyymm')fromdual;

4.观察自动创建的分区：

SQL>selecttable_name,partition_namefromuser_tab_partitionswheretable_name='INTERVALPART';

TABLE_NAMEPARTITION_NAME

------------------------------------------------------------

INTERVALPARTPART1

INTERVALPARTPART2

INTERVALPARTSYS_P22

INTERVALPARTSYS_P23

INTERVALPARTSYS_P24

INTERVALPARTSYS_P25

INTERVALPARTSYS_P26

INTERVALPARTSYS_P27

INTERVALPARTSYS_P28

INTERVALPARTSYS_P29

INTERVALPARTSYS_P30

INTERVALPARTSYS_P31

已选择12行。

5.查看分区内容：

SQL>select*fromINTERVALPART;

C1C3

--------------------

12010-01-01

02010-01-01

12010-02-01

22010-03-01

32010-04-01

42010-05-01

52010-06-01

62010-07-01

72010-08-01

82010-09-01

92010-10-01

102010-11-01

112010-12-01

已选择13行。

SQL>select*fromINTERVALPARTpartition(part1);

C1C3

--------------------

12010-01-01

02010-01-01

SQL>select*fromINTERVALPARTpartition(part2);

C1C3

--------------------

12010-02-01

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

1.创建分区表：

SQL>createtabledave
2(
3idnumber,
4dtdate
5)
6partitionbyrange(dt)
7INTERVAL(NUMTODSINTERVAL(1,'day'))
8(
9partitionp100101valueslessthan(to_date('2010-01-01','yyyy-mm-dd'))
10);

2.查看表分区：
SQL>selecttable_name,partition_namefromuser_tab_partitionswheretable_name='DAVE';

TABLE_NAMEPARTITION_NAME

------------------------------------------------------------

DAVEP100101

3.插入测试数据：

SQL>begin

2foriin1..12loop

3insertintodavevalues(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i));

4endloop;

5commit;

6end;

7/

PL/SQL过程已成功完成。

4.观察自动创建的分区：

SQL>selecttable_name,partition_namefromuser_tab_partitionswheretable_name='DAVE';

TABLE_NAMEPARTITION_NAME

------------------------------------------------------------

DAVEP100101

DAVESYS_P32

DAVESYS_P33

DAVESYS_P34

DAVESYS_P35

DAVESYS_P36

DAVESYS_P37

DAVESYS_P38

DAVESYS_P39

DAVESYS_P40

DAVESYS_P41

DAVESYS_P42

DAVESYS_P43

已选择13行。

5.查看分区内容：

SQL>select*fromdavepartition(SYS_P32);

IDDT

--------------------

12010-01-02

SQL>select*fromdavepartition(SYS_P33);

IDDT

--------------------

22010-01-03

SQL>select*fromdavepartition(SYS_P34);

IDDT

--------------------

32010-01-04

SQL>select*fromdave;

IDDT

--------------------

12010-01-02

22010-01-03

32010-01-04

42010-01-05

52010-01-06

62010-01-07

72010-01-08

82010-01-09

92010-01-10

102010-01-11

112010-01-12

122010-01-13

已选择12行。

我们对Interval分区进行一个总结，通过上面的2个例子我们发现一个现象，就是在创建分区的时候，如果选择了Interval分区，那么在该分区表中，没有创建的分区会自动生成。这样有什么好处呢？

我们假设一下，由于数据量的巨大，所以表设计为每天一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的分区，用以存储第二天的数据。如果在Oracle11g，我们就可以用Interval分区很好的实现这个功能，Oracle会自动完成剩下的分区，是不是很方便呢？

------------------------------------------------------------------------------