分区表的使用和管理

-----------------------------------------------------

分区表的使用和管理

-----------------------------------------------------

1创建和使用分区表

Oracle分区表分为四类：范围分区表；列表分区表；哈希分区表；组合分区表

范围分区表

创建一个按字段数据范围分区的表，分区置于指定的不同表空间中

 

示例代码：

--为各个分区准备独立的表空间

create tablespace test_space01 datafile‘d:/tbs01.dbf’ size 50m

create tablespace test_space02 datafile‘d:/tbs02.dbf’ size 50m

create tablespace test_space03 datafile‘d:/tbs03.dbf’ size 50m

create tablespace test_space04 datafile‘d:/tbs04.dbf’ size 50m

 

--创建分区表，

CREATE TABLE range_example(

    range_key_column DATE,

    DATA VARCHAR2(20),

    ID integer

)

PARTITION BY RANGE(range_key_column)

(  

PARTITION part01 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACEtest_space01,

PARTITION part02 VALUES LESS THAN(TO_DATE('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space02,

PARTITION part03 VALUES LESS THAN(TO_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space03,

PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test_space04

);

 

--插入测试数据

insert into range_examplevalues(TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '1111', 1);

insert into range_examplevalues(TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2222', 2);

insert into range_examplevalues(TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '3333', 3);

commit;

 

--在表上执行查询

select * from range_example;

 

 

--在表分区上执行查询

select * from range_example partition(part01);

 

列表分区表

创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中，否则不能被插入。

示例代码：

CREATE TABLE list_example(

dnameVARCHAR2(10),

DATA VARCHAR2(20)

)

PARTITION BY LIST(dname)

(

PARTITION part01VALUES('初始登记','转移登记'),

PARTITION part02 VALUES('更名登记','楼盘变更'),

PARTITION part03 VALUES('抵押登记'),

PARTITION part03 VALUES('限制登记')

);

哈希分区表

创建一个按字段数据Hash值分区的表

示例代码：

CREATE TABLE hash_example(

     hash_key_column DATE,

     DATA VARCHAR2(20)

)

PARTITION BY HASH(hash_key_cloumn)

(

    PARTITION part01,

    PARTITION part02

);

组合分区表

在分区中可以再建立子分区，以实现分区组合。可任意对上述各类分区进行组合分区。

此例中创建了一个由范围分区和哈希分区组合实现分区的表。

示例代码：

CREATE TABLE range_hash_example(

 range_column_key int,

 hash_column_key INT,

 DATAVARCHAR2(20)

)

PARTITION BY RANGE(range_column_key)

SUBPARTITION BY HASH(hash_column_key)SUBPARTITIONS 2

(

PARTITION part_1 VALUES LESS THAN (100000000)

(

   SUBPARTITION part_1_sub_1,

   SUBPARTITION part_1_sub_2,

   SUBPARTITION part_1_sub_3

    ),

PARTITION part_2 VALUES LESS THAN (200000000)

(

     SUBPARTITION part_2_sub_1,

   SUBPARTITION part_2_sub_2

)

);

--注 subpartitions 2 并不是指定subpartition的个数一定为2，实际上每个分区的子分区个数可以不同。如果不指定subpartition的具体明细，则系统按照subpartitions的值指定subpartition的个数生成子分区，名称由系统定义 。

2增加表分区

-- range partitioned table

ALTER TABLE range_example ADD PARTITIONpart04 VALUES LESS THAN (TO_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));

 

--list partitioned table

ALTER TABLE list_example ADD PARTITIONpart04 VALUES ('TE');

 

--Adding Values for a List Partition

ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES('MIS');

 

--Dropping Values from a List Partition

ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES('MIS');

 

--hash partitioned table

ALTER TABLE hash_example ADD PARTITIONpart03;

 

--增加subpartition

ALTER TABLE range_hash_example MODIFY PARTITIONpart_1 ADD SUBPARTITION part_1_sub_4;

 

注:hash partitioned table 新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中。所以被重新分配的分区的 indexes需要rebuild 。

3删除分区

ALTER TABLE ... DROP PARTITION part_name;

4分区合并

合并父分区

ALTER TABLE range_example MERGE PARTITIONSpart01_1, part01_2 INTO PARTITION part01 UPDATE INDEXES;

--如果省略update indexes子句的话，必须重建受影响的分区的index;

ALTER TABLE range_example MODIFY PARTITIONpart02 REBUILD UNUSABLE LOCAL INDEXES;

合并子分区

ALTER TABLE composite_example

MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;

5转换分区

可以将分区表转换成非分区表，或者几种不同分区表之间的转换。

如下：

CREATE TABLE hash_part02 AS SELECT * FROMhash_example WHERE 1=2;

ALTER TABLE hash_example EXCHANGE PARTITIONpart02 WITH TABLE hash_part02;

这时，分区表hash_example中的part02分区的资料将被转移到hash_part02这个非分区表中。

6关于分区表和索引

在分区表上可以建立三种类型的索引：1和普通表一样的全局索引；2.全局分区索引；3.本地分区索引。

它们之间的区别如下图示:

 

 

以表range_example为例。

1.建立普通的索引

create index com_index_range_example_id onrange_example(id);

 

2.建立本地分区索引

create index  local_index_range_example_id onrange_example(id) local;

 

3.建立全局分区索引

create index gidx_range_exampel_id onrange_example(id)

GLOBAL partition by  range(id)

(

 part_01 values less than(1000),

 part_02 values less than(MAXVALUE)

);

 

对于分区索引的删除，local index 不能指定分区名称，单独的删除分区索引。

local index 对应的分区会伴随着data分区的删除而一起被删除。globalpartition index 可以指定分区名称，删除某一分区。但是有一点要注意，如果该分区不为空，则会导致更高一级的索引分区被置为UNUSABLE 。

 ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句将导致part_02 状态为UNUSABLE

一、什么场景、时间适合用分区表？

1、大数据量的表，比如大于2GB。一方面2GB文件对于32位OS是一个上限，另外备份时间长。

2、包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-only

        就我实际工作中了解到的几种情况：

1. 全国法人单位名录库约有700-800万条记录，字段约有100个。可按所属省行政区划、指标枚举等字段建立分区表。
2. 北京统计宏观数据库，在村一级采用一维表存储，随着指标增多，记录可达千万至亿条级。
3. 上海市房管局系统的权属数据，按照市中心及19个区县，共划分了20个分区，以期提高数据的存取效率。

二、分区表有什么优势？

1、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

2、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

3、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；

4、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

三、分区表有几种？

Oracle分区表分为四类：范围分区表、列表分区表、哈希分区表、组合分区表。

1. 范围分区表：创建一个按字段数据范围分区的表，分区置于指定的不同表空间中。
2. 列表分区表：创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中，否则不能被插入。
3. 哈希分区表：创建一个按字段数据Hash值分区的表。
4. 组合分区表：在分区中可以再建立子分区，以实现分区组合。可任意对上述各类分区进行组合分区。

四、如何建立分区表？

1. ORACLE分区表的使用和管理：http://www.cnblogs.com/advocate/archive/2010/09/03/1816628.html
2. Oracle中分区表的使用：http://download.csdn.net/detail/wildboy2001/4762087
   

五、其他

1. oracle分区表总结：http://www.codesky.net/article/200805/119413.html
   
2. 什么时候使用分区：http://wenku.it168.com/d_000063679.shtml