《Oracle编程艺术》学习笔记(38)-表分区

分区（partitioning）在Oracle 8.0中引入，将一个表或索引物理地分解为多个更小、更可管理的部分。
11g之前有4种对表分区的方法：
1）区间分区：指定数据区间来决定数据存储在哪个分区。
2）散列分区：在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。
3）列表分区：指定一个离散值集，来数据存储在哪个分区。
4）组合分区：区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。
11g之后又增加了2种：
5）间隔分区：和区间分区类似，但是可以在新数据到来时，如果不能放入已有分区，根据规则创建新的分区。
6）引用分区：允许由外键强制的父子关系中的子表继承父表的分区机制。

区间分区
下面的语句创建了包含2个分区的表T。分区区间是按照严格小于某个值(less than)来指定的。
可以看到，每个分区都可以单独指定表空间。

sys@MYTEST2> create tablespace ts1 datafile '%ORACLE_BASE%/oradata/mytest2/ts1.dbf' size 16M; Tablespace created. sys@MYTEST2> create tablespace ts2 datafile '%ORACLE_BASE%/oradata/mytest2/ts2.dbf' size 16M; Tablespace created. sys@MYTEST2> create tablespace ts3 datafile '%ORACLE_BASE%/oradata/mytest2/ts3.dbf' size 16M; Tablespace created. tony@MYTEST2> create table t_range(range_key date, data varchar2(32)) 2 partition by range(range_key) ( 3 partition part_1 values less than (to_date('2011/01/01', 'yyyy/mm/dd')) tablespace ts1, 4 partition part_2 values less than (to_date('2012/01/01', 'yyyy/mm/dd')) tablespace ts2 5 ); Table created. tony@MYTEST2> insert into t_range values(to_date('2010/12/31', 'yyyy/mm/dd'), 'AAA'); 1 row created. tony@MYTEST2> insert into t_range values(to_date('2011/01/01', 'yyyy/mm/dd'), 'BBB'); 1 row created. tony@MYTEST2> select * from t_range partition(part_1); RANGE_KEY DATA ------------------- -------------------------------- 2010/12/31 00:00:00 AAA tony@MYTEST2> select * from t_range partition(part_2); RANGE_KEY DATA ------------------- -------------------------------- 2011/01/01 00:00:00 BBB

如果插入的数据超过了最大范围，会产生1个ORA-14400错误。

tony@MYTEST2> insert into t_range values(to_date('2012/01/01', 'yyyy/mm/dd'), 'CCC'); insert into t_range values(to_date('2012/01/01', 'yyyy/mm/dd'), 'CCC') * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition

可以使用MAXVALUE关键字来指定一个"others"区间。（即使range_key为null,也插入此分区）

tony@MYTEST2> alter table t_range add partition part_3 2 values less than (maxvalue) tablespace ts3; Table altered. tony@MYTEST2> insert into t_range values(to_date('2012/01/01', 'yyyy/mm/dd'), 'CCC'); 1 row created. tony@MYTEST2> insert into t_range values(null, 'DDD'); 1 row created. tony@MYTEST2> select * from t_range partition(part_3); RANGE_KEY DATA ------------------- -------------------------------- 2012/01/01 00:00:00 CCC DDD

散列分区
Oracle会对分区键应用一个散列函数，以此确定数据应当放在N 个分区中的哪一个分区中。
因此不能控制一行最终会放在哪个分区中。
分区数应该是2的幂，这样才能让数据在各个分区均匀分布。

tony@MYTEST2> create table t_hash (hash_key date, data varchar2(32)) 2 partition by hash(hash_key) ( 3 partition part_1 tablespace ts1, 4 partition part_2 tablespace ts2 5 ); Table created. tony@MYTEST2> insert into t_hash values(to_date('2010/12/31', 'yyyy/mm/dd'), 'AAA'); 1 row created. tony@MYTEST2> insert into t_hash values(to_date('2011/01/01', 'yyyy/mm/dd'), 'BBB'); 1 row created. tony@MYTEST2> insert into t_hash values(to_date('2012/01/01', 'yyyy/mm/dd'), 'CCC'); 1 row created. tony@MYTEST2> insert into t_hash values(to_date('2012/12/12', 'yyyy/mm/dd'), 'DDD'); 1 row created. tony@MYTEST2> select * from t_hash partition(part_1); HASH_KEY DATA ------------------- -------------------------------- 2011/01/01 00:00:00 BBB 2012/01/01 00:00:00 CCC tony@MYTEST2> select * from t_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分区，就不能再往这个表增加分区了。

