oracle 分区表管理

时间:2013年07月21日 | 分类:Oracle基础篇 | 评论:0 条 | 浏览:1,419 次

1.oracle分区表:
逻辑上是一张大表,物理上是由若干小表组成

2.oracle分区表的分类:
范围分区表(Range partitioning)
列表分区表(List partitioning)
哈希分区表(Hash partitioning)
组合分区表:
(Composite range-hash partitioning:先做hash,再做范围)
(Composite range-list partitioning:先做列表,再做范围)

3.范例:

(1).创建范围分区表

  1. --为各个分区准备独立的表空间  
  2. create tablespace test_space01 datafile '/u01/app/oracle/oradata/orcl/ts_test01.dbf' size 50m  
  3. create tablespace test_space02 datafile '/u01/app/oracle/oradata/orcl/ts_test02.dbf'size 50m  
  4. create tablespace test_space03 datafile '/u01/app/oracle/oradata/orcl/ts_test03.dbf'size 50m  
  5. create tablespace test_space04 datafile '/u01/app/oracle/oradata/orcl/ts_test04.dbf'size 50m  
  6. --创建分区表,  
  7. CREATE TABLE range_example  
  8. (  
  9. range_key_column DATE,  
  10. DATA VARCHAR2(20),  
  11. ID integer  
  12. )  
  13. PARTITION BY RANGE(range_key_column)  
  14. (  
  15. PARTITION part01 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space01,  
  16. PARTITION part02 VALUES LESS THAN(TO_DATE('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space02,  
  17. PARTITION part03 VALUES LESS THAN(TO_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space03,  
  18. PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test_space04  
  19. );  
  20. --插入测试数据  
  21. insert into range_examplevalues(TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '1111', 1);  
  22. insert into range_examplevalues(TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2222', 2);  
  23. insert into range_examplevalues(TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '3333', 3);  
  24. commit;  
  25. --在表上执行查询  
  26. select * from range_example;  
  27. --在表分区上执行查询  
  28. select * from range_example partition(part01);  

(2).创建列表分区表
创建一个按字段数据列表固定可枚举值分区的表。插入记录分区字段的值必须在列表中, 否则不能被插入。
示例代码:

  1. CREATE TABLE list_example(  
  2. dname VARCHAR2(10),  
  3. DATA VARCHAR2(20)  
  4. )  
  5. PARTITION BY LIST(dname)  
  6. (  
  7. PARTITION part01 VALUES('初始登记','转移登记'),  
  8. PARTITION part02 VALUES('更名登记','楼盘变更'),  
  9. PARTITION part03 VALUES('抵押登记'),  
  10. PARTITION part03 VALUES('限制登记')  
  11. );  

(3).创建哈希分区表
创建一个按字段数据 Hash 值分区的表
示例代码:

  1. CREATE TABLE hash_example(  
  2. hash_key_column DATE,  
  3. DATA VARCHAR2(20)  
  4. )  
  5. PARTITION BY HASH(hash_key_cloumn)  
  6. (  
  7. PARTITION part01,  
  8. PARTITION part02  
  9. );  

(4).组合分区表
在分区中可以再建立子分区,以实现分区组合。可任意对上述各类分区进行组合分区。
此例中创建了一个由范围分区和哈希分区组合实现分区的表。
示例代码:

  1. CREATE TABLE range_hash_example(  
  2. range_column_key int,  
  3. hash_column_key INT,  
  4. DATAVARCHAR2(20)  
  5. )  
  6. PARTITION BY RANGE(range_column_key)  
  7. SUBPARTITION BY HASH(hash_column_key) SUBPARTITIONS 2  
  8. (  
  9. PARTITION part_1 VALUES LESS THAN (100000000)  
  10. (  
  11. SUBPARTITION part_1_sub_1,  
  12. SUBPARTITION part_1_sub_2,  
  13. SUBPARTITION part_1_sub_3  
  14. ),  
  15. PARTITION part_2 VALUES LESS THAN (200000000)  
  16. (  
  17. SUBPARTITION part_2_sub_1,  
  18. SUBPARTITION part_2_sub_2  
  19. )  
  20. );  
  21. --注 subpartitions 2 并不是指定 subpartition 的个数一定为2,实际上每个分区的子分区个数可以不同。  
  22. 如果不指定 subpartition 的具体明细,则系统按照 subpartitions 的值  
  23. 指定 subpartition 的个数生成子分区,名称由系统定义 。  

4.分区表其他相关操作:

(1).增加表分区

  1. -- range partitioned table  
  2. ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));  
  3. --list partitioned table  
  4. ALTER TABLE list_example ADD PARTITION part04 VALUES ('TE');  
  5. --Adding Values for a List Partition  
  6. ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES('MIS');  
  7. --Dropping Values from a List Partition  
  8. ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES('MIS');  
  9. --hash partitioned table  
  10. ALTER TABLE hash_example ADD PARTITION part03;  

(2).增加 subpartition

  1. ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4;  
  2. # 注:hash partitioned table 新增 partition 时,现有表的中所有 data 都有重新计算 hash值,然后重新分配到分区中。所以被重新分配的分区的 indexes 需要 rebuild 。  

(3).删除分区

  1. ALTER TABLE ... DROP PARTITION part_name;  

(4).分区合并

  1. 合并父分区  
  2. ALTER TABLE range_example MERGE PARTITIONS part01_1, part01_2 INTO PARTITION part01 UPDATE INDEXES;  
  3. --如果省略 update indexes 子句的话,必须重建受影响的分区的 index;  
  4. ALTER TABLE range_example MODIFY PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;  
  5. 合并子分区  
  6. ALTER TABLE composite_example MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;  

(5).转换分区
可以将分区表转换成非分区表,或者几种不同分区表之间的转换。
如下:

  1. CREATE TABLE hash_part02 AS SELECT * FROM hash_example WHERE 1=2;  
  2. ALTER TABLE hash_example EXCHANGE PARTITION part02 WITH TABLE hash_part02;  
  3. 这时,分区表 hash_example 中的 part02分区的资料将被转移到 hash_part02这个非分区表中。  

(6).移动表分区到另一个表空间

  1. alter table sales move partition part03 tablespace example;  
  2. ALTER TABLE sales RENAME PARTITION part03 TO sales_q1_q2_1998;  

(7).从一个范围分区表分离一个分区

  1. ALTER TABLE sales split PARTITION sales_q1_q2_1998 at (to_date('1998-04-01','yyyy-mm-dd')) into (partition sales_q1_1998,partition sales_q2_1998);  

(8).清空分区表内某个分区的数据

  1. alter table sales truncate partition sales_q2_1998 ;  

(9).关于分区表和索引
在分区表上可以建立三种类型的索引:

A.和普通表一样的全局索引;

B.全局分区索引;

C.本地分区索引。

以表 range_example 为例。

  1. A.建立普通的索引  
  2. create index com_index_range_example_id on range_example(id);  
  3.   
  4. B.建立本地分区索引  
  5. create index local_index_range_example_id on range_example(id) local;  
  6.   
  7. C.建立全局分区索引  
  8. create index gidx_range_exampel_id on range_example(id)  
  9. GLOBAL partition by range(id)  
  10. (  
  11. part_01 values less than(1000),  
  12. part_02 values less than(MAXVALUE)  
  13. );  
  14. 对于分区索引的删除,local index 不能指定分区名称,单独的删除分区索引。  
  15. local index 对应的分区会伴随着 data 分区的删除而一起被删除。  
  16. globalpartition index可以指定分区名称,删除某一分区。但是有一点要注意,如果该分区不为空,则会导致更高一级的索引分区被置为 UNUSABLE 。  
  17. ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句将导致 part_02 状态为 UNUSABLE  

5.分区表相关的视图:
user_part_tables
user_tab_partitions
user_part_key_columns

×