ORACLE间隔分区

1、Oracle分区详解oracle11gIntervalPartitioning2012-02-1714:33:03|分类：数据库相关|标签：|字号大中小订阅oracle11gIntervalPartitioning摘录部分我的笔记的中doc,和大家一起感受Oracle11g在分区方面的增强Oracle11g中IntervalPartitioning分区实际上是由range分区引申而来，最终实现了range分区的自动化。间隔分区的特点由range分区派生而来以定长宽度创建分区（比如年、月、具体的数字（比如100、500等）分区字段必须是number或date类型必须至少指定一个range分区（永

2、久分区）当有记录插入时，系统根据需要自动创建新的分区和本地索引已有的范围分区可被转换成间隔分区（通过ALTERTABLESETINTERVAL选项完成）IntervalPartitioning不支持支持索引组织表在IntervalPartitioning表上不能仓建domainindex（补充说明：域索引域索引是程序专用（TEXT,SPATIAL）索弓|,作为一个索引类型通过例程管理和访问，之所以叫做域索引是因为它的索引数据在程序专用区域。只有单行索引被域索引支持可以创建单行域索引在纯量，对象，或者LOB数据类型字段上全文索引、空间索引就是域索引的一种。简单的说，是Oracle支持的一种可以有

3、用户自己定制规则的索引.其中全文索引和空间索引等是Oracle已经定义好的域索引)说明1)对于采用date类型的IntervalPartitioning可以支持按year、month、day、hour、minute>second六种类型的定长宽度分区，分别通过如下函数转换numtoyminterval(n,'YEARTMONTH')numtodsinterval(n,'DAY'|'HOUR'|'MINUTE'|'SECOND')具体的用法可以参见下面的例子。2)对于采用number类型的IntervalPar

