分区(partitioning)在Oracle 8.0中引入，将一个表或索引物理地分解为多个更小、更可管理的部分。
11g之前有4种对表分区的方法：
1)区间分区：指定数据区间来决定数据存储在哪个分区。
2)散列分区：在一个列(或多个列)上应用一个散列函数，行会按这个散列值放在某个分区中。
3)列表分区：指定一个离散值集，来数据存储在哪个分区。
4)组合分区：区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。
11g之后又增加了2种：
5)间隔分区：和区间分区类似，但是可以在新数据到来时，如果不能放入已有分区，根据规则创建新的分区。
6)引用分区：允许由外键强制的父子关系中的子表继承父表的分区机制。
区间分区下面的语句创建了包含2个分区的表T。分区区间是按照严格小于某个值(less than)来指定的。
可以看到，每个分区都可以单独指定表空间。
[sql]
sys@MYTEST2>createtablespace ts1 datafile'%ORACLE_BASE%/oradata/mytest2/ts1.dbf'size16M;
Tablespace created.
sys@MYTEST2>createtablespace ts2 datafile'%ORACLE_BASE%/oradata/mytest2/ts2.dbf'size16M;
Tablespace created.
sys@MYTEST2>createtablespace ts3 datafile'%ORACLE_BASE%/oradata/mytest2/ts3.dbf'size16M;
Tablespace created.
tony@MYTEST2>createtablet_range(range_keydate, data varchar2(32))
2 partitionbyrange(range_key) (
3 partition part_1valuesless than (to_date('2011/01/01','yyyy/mm/dd')) tablespace ts1,
4 partition part_2valuesless than (to_date('2012/01/01','yyyy/mm/dd')) tablespace ts2
5 );
Tablecreated.
tony@MYTEST2>insertintot_rangevalues(to_date('2010/12/31','yyyy/mm/dd'),'AAA');
1 row created.
tony@MYTEST2>insertintot_rangevalues(to_date('2011/01/01','yyyy/mm/dd'),'BBB');
1 row created.
tony@MYTEST2>select*fromt_range partition(part_1);
RANGE_KEY DATA
------------------- --------------------------------
2010/12/31 00:00:00 AAA
tony@MYTEST2>select*fromt_range partition(part_2);
RANGE_KEY DATA
------------------- --------------------------------
2011/01/01 00:00:00 BBB
如果插入的数据超过了最大范围，会产生1个ORA-14400错误。
[sql]
tony@MYTEST2>insertintot_rangevalues(to_date('2012/01/01','yyyy/mm/dd'),'CCC');
insertintot_rangevalues(to_date('2012/01/01','yyyy/mm/dd'),'CCC')
*
ERRORatline 1:
ORA-14400: inserted partitionkeydoesnotmaptoanypartition
可以使用MAXVALUE关键字来指定一个"others"区间。(即使range_key为null,也插入此分区)
[sql]
tony@MYTEST2>altertablet_rangeaddpartition part_3
2valuesless than (maxvalue) tablespace ts3;
Tablealtered.
tony@MYTEST2>insertintot_rangevalues(to_date('2012/01/01','yyyy/mm/dd'),'CCC');
1 row created.
tony@MYTEST2>insertintot_rangevalues(null,'DDD');
1 row created.
tony@MYTEST2>select*fromt_range partition(part_3);
RANGE_KEY DATA
------------------- --------------------------------
2012/01/01 00:00:00 CCC
DDD
散列分区Oracle会对分区键应用一个散列函数，以此确定数据应当放在N 个分区中的哪一个分区中。
因此不能控制一行最终会放在哪个分区中。
分区数应该是2的幂，这样才能让数据在各个分区均匀分布。
[sql]
tony@MYTEST2>createtablet_hash (hash_keydate, data varchar2(32))
2 partitionbyhash(hash_key) (
3 partition part_1 tablespace ts1,
4 partition part_2 tablespace ts2
5 );
Tablecreated.
tony@MYTEST2>insertintot_hashvalues(to_date('2010/12/31','yyyy/mm/dd'),'AAA');
1 row created.
tony@MYTEST2>insertintot_hashvalues(to_date('2011/01/01','yyyy/mm/dd'),'BBB');
1 row created.
tony@MYTEST2>insertintot_hashvalues(to_date('2012/01/01','yyyy/mm/dd'),'CCC');
1 row created.
tony@MYTEST2>insertintot_hashvalues(to_date('2012/12/12','yyyy/mm/dd'),'DDD');
1 row created.
tony@MYTEST2>select*fromt_hash partition(part_1);
HASH_KEY DATA
------------------- --------------------------------
2011/01/01 00:00:00 BBB
2012/01/01 00:00:00 CCC
tony@MYTEST2>select*fromt_hash partition(part_2);
HASH_KEY DATA
------------------- --------------------------------
2010/12/31 00:00:00 AAA
2012/12/12 00:00:00 DDD
列表分区列表分区提供按照离散值来进行分区的功能。
和区间分区类似，如果插入的分区键不在指定的集合中，会产生错误，可以使用VALUES(DEFAULT)子句来指定一个"others"区间。但是如果已经存在DEFAULT分区，就不能再往这个表增加分区了。
[sql]
tony@MYTEST2>createtablet_list(list_key varchar2(5), data varchar2(32))
2 partitionbylist(list_key) (
3 partition part_1values('east','south') tablespace ts1,
4 partition part_2values('west','north') tablespace ts2,
5 partition part_3values(default) tablespace ts3
6 );
Tablecreated.
tony@MYTEST2>insertintot_listvalues('east','AAA');
1 row created.
tony@MYTEST2>insertintot_listvalues('south','BBB');
1 row created.
tony@MYTEST2>insertintot_listvalues('west','CCC');
1 row created.
tony@MYTEST2>insertintot_listvalues('north','DDD');
1 row created.
tony@MYTEST2>insertintot_listvalues(null,'EEE');
1 row created.
tony@MYTEST2>select*fromt_list partition(part_1);
LIST_ DATA
----- --------------------------------
east AAA
south BBB
tony@MYTEST2>select*fromt_list partition(part_2);
LIST_ DATA
----- --------------------------------
west CCC
north DDD
tony@MYTEST2>select*fromt_list partition(part_3);
LIST_ DATA
----- --------------------------------
EEE
间隔分区要使用间隔分区，首先从一个没有MAXVALUE分区的区间分区表开始，指定一个间隔。
分区列必须能够增加NUMBER或者INTERVAL类型的值。
不过使用INTERVAL时需要注意月末日期的影响，实际上如果使用大于28号的日期来指定分区上界，就会得到一个错误：
ORA-14767: Cannot specify this interval with existing high bounds
(INTERVAL和月末日期：http://www.linuxidc.com/Linux/2012-01/51264p36.htm)
可以使用ALTER将一个现有区间分区表修改为间隔分区表，也可以直接创建1个间隔分区表。
例如，下面创建1个分区表，2011年之前的数据都放入part_1分区中，之后每个月创建1个分区。
store in子句用来指定在那些表空间来创建分区。
[sql]
tony@ORA11GR2>createtablet_interval (range_keydate, data varchar2(32))
2 partitionbyrange (range_key)
3 interval (numtoyminterval(1,'month'))
4 storein(ts1, ts2, ts3) (
5 partition part_1valuesless than (to_date('2011/01/01','yyyy/mm/dd')) tablespace ts1
6 );
Tablecreated.
tony@ORA11GR2>selecta.partition_name, a.tablespace_name, a.high_value,
2 decode( a.interval,'YES', b.interval ) interval
3fromuser_tab_partitions a, user_part_tables b
4wherea.table_name ='T_INTERVAL'
5anda.table_name = b.table_name
6orderbya.partition_position;
PARTITION_ TABLESPACE HIGH_VALUE INTERVAL
---------- ---------- ------------------------------ --------------------
PART_1 TS1 TO_DATE(' 2011-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
现在只有1个分区，存放2011年之前的数据，如果插入2011/01/01之后的数据，会自动创建新的分区：
[sql]
tony@ORA11GR2>insertintot_intervalvalues(to_date('2011/04/01','yyyy/mm/dd'),'AAA');
1 row created.
tony@ORA11GR2>insertintot_intervalvalues(to_date('2011/05/04','yyyy/mm/dd'),'BBB');
1 row created.
tony@ORA11GR2>selecta.partition_name, a.tablespace_name, a.high_value,
2 decode( a.interval,'YES', b.interval ) interval
3fromuser_tab_partitions a, user_part_tables b
4wherea.table_name ='T_INTERVAL'
5anda.table_name = b.table_name
6orderbya.partition_position;
PARTITION_ TABLESPACE HIGH_VALUE INTERVAL
---------- ---------- ------------------------------ --------------------
PART_1 TS1 TO_DATE(' 2011-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIAN')
SYS_P21 TS2 TO_DATE(' 2011-05-01 00:00:00'NUMTOYMINTERVAL(1,'M
,'SYYYY-MM-DD HH24:MI:SS','N ONTH')
LS_CALENDAR=GREGORIAN')
SYS_P22 TS3 TO_DATE(' 2011-06-01 00:00:00'NUMTOYMINTERVAL(1,'M
,'SYYYY-MM-DD HH24:MI:SS','N ONTH')
LS_CALENDAR=GREGORIAN')
如果这个时候rollback，分区仍然会保留。这些分区是使用一个递归事务创建的，和正在执行的事务并不是同一个事务。
另外，新创建的分区名字是oracle命名的，可以使用下面的过程来改为有意义的分区名字。
[sql]
declare
l_str varchar2(4000);
begin
forxin(selecta.partition_name, a.tablespace_name, a.high_value
fromuser_tab_partitions a
wherea.table_name ='T_INTERVAL'
anda.interval ='YES'
anda.partition_namelike'SYS\_P%'escape'\')
loop
executeimmediate
'select to_char( '|| x.high_value ||
'-numtodsinterval(1,''second''), ''"PART_"yyyy_mm'' ) from dual'
intol_str;
executeimmediate
'alter table T_INTERVAL rename partition "'||
x.partition_name ||'" to "'|| l_str ||'"';
endloop;
end;
/
[sql]
tony@ORA11GR2>selectpartition_namefromuser_tab_partitions
2wheretable_name='T_INTERVAL';
PARTITION_NAME
--------------------
PART_1
PART_2011_04
PART_2011_05
引用分区使用引用分区，子表会继承父表的分区机制。
先创建父表，创建子表的时候使用partition by reference子句，指定一个外键约束，子表就会使用父表的分区机制。
对父表分区结构进行的任何修改都会传递到子表。
例如，使用上面创建的T_RANGE作为父表，创建子表。
[sql]
tony@ORA11GR2>truncatetablet_range;
Tabletruncated.
tony@ORA11GR2>altertablet_rangeadd(idintprimarykey);
Tablealtered.
tony@ORA11GR2>createtablet_range_sub(pidintnotnull, ext_data varchar2(32),
2constraintt_range_sub_fkforeignkey(pid)referencest_range (id))
3 partitionbyreference (t_range_sub_fk);
Tablecreated.
tony@ORA11GR2>selecttable_name, partition_namefromuser_tab_partitions
2wheretable_namein('T_RANGE','T_RANGE_SUB');
TABLE_NAME PARTITION_NAME
------------------------------------------------------------ --------------
T_RANGE PART_1
T_RANGE PART_2
T_RANGE PART_3
T_RANGE_SUB PART_1
T_RANGE_SUB PART_2
T_RANGE_SUB PART_3
tony@ORA11GR2>altertablet_rangedroppartition part_3;
Tablealtered.
tony@ORA11GR2>selecttable_name, partition_namefromuser_tab_partitions
2wheretable_namein('T_RANGE','T_RANGE_SUB');
TABLE_NAME PARTITION_NAME
------------------------------------------------------------ --------------------
T_RANGE PART_1
T_RANGE PART_2
T_RANGE_SUB PART_1
T_RANGE_SUB PART_2
tony@ORA11GR2>altertablet_rangeaddpartition part_3valuesless than (maxvalue);
Tablealtered.
tony@ORA11GR2>selecttable_name, partition_namefromuser_tab_partitions
2wheretable_namein('T_RANGE','T_RANGE_SUB');
TABLE_NAME PARTITION_NAME
------------------------------------------------------------ --------------------
T_RANGE PART_1
T_RANGE PART_2
T_RANGE PART_3
T_RANGE_SUB PART_1
T_RANGE_SUB PART_2
T_RANGE_SUB PART_3
使用引用分区有2个需要注意的地方：
1)子表的外键列需要指定为NOT NULL。否则会有1个错误：
ORA-14652: reference partitioning foreign key is not supported
2)父表不能为间隔分区。否则会有1个错误：
ORA-14659: Partitioning method of the parent table is not supported
组合分区组合分区区(composite partitioning)中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区。
使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有段(这就类似于分区表本身没有段)。数据物理的存储在子分区段上，分区成为一个逻辑容器。
可以在分区上指定表空间，也可以在子分区上指定表空间。
每个区间分区不需要有相同数目的子分区。
[sql]
tony@MYTEST2>createtablet_range_list(range_keydate, list_keyint, data varchar2(32))
2 partitionbyrange (range_key)
3 subpartitionbylist (list_key) (
4 partition part_1valuesless than (to_date('2011/01/01','yyyy/mm/dd')) (
5 subpartition part_1_sub_1values(1,3,5,7) tablespace ts1,
6 subpartition part_1_sub_2values(2,4,6,8) tablespace ts2
7 ),
8 partition part_2valuesless than (to_date('2012/01/01','yyyy/mm/dd')) (
9 subpartition part_2_sub_1values(1,3) tablespace ts1,
10 subpartition part_2_sub_2values(5,7) tablespace ts2,
11 subpartition part_2_sub_3values(2,4,6,8) tablespace ts3
12 )
13 );
tony@MYTEST2>insertintot_range_listvalues(to_date('2010/12/31','yyyy/mm/dd'), 1,'AAA');
1 row created.
tony@MYTEST2>insertintot_range_listvalues(to_date('2010/12/31','yyyy/mm/dd'), 2,'BBB');
1 row created.
tony@MYTEST2>select*fromt_range_list partition(part_1);
RANGE_KEY LIST_KEY DATA
--------- ---------- --------------------------------
31-DEC-10 1 AAA
31-DEC-10 2 BBB
tony@MYTEST2>select*fromt_range_list subpartition(part_1_sub_1);
RANGE_KEY LIST_KEY DATA
--------- ---------- --------------------------------
31-DEC-10 1 AAA
行移动如果修改用于确定分区的列，并且修改会导致行跨分区移动，那么只有当表启用了行移动这个修改才能成功。(跨分区移动会导致行的ROWID改变)
使用下面语句启用行移动。
alter table t_range enable row movement;
否则，会得到一个错误，
ORA-14402: updating partition key column would cause a partition change
另外，对于引用分区，父表的更新引发的行移动也会传递到子表。
分区相关的常用操作1) user_tab_partitions: 查看表分区信息
user_tab_subpartitions : 查看表子分区信息
2) user_ind_partitions: 查看索引分区信息
user_ind_subpartitions: 查看索引子分区信息
3) user_part_key_columns: 查看分区键
user_subpart_key_columns: 查看子分区键
4) alter table ... rename partition ... to ... : 重命名分区
alter table ... rename subpartition ... to ... : 重命名分区
5) alter table ... add partition ... : 增加分区
6) alter table ... modify partition ... add subpartition ... : 增加子分区
7) alter table ... drop partition ... : 删除分区
8) alter table ... drop subpartition ... : 删除子分区
9) alter table ... truncate partition ... : 截断分区
10)alter table ... truncate subpartition ... : 截断子分区
11)拆分/合并分区
Hash分区不能被拆分，合并，因为其中的数据是根据Hash运算结果自动分配的。
合并分区：alter table ... merge partitions ... into partition ...
拆分分区：alter table ... split partition ... at/values ... into ...
对于区间分区使用at子句，
对于列表分区使用values子句，其中指定的离散值用于第一个分区，其余的离散值用于第二个分区。
[sql]
tony@MYTEST2>selectpartition_name,tablespace_name,high_value
2fromuser_tab_partitionswheretable_name='T_RANGE';
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- --------------------------------------------------
PART_1 TS1 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2 TS2 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_3 TS3 MAXVALUE
tony@MYTEST2>altertablet_range merge partitions part_1, part_2
2intopartition part_2 tablespace ts2;
Tablealtered.
tony@MYTEST2>selectpartition_name,tablespace_name,high_value
2fromuser_tab_partitionswheretable_name='T_RANGE';
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- --------------------------------------------------
PART_2 TS2 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_3 TS3 MAXVALUE
tony@MYTEST2>altertablet_range split partition part_2
2at(to_date('2011/01/01','yyyy.mm/dd'))
3into(partition part_1 tablespace ts1, partition part_2 tablespace ts2);
Tablealtered.
tony@MYTEST2>selectpartition_name,tablespace_name,high_value
2fromuser_tab_partitionswheretable_name='T_RANGE';
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- --------------------------------------------------
PART_1 TS1 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2 TS2 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_3 TS3 MAXVALUE
tony@MYTEST2>selectpartition_name,tablespace_name,high_value
2fromuser_tab_partitionswheretable_name='T_LIST';
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ----------------------------------------
PART_1 TS1'east','south'
PART_2 TS2'west','north'
PART_3 TS3default
tony@MYTEST2>altertablet_list merge partitions part_1, part_2intopartition part_2;
Tablealtered.
tony@MYTEST2>selectpartition_name,tablespace_name,high_value
2fromuser_tab_partitionswheretable_name='T_LIST';
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- --------------------------------------------------
PART_2 USERS'east','south','west','north'
PART_3 TS3default
tony@MYTEST2>altertablet_list split partition part_2
2values('east','south')
3into(partition part_1 tablespace ts1, partition part_2 tablespace ts2);
Tablealtered.
tony@MYTEST2>selectpartition_name,tablespace_name,high_value
2fromuser_tab_partitionswheretable_name='T_LIST';
PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- ----------------------------------------
PART_1 TS1'east','south'
PART_2 TS2'west','north'
PART_3 TS3default
1351
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
