分區(qū)(Partition)一直是Oracle數(shù)據(jù)庫引以為傲的一項技術(shù)，正是分區(qū)的存在讓Oracle高效的處理海量數(shù)據(jù)成為可能，在Oracle 11g中，分區(qū)技術(shù)在易用性和可擴展性上再次得到了增強。在10g的Oracle版本中，要對分區(qū)表做調(diào)整，尤其是對RANGE分區(qū)添加新的分區(qū)都需要DBA手動定期添加，或都使用存儲過程進行管理。在11G的版本中的Interval Partition不再需要DBA去干預新分區(qū)的添加，Oracle會自動去執(zhí)行這樣的操作，減少了DBA的工作量。Interval Partition是Range分區(qū)的一個擴展。
創(chuàng)新互聯(lián)公司主要從事成都網(wǎng)站設計、成都網(wǎng)站制作、網(wǎng)頁設計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務。立足成都服務保德,十余年網(wǎng)站建設經(jīng)驗,價格優(yōu)惠、服務專業(yè),歡迎來電咨詢建站服務:18982081108
使用Interval Partition也有一些限制：
You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval-partitioned table.
Interval Partition也可以創(chuàng)建復合分區(qū)：
Interval-range
Interval-hash
Interval-list
創(chuàng)建Interval分區(qū)表：
重慶分公司
重慶分公司
sys@ORCL>CREATE TABLE interval_sales
2 ( prod_id NUMBER(6)
3 , cust_id NUMBER
4 , time_id DATE
5 , channel_id CHAR(1)
6 , promo_id NUMBER(6)
7 , quantity_sold NUMBER(3)
8 , amount_sold NUMBER(10,2)
9 )
10 PARTITION BY RANGE (time_id)
11 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
12 ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
13 PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
14 PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
15 PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
Table created.插入在指定分區(qū)范圍內(nèi)的測試數(shù)據(jù)，數(shù)據(jù)插入成功
sys@ORCL>insert into interval_sales values(1,101,to_date('2008-06-01','yyyy-mm-dd'),'a',201,101,10);
1 row created.
sys@ORCL>commit;
Commit complete.
sys@ORCL>select * from interval_sales partition(p1);
PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- --- ---------- ------------- -----------
1 101 2008-06-01 00:00:00 a 201 101 10插入不在指定分區(qū)范圍內(nèi)的測試數(shù)據(jù)，數(shù)據(jù)插入成功
sys@ORCL>insert into interval_sales values(2,101,to_date('2010-01-03','yyyy-mm-dd'),'a',201,101,10);
1 row created.
sys@ORCL>commit;
Commit complete.
sys@ORCL>select * from interval_sales;
PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- --- ---------- ------------- -----------
1 101 2008-06-01 00:00:00 a 201 101 10
2 101 2010-01-03 00:00:00 a 201 101 10查看現(xiàn)在表的所有分區(qū)
sys@ORCL>col table_owner for a10
sys@ORCL>col table_name for a15
sys@ORCL>col partition_name for a20
sys@ORCL>col high_value for a100
sys@ORCL>set linesize 300
sys@ORCL>select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_SALES';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------------------------------------------------------------------------------------------------
SYS INTERVAL_SALES P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES SYS_P41 TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')可以看到INTERVAL_sales表多了一個SYS_P41分區(qū)，分區(qū)的HIGH_VALUE為2010-02-01，分區(qū)是增加了一個月
再插入間隔再大一些的測試數(shù)據(jù)看看變化
sys@ORCL>insert into interval_sales values(2,101,to_date('2010-08-03','yyyy-mm-dd'),'a',201,101,10);
1 row created.
sys@ORCL>commit;
Commit complete.
sys@ORCL>select * from interval_sales;
PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- --- ---------- ------------- -----------
1 101 2008-06-01 00:00:00 a 201 101 10
2 101 2010-01-03 00:00:00 a 201 101 10
2 101 2010-08-03 00:00:00 a 201 101 10
sys@ORCL>select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_SALES';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------------------------------------------------------------------------------------------------
SYS INTERVAL_SALES P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES SYS_P41 TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES SYS_P42 TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
6 rows selected.
sys@ORCL>insert into interval_sales values(2,101,to_date('2010-05-03','yyyy-mm-dd'),'a',201,101,10);
1 row created.
sys@ORCL>commit;
Commit complete.
sys@ORCL>select * from interval_sales;
PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- --- ---------- ------------- -----------
1 101 2008-06-01 00:00:00 a 201 101 10
2 101 2010-01-03 00:00:00 a 201 101 10
2 101 2010-05-03 00:00:00 a 201 101 10
2 101 2010-08-03 00:00:00 a 201 101 10
sys@ORCL>select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_SALES';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------------------------------------------------------------------------------------------------
SYS INTERVAL_SALES P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES SYS_P41 TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES SYS_P42 TO_DATE(' 2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS INTERVAL_SALES SYS_P43 TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
7 rows selected.可以看出插入2010-08-03的數(shù)據(jù)會創(chuàng)建一個2010-09-01的分區(qū)，然后再插入2010-05-03的數(shù)據(jù)會創(chuàng)建2010-06-01的分區(qū)，說明所有自動創(chuàng)建的分區(qū)都會按整個月來控制?？刂七@個時間間隔的就是NUMTOYMINTERVAL(1, 'MONTH')。
NUMTOYMINTERVAL(1, 'YEAR') 一年
NUMTOYMINTERVAL(1, 'MONTH') 一個月
NUMTODSINTERVAL(1, 'DAY') 一天
NUMTODSINTERVAL(1, 'HOUR') 一小時
NUMTODSINTERVAL(1, 'MINUTE') 一分鐘
NUMTODSINTERVAL(1, 'SECOND') 一秒
使用數(shù)值做分區(qū)鍵也可以使用Interval Partition
sys@ORCL>create table interval_num
2 (id number,
3 name varchar2(20),
4 time_id date
5 )
6 partition by range (id)
7 interval(20)
8 (partition p0 values less than (20));
Table created.
sys@ORCL>insert into interval_num values(1,'a',to_date('2016-01-01','yyyy-mm-dd'));
1 row created.
sys@ORCL>insert into interval_num values(21,'a',to_date('2016-01-01','yyyy-mm-dd'));
1 row created.
sys@ORCL>commit;
Commit complete.
sys@ORCL>select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_NUM';
TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------------------------------------------------------------------------------------------------
SYS INTERVAL_NUM P0 20
SYS INTERVAL_NUM SYS_P44 40使用Interval Partition的注意事項，自動創(chuàng)建的分區(qū)名都是自動分配的，類似SYS_P**的，如果覺得這種名字不合規(guī)的話可以進行修改
sys@ORCL>alter table interval_num rename partition sys_p44 to p1; Table altered. sys@ORCL>select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_NUM'; TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- --------------- -------------------- ---------------------------------------------------------------------------------------------------- SYS INTERVAL_NUM P0 20 SYS INTERVAL_NUM P1 40
非Interval Partition轉(zhuǎn)為Partition，使用alter table table_name set interval(...);
sys@ORCL> create table interval_num 2 (id number, 3 name varchar2(20), 4 time_id date 5 ) 6 partition by range (id) 7 (partition p0 values less than (20)); Table created. sys@ORCL>insert into interval_num(id) values(1); 1 row created. sys@ORCL>insert into interval_num(id) values(21); insert into interval_num(id) values(21) * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition sys@ORCL>alter table interval_num set interval(20); Table altered. sys@ORCL>insert into interval_num(id) values(21); 1 row created. sys@ORCL>select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_NUM'; TABLE_OWNER TABLE_NAME PARTITION_ HIGH_VALUE --------------- --------------- ---------- -------------------------------------------------------------------------------- SYS INTERVAL_NUM P0 20 SYS INTERVAL_NUM SYS_P45 40
官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#BAJHFFBE