4、titioning必须按照固定的宽度分区下面用几个具体的例子来感受一下IntervalPartitioning示例一:DATE类型按YEARIntervalPartitioningConnectedtoOracleDatabase11gEnterpriseEditionRelease11.1.0.6.0ConnectedasstudySQL>createtableinterval_test_by_year2(regionnumber(4),3recdatedatenotnull,4 recoidnumber5 )6 partitionbyrange(recdate)7interval(n

5、umtoyminterval(1,'year')8 (9 partitionp1valueslessthan(to_date('20050101','yyyymmdd')10 );TablecreatedSQL>selecttable_name,partition_name,high_value2 fromuser_tab_partitions3 wheretable_name='INTERVAL_TEST_BY_YEAR'4 orderbypartition_position;TABLE_NAMEPARTITION_NAM

6、EHIGH_VALUEINTERVAL_TEST_BY_YEARP1TO_DATE('2005-01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAuser_part_tables2whereSQL>selecttable_name,partitioning_type,status,intervalfromtable_name='INTERVAL_TEST_BY_YEAR'TABLE_NAMEPARTITIONING_TYPESTATUSINTERVALINTER

7、VAL_TEST_BY_YEARRANGEVALIDNUMTOYMINTERVAL(1,'YEARSQL>select*frominterval_test_by_year;REGIONRECDATERECOID-SQL>insertintointerval_test_by_yearvalues(531,to_date('20040101','yyyymmdd'),1);1rowinsertedSQL>insertintointerval_test_by_yearvalues(531,to_date('20050101',

8、'yyyymmdd'),2);1rowinsertedSQL>insertintointerval_test_by_yearvalues(531,to_date('20060101','yyyymmdd'),3);1rowinsertedSQL>commit;CommitcompleteSQL>selecttable_name,partition_name,high_value2fromuser_tab_partitions3wheretable_name='INTERVAL_TEST_BY_YEAR'4orde

9、rbypartition_position;TABLE_NAMEPARTITION_NAMEHIGH_VALUEINTERVAL_TEST_BY_YEARP1TO_DATE('2005-01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_YEARSYS_P61TO_DATE('2006-01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINT

10、ERVAL_TEST_BY_YEARSYS_P62TO_DATE('2007-01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIASQL>select*frominterval_test_by_year;REGIONRECDATERECOID5312004-1-115312005-1-125312006-1-13SQL>select*frominterval_test_by_yearpartition(p1);REGIONRECDATERECOID5312004-1-11S

11、QL>select*frominterval_test_by_yearpartition(sys_p61);REGIONRECDATERECOID5312005-1-12SQL>select*frominterval_test_by_yearpartition(sys_p62);REGIONRECDATERECOID5312006-1-13SQL>示例二：DATE类型按monhIntervalPartitioningConnectedtoOracleDatabase11gEnterpriseEditionRelease11.1.0.6.0ConnectedasstudySQL

12、>createtableinterval_test_by_month2 (regionnumber(4),3 recdatedatenotnull,4 recoidnumber6partitionbyrange(recdate)7interval(numtoyminterval(1,'month')8 (9 partitionp1valueslessthan(to_date('20071201','yyyymmdd')10 );TablecreatedSQL>selecttable_name,partition_name,high_v

13、alue2 fromuser_tab_partitions3 wheretable_name='INTERVAL_TEST_BY_MONTH'4 orderbypartition_position;TABLE_NAMEPARTITION_NAMEHIGH_VALUEINTERVAL_TEST_BY_MONTHP1TO_DATE('2007-12-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIASQL>selecttable_name,partitioning_typ

14、e,status,intervalfromuser_part_tables2wheretable_name='INTERVAL_TEST_BY_MONTH'TABLE_NAMEPARTITIONING_TYPESTATUSINTERVALINTERVAL_TEST_BY_MONTHRANGEVALIDNUMTOYMINTERVAL(1,'MONTH3QL>insertintointerval_test_by_monthvalues(531,to_date('20071212','yyyymmdd'),1);1rowinsertedS

15、QL>insertintointerval_test_by_monthvalues(531,to_date('20080110','yyyymmdd'),2);1rowinsertedSQL>insertintointerval_test_by_monthvalues(531,to_date('20090101','yyyymmdd'),3);1rowinsertedSQL>commit;CommitcompleteSQL>selecttable_name,partition_name,high_value

16、2fromuser_tab_partitions3wheretable_name='INTERVAL_TEST_BY_MONTH'4orderbypartition_position;TABLE_NAMEPARTITION_NAMEHIGH_VALUEINTERVAL_TEST_BY_MONTHP1TO_DATE('2007-12-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_MONTHSYS_P63TO_DATE('2008-

17、01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_MONTHSYS_P64TO_DATE('2008-02-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_MONTHSYS_P65TO_DATE('2009-02-0100:00:00','SYYYY-MM-DDHH24:MI:SS',&

18、#39;NLS_CALENDAR=GREGORIASQL>select*frominterval_test_by_monthpartition(p1);REGIONRECDATERECOIDSQL>select*frominterval_test_by_monthpartition(sys_p63);REGIONRECDATERECOID5312007-12-121SQL>select*frominterval_test_by_monthpartition(sys_p64);REGIONRECDATERECOID5312008-1-102SQL>select*fromi

19、nterval_test_by_monthpartition(sys_p65);REGIONRECDATERECOID5312009-1-13SQL>示例三:DATE类型按dayIntervalPartitioningConnectedtoOracleDatabase11gEnterpriseEditionRelease11.1.0.6.0ConnectedasstudySQL>createtableinterval_test_by_day2(regionnumber(4),3recdatedatenotnull,4recoidnumber5)6partitionbyrange(r

20、ecdate)7interval(numtodsinterval(1,'day')8(9partitionp1valueslessthan(to_date('20071201','yyyymmdd')10);TablecreatedSQL>selecttable_name,partition_name,high_value2fromuser_tab_partitions3wheretable_name='INTERVAL_TEST_BY_DAY'4orderbypartition_position;TABLE_NAMEPAR

21、TITION_NAMEHIGH_VALUEINTERVAL_TEST_BY_DAYP1TO_DATE('2007-12-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIASQL>selecttable_name,partitioning_type,status,intervalfromuser_part_tables2wheretable_name='INTERVAL_TEST_BY_DAY'TABLE_NAMEPARTITIONING_TYPESTATUSINTER

22、VALINTERVAL_TEST_BY_DAYRANGEVALIDNUMTODSINTERVAL(1,'DAY'SQL>insertintointerval_test_by_dayvalues(531,to_date('20071202','yyyymmdd'),1);1rowinsertedSQL>insertintointerval_test_by_dayvalues(531,to_date('20071203','yyyymmdd'),2);1rowinsertedSQL>insertint

23、ointerval_test_by_dayvalues(531,to_date('20081205','yyyymmdd'),3);1rowinsertedSQL>commit;CommitcompleteSQL>selecttable_name,partition_name,high_value2fromuser_tab_partitions3wheretable_name='INTERVAL_TEST_BY_DAY'4orderbypartition_position;TABLE_NAMEPARTITION_NAMEHIGH_VA

24、LUEINTERVAL_TEST_BY_DAYP1TO_DATE('2007-12-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P66TO_DATE('2007-12-0300:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P67TO_DATE('2007-12-0400:00:00

25、','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P68TO_DATE('2008-12-0600:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIASQL>selecttable_name,partitioning_type,status,intervalfromuser_part_tables2wheretable_name='INTERVAL_TEST_B

26、Y_DAY'TABLE_NAMEPARTITIONING_TYPESTATUSINTERVALINTERVAL_TEST_BY_DAYRANGEVALIDNUMTODSINTERVAL(1,'DAY')SQL>insertintointerval_test_by_dayvalues(531,to_date('20081204','yyyymmdd'),4);1rowinsertedSQL>commit;CommitcompleteSQL>selecttable_name,partition_name,high_value

27、2fromuser_tab_partitions3wheretable_name='INTERVAL_TEST_BY_DAY'4orderbypartition_position;TABLE_NAMEPARTITION_NAMEHIGH_VALUEINTERVAL_TEST_BY_DAYP1TO_DATE('2007-12-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P66TO_DATE('2007-12-030

28、0:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P67TO_DATE('2007-12-0400:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P69TO_DATE('2008-12-0500:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CA

29、LENDAR=GREGORIAINTERVAL_TEST_BY_DAYSYS_P68TO_DATE('2008-12-0600:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIASQL>说明：按照hour,minute,second的和上面的做法类似，本案例不再示例中不再介绍.示例四：NUMBER类型按定长间隔分区假设我们的系统中有一张存放员工薪水的表，由于公司员工很多，为了便于管理，我们按照员工的薪水将这张表分区，每2000一个档次，以前的做法是：createtableinterval_te

30、st_by_number(empnonumber(6),namevarchar2(20),salnumber(6)partitionbyrange(salary)(partitionp_2kvalueslessthan(2001),partitionp_4kvalueslessthan(4001),partitionp_6kvalueslessthan(6001),partitionp_8kvalueslessthan(8001)partitionp_10kvalueslessthan(10001)partitionp_maxvalueslessthan(maxvalue);设置5个档次，分别

31、从2K到10K,预留一个maxvalue分区，考虑将来随着物价的上涨，员工的薪水可能会超过10K,至U时彳宛通过split动作来新扩展分区.那么到了11g中，我们就不需要这么麻烦了，完全可以提交给Oracle,利用IntervalPartitioning技术，让系统自动完成。对DBA来说，只需要创建一个基本salary薪水的分区即可。以后谁着公司的薪酬体系的变化，系统会自动扩展需要的分区。比如：ConnectedtoOracleDatabase11gEnterpriseEditionRelease11.1.0.6.0ConnectedasstudySQL>createtableinterval_test_by_number2(empnonumber(6),3 namevarchar2(20),4 salnumber(6)5 )6 partitionbyrange(sal)7 Interval(2000)8 (9 partitionp_2kvalueslessthan(2001)10 );TablecreatedSQL>selec