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

最新推荐文章于 2022-08-25 11:15:25 发布

cuihan9123

最新推荐文章于 2022-08-25 11:15:25 发布

阅读量94

点赞数

分区（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.

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';

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