今天朋友问我,说想建一个按星期分区的表,就是按星期几分区,没用过,就搜了下,可以用Oracle11g中虚拟列来实现。在11g之前分区表的partitionkey必须是物理存在的。11g开始提供了虚拟列,并且可以作为partitionkey。
关于分区表,之前也做个整理:
Oracle分区表
http://blog.csdn.net/tianlesoftware/archive/2009/10/24/4717318.aspx
Oracle11g新特性简介
http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx
一.按星期分区表示例:
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会自动完成剩下的分区,是不是很方便呢?
<!--EndFragment-->
------------------------------------------------------------------------------
Blog:http://blog.csdn.net/tianlesoftware
网上资源:http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1群:62697716(满);DBA2群:62697977