ORACLE分区表的使用和管理

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