Oracle分区之二：表分区

1,Create table statement with partitioning

创建分区表的例子：

SQL>create table simple

(idx number, txt varchar2(20))

partition by range(idx)

(partition values less than (0)

         tablespace data01

,partition values less than (maxvalue));

注：一个表可以有65535个分区，每个分区就是一个segment.

2，分区键值的注意事项

a,分区键值必须是一个literal，即一个固定的常量值

b,除非可以是to_date函数转换，其他的任何表达式都不可以作为parttition key value

c,分区键值最多可以有16个列组成

 

3，范围分区

a,每个分区都必须显示定义

b,MAXVALUE包括了NULL的值

下面是一个范围分区的例子：

create table t1(id number,createdate date)
partition by range(createdate)
(
partition p1 values less than(to_date('2010-04-01','yyyy-mm-dd')),
partition p2 values less than(to_date('2010-07-01','yyyy-mm-dd')),
partition p3 values less than(to_date('2010-10-01','yyyy-mm-dd')),
partition p4 values less than(to_date('2011-01-01','yyyy-mm-dd')),
partition p_max values less than(maxvalue)
);

c,多列分区表

列的顺序非常重要，只有第一个列的值相等的时候，才比较第二个列的值，从而来决定该值应该到那个分区里。

下面是一个多列分区表的例子：

create table multicol
(unit number(1), subunit char(1))
partition by range (unit,subunit)
(partition P_2b values less than (2,'B')
,partition P_2c values less than (2,'C')
,partition P_3b values less than (3,'B')
,partition P_4x values less than (4,'X'));

 

4,列表分区

create table t3(id number,city varchar2(10))
partition by list(city)
(
partition p1 values ('SH','JS','ZJ'),
partition p2 values ('BJ','TJ','HB'),
partition p3 values ('GZ','SZ'),
partition p_others values (default)
);

 

5,哈希分区

需要指定分区列和分区的数量，分区的数量应该是2的幂，这样可以保证的数据均匀的分布在所有的分区里。

create table t2(id number,name varchar2(10))
partition by hash(id)
partitions 4;

或者是下面的写法

create table t2(id number,name varchar2(10))
partition by hash(id)
(
partition p1,
partition p2,
partition p3,
partition p4
);

 

复合分区：范围分区和列表分区
create table TT3(grade number(2),losal number(2),hisal varchar2(20))
partition by range(grade)
subpartition by list(hisal)
(
partition p1 values less than(10)
  (
   subpartition p1a values('安徽'),
   subpartition p1b values(default)
  ),
partition p2 values less than(20)
  (
   subpartition p2a values('湖南'),
   subpartition p2b values(default)
  ),
partition p3 values less than(30)
  (
   subpartition p3a values('北京'),
   subpartition p3b values(default)
  ));

 

6，索引组织表分区（IOT）

索引组织表可以进行范围或者哈希分区，分区的键值必须是IOT表主键的一个子集组合。

下面是一个例子：

create table simple (idx number, txt varchar2(20), id2 number,constraint s_pk primary key (idx,txt))
organization index
partition by hash(txt)
(
partition s_h1 tablespace EXAMPLE,
partition s_h2 tablespace USERS
);

 

7, 更新分区键值，有三种可能情况

a,更新后的键值依旧在自己的分区里

b,更新后导致map到了其他的分区，默认是不允许的

c,允许更新后map到其他的分区间，此时需要做如下的设置

alter table tablename enable row movement;

 

create table TB(idx number,name varchar2(20))
enable row movement
partition by range (idx)
(partition  p1 values less than (100),
 partition  p2 values less than (1000),
partition p_max values less than(maxvalue));

SQL> insert into TB VALUES (1,'Moving');

1 row created.

SQL> select idx,rowid from TB;

       IDX ROWID
---------- ------------------
         1 AAAM1dAAEAAAB7kAAA

SQL> update TB set idx=9;

1 row updated.

SQL> select idx,rowid from TB;

       IDX ROWID
---------- ------------------
         9 AAAM1dAAEAAAB7kAAA

SQL> update TB set idx=800;

1 row updated.

SQL> select idx,rowid from TB;

       IDX ROWID
---------- ------------------
       800 AAAM1eAAEAAAB7sAAA

注意看AAAM1d 和AAAM1e已经不在同一个块了。证明做了row movement.因为我创建表的时候设置的是enable row movement

下面测试下把他设置为disable,错误预期的出现了。

SQL> alter table TB disable row movement;

Table altered.

SQL> update TB set idx=1;
update TB set idx=1
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

 

8，下面列几种带分区的SQL语句

查询表的某一个分区
select * from TT3 partition (P1);
查询表的某一个子分区
delete from TT3 subpartition (p1a);
从某一个分区来创建新表
create table TT3_2 as select * from TT3 partition (p2);

 

9，表，分区，段的关系

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。分区表是一个由多个子对象(即分区)组成的一个对象。表示虚拟的，是由物理的分区组成的，分区也是一个segment。

 

10,常用视图

DBA_TABLES                                                               
DBA_PART_TABLES
DBA_TAB_PARTITIONS
DBA_PART_KEY_COLUMNS