Oracle 11g 新特性：自动创建分区(Interval Partition)

发布时间：2020-12-12 15:39:20 所属栏目：百科来源：网络整理

导读：分区(Partition)一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。在10g的Oracle版本中，要对分区表做调整，尤其是对RANGE分区添加新的分区都需要DB

分区(Partition)一直是Oracle数据库引以为傲的一项技术，正是分区的存在让Oracle高效的处理海量数据成为可能，在Oracle 11g中，分区技术在易用性和可扩展性上再次得到了增强。在10g的Oracle版本中，要对分区表做调整，尤其是对RANGE分区添加新的分区都需要DBA手动定期添加，或都使用存储过程进行管理。在11G的版本中的Interval Partition不再需要DBA去干预新分区的添加，Oracle会自动去执行这样的操作，减少了DBA的工作量。Interval Partition是Range分区的一个扩展。

使用Interval Partition也有一些限制：

You can only specify one partitioning key column,and it must be ofNUMBERorDATEtype.
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval-partitioned table.

Interval Partition也可以创建复合分区：

Interval-range
Interval-hash
Interval-list

创建Interval分区表：

sys@ORCL>CREATETABLEinterval_sales
2(prod_idNUMBER(6)
3,cust_idNUMBER
4,time_idDATE
5,channel_idCHAR(1)
6,promo_idNUMBER(6)
7,quantity_soldNUMBER(3)
8,amount_soldNUMBER(10,2)
9)
10PARTITIONBYRANGE(time_id)
11INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
12(PARTITIONp0VALUESLESSTHAN(TO_DATE('1-1-2008','DD-MM-YYYY')),13PARTITIONp1VALUESLESSTHAN(TO_DATE('1-1-2009',14PARTITIONp2VALUESLESSTHAN(TO_DATE('1-7-2009',15PARTITIONp3VALUESLESSTHAN(TO_DATE('1-1-2010','DD-MM-YYYY')));

Tablecreated.

插入在指定分区范围内的测试数据，数据插入成功

sys@ORCL>insertintointerval_salesvalues(1,101,to_date('2008-06-01','yyyy-mm-dd'),'a',201,10);

1rowcreated.

sys@ORCL>commit;

Commitcomplete.

sys@ORCL>select*frominterval_salespartition(p1);

PROD_IDCUST_IDTIME_IDCHAPROMO_IDQUANTITY_SOLDAMOUNT_SOLD
----------------------------------------------------------------------------
11012008-06-0100:00:00a20110110

插入不在指定分区范围内的测试数据，数据插入成功

sys@ORCL>insertintointerval_salesvalues(2,to_date('2010-01-03',10);

1rowcreated.

sys@ORCL>commit;

Commitcomplete.

sys@ORCL>select*frominterval_sales;

PROD_IDCUST_IDTIME_IDCHAPROMO_IDQUANTITY_SOLDAMOUNT_SOLD
----------------------------------------------------------------------------
11012008-06-0100:00:00a20110110
21012010-01-0300:00:00a20110110

查看现在表的所有分区

sys@ORCL>coltable_ownerfora10
sys@ORCL>coltable_namefora15
sys@ORCL>colpartition_namefora20
sys@ORCL>colhigh_valuefora100
sys@ORCL>setlinesize300
sys@ORCL>selecttable_owner,table_name,partition_name,high_valuefromdba_tab_partitionswheretable_name='INTERVAL_SALES';

TABLE_OWNETABLE_NAMEPARTITION_NAMEHIGH_VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------
SYSINTERVAL_SALESP0TO_DATE('2008-01-0100:00:00','SYYYY-MM-DDHH24:MI:SS','NLS_CALENDAR=GREGORIAN')
SYSINTERVAL_SALESP1TO_DATE('2009-01-0100:00:00','NLS_CALENDAR=GREGORIAN')
SYSINTERVAL_SALESP2TO_DATE('2009-07-0100:00:00','NLS_CALENDAR=GREGORIAN')
SYSINTERVAL_SALESP3TO_DATE('2010-01-0100:00:00','NLS_CALENDAR=GREGORIAN')
SYSINTERVAL_SALESSYS_P41TO_DATE('2010-02-0100:00:00','NLS_CALENDAR=GREGORIAN')

可以看到INTERVAL_sales表多了一个SYS_P41分区，分区的HIGH_VALUE为2010-02-01，分区是增加了一个月

再插入间隔再大一些的测试数据看看变化

sys@ORCL>insertintointerval_salesvalues(2,to_date('2010-08-03',10);

1rowcreated.

sys@ORCL>commit;

Commitcomplete.

sys@ORCL>select*frominterval_sales;

PROD_IDCUST_IDTIME_IDCHAPROMO_IDQUANTITY_SOLDAMOUNT_SOLD
----------------------------------------------------------------------------
11012008-06-0100:00:00a20110110
21012010-01-0300:00:00a20110110
21012010-08-0300:00:00a20110110

sys@ORCL>selecttable_owner,'NLS_CALENDAR=GREGORIAN')
SYSINTERVAL_SALESSYS_P42TO_DATE('2010-09-0100:00:00','NLS_CALENDAR=GREGORIAN')

6rowsselected.

sys@ORCL>insertintointerval_salesvalues(2,to_date('2010-05-03',10);

1rowcreated.

sys@ORCL>commit;

Commitcomplete.

sys@ORCL>select*frominterval_sales;

PROD_IDCUST_IDTIME_IDCHAPROMO_IDQUANTITY_SOLDAMOUNT_SOLD
----------------------------------------------------------------------------
11012008-06-0100:00:00a20110110
21012010-01-0300:00:00a20110110
21012010-05-0300:00:00a20110110
21012010-08-0300:00:00a20110110

sys@ORCL>selecttable_owner,'NLS_CALENDAR=GREGORIAN')
SYSINTERVAL_SALESSYS_P43TO_DATE('2010-06-0100:00:00','NLS_CALENDAR=GREGORIAN')

7rowsselected.

可以看出插入2010-08-03的数据会创建一个2010-09-01的分区，然后再插入2010-05-03的数据会创建2010-06-01的分区，说明所有自动创建的分区都会按整个月来控制。控制这个时间间隔的就是NUMTOYMINTERVAL(1,'MONTH')。

NUMTOYMINTERVAL(1,'YEAR') 一年

NUMTOYMINTERVAL(1,'MONTH') 一个月

NUMTODSINTERVAL(1,'DAY') 一天

NUMTODSINTERVAL(1,'HOUR') 一小时

NUMTODSINTERVAL(1,'MINUTE') 一分钟

NUMTODSINTERVAL(1,'SECOND') 一秒

使用数值做分区键也可以使用Interval Partition

sys@ORCL>createtableinterval_num
2(idnumber,3namevarchar2(20),4time_iddate
5)
6partitionbyrange(id)
7interval(20)
8(partitionp0valueslessthan(20));

Tablecreated.

sys@ORCL>insertintointerval_numvalues(1,to_date('2016-01-01','yyyy-mm-dd'));

1rowcreated.

sys@ORCL>insertintointerval_numvalues(21,'yyyy-mm-dd'));

1rowcreated.

sys@ORCL>commit;

Commitcomplete.

sys@ORCL>selecttable_owner,high_valuefromdba_tab_partitionswheretable_name='INTERVAL_NUM';

TABLE_OWNETABLE_NAMEPARTITION_NAMEHIGH_VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------
SYSINTERVAL_NUMP020
SYSINTERVAL_NUMSYS_P4440

使用Interval Partition的注意事项，自动创建的分区名都是自动分配的，类似SYS_P**的，如果觉得这种名字不合规的话可以进行修改

sys@ORCL>altertableinterval_numrenamepartitionsys_p44top1;

Tablealtered.

sys@ORCL>selecttable_owner,high_valuefromdba_tab_partitionswheretable_name='INTERVAL_NUM';

TABLE_OWNETABLE_NAMEPARTITION_NAMEHIGH_VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------
SYSINTERVAL_NUMP020
SYSINTERVAL_NUMP140

非Interval Partition转为Partition，使用alter table table_name set interval(...);

sys@ORCL>createtableinterval_num
		2		(idnumber,4time_iddate
		5)
		6partitionbyrange(id)
		7(partitionp0valueslessthan(20));

Tablecreated.

sys@ORCL>insertintointerval_num(id)values(1);

1rowcreated.

sys@ORCL>insertintointerval_num(id)values(21);
insertintointerval_num(id)values(21)
*
ERRORatline1:
ORA-14400:insertedpartitionkeydoesnotmaptoanypartition


sys@ORCL>altertableinterval_numsetinterval(20);

Tablealtered.

sys@ORCL>insertintointerval_num(id)values(21);

1rowcreated.

sys@ORCL>selecttable_owner,high_valuefromdba_tab_partitionswheretable_name='INTERVAL_NUM';

TABLE_OWNER	TABLE_NAME	PARTITION_HIGH_VALUE
------------------------------------------------------------------------------------------------------------------------
SYS		INTERVAL_NUM	P0	20
SYS		INTERVAL_NUM	SYS_P4540

官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#BAJHFFBE

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!