tony@MYTEST2> create table t_list(list_key varchar2(5), data varchar2(32)) 2 partition by list(list_key) ( 3 partition part_1 values ('east', 'south') tablespace ts1, 4 partition part_2 values ('west', 'north') tablespace ts2, 5 partition part_3 values (default) tablespace ts3 6 ); Table created. tony@MYTEST2> insert into t_list values('east', 'AAA'); 1 row created. tony@MYTEST2> insert into t_list values('south', 'BBB'); 1 row created. tony@MYTEST2> insert into t_list values('west', 'CCC'); 1 row created. tony@MYTEST2> insert into t_list values('north', 'DDD'); 1 row created. tony@MYTEST2> insert into t_list values(null, 'EEE'); 1 row created. tony@MYTEST2> select * from t_list partition(part_1); LIST_ DATA ----- -------------------------------- east AAA south BBB tony@MYTEST2> select * from t_list partition(part_2); LIST_ DATA ----- -------------------------------- west CCC north DDD tony@MYTEST2> select * from t_list partition(part_3); LIST_ DATA ----- -------------------------------- EEE

间隔分区
要使用间隔分区，首先从一个没有MAXVALUE分区的区间分区表开始，指定一个间隔。
分区列必须能够增加NUMBER或者INTERVAL类型的值。
不过使用INTERVAL时需要注意月末日期的影响，实际上如果使用大于28号的日期来指定分区上界，就会得到一个错误：
ORA-14767: Cannot specify this interval with existing high bounds
（INTERVAL和月末日期：http://blog.csdn.net/fw0124/article/details/6918611）

可以使用ALTER将一个现有区间分区表修改为间隔分区表，也可以直接创建1个间隔分区表。

例如，下面创建1个分区表，2011年之前的数据都放入part_1分区中，之后每个月创建1个分区。
store in子句用来指定在那些表空间来创建分区。

tony@ORA11GR2> create table t_interval (range_key date, data varchar2(32)) 2 partition by range (range_key) 3 interval (numtoyminterval(1, 'month')) 4 store in (ts1, ts2, ts3) ( 5 partition part_1 values less than (to_date('2011/01/01', 'yyyy/mm/dd')) tablespace ts1 6 ); Table created. tony@ORA11GR2> select a.partition_name, a.tablespace_name, a.high_value, 2 decode( a.interval, 'YES', b.interval ) interval 3 from user_tab_partitions a, user_part_tables b 4 where a.table_name = 'T_INTERVAL' 5 and a.table_name = b.table_name 6 order by a.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之后的数据，会自动创建新的分区：

tony@ORA11GR2> insert into t_interval values(to_date('2011/04/01', 'yyyy/mm/dd'), 'AAA'); 1 row created. tony@ORA11GR2> insert into t_interval values(to_date('2011/05/04', 'yyyy/mm/dd'), 'BBB'); 1 row created. tony@ORA11GR2> select a.partition_name, a.tablespace_name, a.high_value, 2 decode( a.interval, 'YES', b.interval ) interval 3 from user_tab_partitions a, user_part_tables b 4 where a.table_name = 'T_INTERVAL' 5 and a.table_name = b.table_name 6 order by a.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命名的，可以使用下面的过程来改为有意义的分区名字。

