分区(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 of
NUMBERorDATEtype. -
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分区表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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.
|
插入在指定分区范围内的测试数据，数据插入成功
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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
|
插入不在指定分区范围内的测试数据，数据插入成功
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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
|
查看现在表的所有分区
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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分区，分区的HIGH_VALUE为2010-02-01，分区是增加了一个月
再插入间隔再大一些的测试数据看看变化
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
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的数据会创建一个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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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的注意事项，自动创建的分区名都是自动分配的，类似SYS_P**的，如果觉得这种名字不合规的话可以进行修改
|
1
2
3
4
5
6
7
8
9
10
|
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转为Partition，使用alter table table_name set interval(...);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1878094，如需转载请自行联系原作者
