ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 《Oracle编程艺术》学习笔记(38)-表分区 .

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

原创 Linux操作系统 作者:xiaoweisos 时间:2012-07-27 00:07:19 0 删除 编辑

分区(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';

  1. PARTITION_ TABLESPACE HIGH_VALUE
    ---------- ---------- ----------------------------------------   
  2. PART_1     TS1        'east''south'  
  3. PART_2     TS2        'west''north'  
  4. PART_3     TS3        default

      

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11980046/viewspace-737113/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-03-08

  • 博文量
    39
  • 访问量
    10470