declare l_str varchar2(4000); begin for x in ( select a.partition_name, a.tablespace_name, a.high_value from user_tab_partitions a where a.table_name = 'T_INTERVAL' and a.interval = 'YES' and a.partition_name like 'SYS\_P%' escape '\' ) loop execute immediate 'select to_char( ' || x.high_value || '-numtodsinterval(1,''second''), ''"PART_"yyyy_mm'' ) from dual' into l_str; execute immediate 'alter table T_INTERVAL rename partition "' || x.partition_name || '" to "' || l_str || '"'; end loop; end; / tony@ORA11GR2> select partition_name from user_tab_partitions 2 where table_name='T_INTERVAL'; PARTITION_NAME -------------------- PART_1 PART_2011_04 PART_2011_05

引用分区
使用引用分区，子表会继承父表的分区机制。
先创建父表，创建子表的时候使用partition by reference子句，指定一个外键约束，子表就会使用父表的分区机制。
对父表分区结构进行的任何修改都会传递到子表。
例如，使用上面创建的T_RANGE作为父表，创建子表。

tony@ORA11GR2> truncate table t_range; Table truncated. tony@ORA11GR2> alter table t_range add (id int primary key); Table altered. tony@ORA11GR2> create table t_range_sub(pid int not null, ext_data varchar2(32), 2 constraint t_range_sub_fk foreign key (pid) references t_range (id)) 3 partition by reference (t_range_sub_fk); Table created. tony@ORA11GR2> select table_name, partition_name from user_tab_partitions 2 where table_name in ('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> alter table t_range drop partition part_3; Table altered. tony@ORA11GR2> select table_name, partition_name from user_tab_partitions 2 where table_name in ('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> alter table t_range add partition part_3 values less than (maxvalue); Table altered. tony@ORA11GR2> select table_name, partition_name from user_tab_partitions 2 where table_name in ('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）中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区。
使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有段（这就类似于分区表本身没有段）。数据物理的存储在子分区段上，分区成为一个逻辑容器。
可以在分区上指定表空间，也可以在子分区上指定表空间。
每个区间分区不需要有相同数目的子分区。

tony@MYTEST2> create table t_range_list(range_key date, list_key int, data varchar2(32)) 2 partition by range (range_key) 3 subpartition by list (list_key) ( 4 partition part_1 values less than (to_date('2011/01/01', 'yyyy/mm/dd')) ( 5 subpartition part_1_sub_1 values (1,3,5,7) tablespace ts1, 6 subpartition part_1_sub_2 values (2,4,6,8) tablespace ts2 7 ), 8 partition part_2 values less than (to_date('2012/01/01', 'yyyy/mm/dd')) ( 9 subpartition part_2_sub_1 values (1,3) tablespace ts1, 10 subpartition part_2_sub_2 values (5,7) tablespace ts2, 11 subpartition part_2_sub_3 values (2,4,6,8) tablespace ts3 12 ) 13 ); tony@MYTEST2> insert into t_range_list values(to_date('2010/12/31','yyyy/mm/dd'), 1, 'AAA'); 1 row created. tony@MYTEST2> insert into t_range_list values(to_date('2010/12/31','yyyy/mm/dd'), 2, 'BBB'); 1 row created. tony@MYTEST2> select * from t_range_list partition(part_1); RANGE_KEY LIST_KEY DATA --------- ---------- -------------------------------- 31-DEC-10 1 AAA 31-DEC-10 2 BBB tony@MYTEST2> select * from t_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子句，其中指定的离散值用于第一个分区，其余的离散值用于第二个分区。

tony@MYTEST2> select partition_name,tablespace_name,high_value 2 from user_tab_partitions where table_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> alter table t_range merge partitions part_1, part_2 2 into partition part_2 tablespace ts2; Table altered. tony@MYTEST2> select partition_name,tablespace_name,high_value 2 from user_tab_partitions where table_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> alter table t_range split partition part_2 2 at (to_date('2011/01/01','yyyy.mm/dd')) 3 into (partition part_1 tablespace ts1, partition part_2 tablespace ts2); Table altered. tony@MYTEST2> select partition_name,tablespace_name,high_value 2 from user_tab_partitions where table_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> select partition_name,tablespace_name,high_value 2 from user_tab_partitions where table_name='T_LIST'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- ---------------------------------------- PART_1 TS1 'east', 'south' PART_2 TS2 'west', 'north' PART_3 TS3 default tony@MYTEST2> alter table t_list merge partitions part_1, part_2 into partition part_2; Table altered. tony@MYTEST2> select partition_name,tablespace_name,high_value 2 from user_tab_partitions where table_name='T_LIST'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- -------------------------------------------------- PART_2 USERS 'east', 'south', 'west', 'north' PART_3 TS3 default tony@MYTEST2> alter table t_list split partition part_2 2 values ('east','south') 3 into (partition part_1 tablespace ts1, partition part_2 tablespace ts2); Table altered.

tony@MYTEST2> select partition_name,tablespace_name,high_value 2 from user_tab_partitions where table_name='T_LIST'; PARTITION_ TABLESPACE HIGH_VALUE ---------- ---------- ---------------------------------------- PART_1 TS1 'east', 'south' PART_2 TS2 'west', 'north' PART_3 TS3 default