ORACLE間隔分區(qū)

1、Oracle分區(qū)詳解oracle11gIntervalPartitioning2012-02-1714:33:03|分類：數(shù)據(jù)庫相關(guān)|標簽：|字號大中小訂閱oracle11gIntervalPartitioning摘錄部分我的筆記的中doc,和大家一起感受Oracle11g在分區(qū)方面的增強Oracle11g中IntervalPartitioning分區(qū)實際上是由range分區(qū)引申而來，最終實現(xiàn)了range分區(qū)的自動化。間隔分區(qū)的特點由range分區(qū)派生而來以定長寬度創(chuàng)建分區(qū)（比如年、月、具體的數(shù)字（比如100、500等）分區(qū)字段必須是number或date類型必須至少指定一個range分區(qū)（永

2、久分區(qū)）當有記錄插入時，系統(tǒng)根據(jù)需要自動創(chuàng)建新的分區(qū)和本地索引已有的范圍分區(qū)可被轉(zhuǎn)換成間隔分區(qū)（通過ALTERTABLESETINTERVAL選項完成）IntervalPartitioning不支持支持索引組織表在IntervalPartitioning表上不能倉建domainindex（補充說明：域索引域索引是程序?qū)Ｓ茫═EXT,SPATIAL）索弓|,作為一個索引類型通過例程管理和訪問，之所以叫做域索引是因為它的索引數(shù)據(jù)在程序?qū)Ｓ脜^(qū)域。只有單行索引被域索引支持可以創(chuàng)建單行域索引在純量，對象，或者LOB數(shù)據(jù)類型字段上全文索引、空間索引就是域索引的一種。簡單的說，是Oracle支持的一種可以有

3、用戶自己定制規(guī)則的索引.其中全文索引和空間索引等是Oracle已經(jīng)定義好的域索引)說明1)對于采用date類型的IntervalPartitioning可以支持按year、month、day、hour、minute>second六種類型的定長寬度分區(qū)，分別通過如下函數(shù)轉(zhuǎn)換numtoyminterval(n,'YEARTMONTH')numtodsinterval(n,'DAY'|'HOUR'|'MINUTE'|'SECOND')具體的用法可以參見下面的例子。2)對于采用number類型的IntervalPar

4、titioning必須按照固定的寬度分區(qū)下面用幾個具體的例子來感受一下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類型按定長間隔分區(qū)假設我們的系統(tǒng)中有一張存放員工薪水的表，由于公司員工很多，為了便于管理，我們按照員工的薪水將這張表分區(qū)，每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分區(qū)，考慮將來隨著物價的上漲，員工的薪水可能會超過10K,至U時彳宛通過split動作來新擴展分區(qū).那么到了11g中，我們就不需要這么麻煩了，完全可以提交給Oracle,利用IntervalPartitioning技術(shù)，讓系統(tǒng)自動完成。對DBA來說，只需要創(chuàng)建一個基本salary薪水的分區(qū)即可。以后誰著公司的薪酬體系的變化，系統(tǒng)會自動擴展需要的分區(qū)。比如：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