数据库教程:Oracle分区表详解

一、分区表的概述：

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。

1、分区表的优点：

由于将数据分散到各个分区中，减少了数据损坏的可能性； 可以对单独的分区进行备份和恢复； 可以将分区映射到不同的物理磁盘上，来分散IO； 提高可管理性、可用性和性能。

2、什么时候用分区表

单表过大，超过一定范围，建议以g计算表，均可考虑用分区 历史数据据需要剥离的 查询特征非常明显，比如是按整年、整月或者按某个范围！

3、分区表的类型

range分区，按范围 list分区，列举分区 hash分区，根据hash值进行的散列分区 复合分区，9i开始，Oracle就包括了2种复合分区，RANGE-HASH和RANGE-LIST。在11g，Oracle一下就提供了4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。

二、创建分区的举例

1、range分区

  create table p_table(      obj_id number(10),      object_id number(10),      object_name varchar2(128),      owner varchar2(30),      object_type varchar2(19),      created date)  partition by range (obj_id)  (partition obj_id1 values less than (20000),  partition obj_id2 values less than (40000),  partition obj_id3 values less than (60000),  partition obj_id4 values less than (80000),  partition obj_id5 values less than (99999));

2、list分区

  create table l_table(      obj_id number(10),      object_id number(10),      object_name varchar2(128),      owner varchar2(30),      segment_type varchar2(19),      created date)  partition by LIST(segment_type)  (partition l_type1 values ('LOBINDEX') tablespace my_space1,   partition l_type2 values ('VIEW') tablespace my_space2,   partition l_type3 values ('TABLE') tablespace my_space2);

3、hash分区

  create table h_table(      obj_id number(10),      object_id number(10),      object_name varchar2(128),      owner varchar2(30),      object_type varchar2(19),      created date)  partition by hash(object_id)  ( partition h_objid1,   partition h_objid2,   partition h_objid3,   partition h_objid4);

4、复合分区

  Oracle11g一下就提供了4种复合分区：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。  4.1range-range  CREATE TABLE r_r_table(      obj_id number(10),      object_id number(10),      object_name varchar2(128),      owner varchar2(30),      object_type varchar2(19),      created date)  PARTITION BY RANGE (CREATED)  SUBPARTITION BY RANGE (obj_id)  (PARTITION p_level1_1 VALUES LESS THAN (TO_DATE('2013-04-01','YYYY-MM-DD'))  (  SUBPARTITION p_level2_1 VALUES LESS THAN (40000),  SUBPARTITION p_level2_2 VALUES LESS THAN (80000),  SUBPARTITION p_level2_3 VALUES LESS THAN (maxvalue)  ),   PARTITION p_level1_2 VALUES LESS THAN (TO_DATE('2013-07-1', 'YYYY-MM-DD'))  (  SUBPARTITION p_level2_4 VALUES LESS THAN (40000),  SUBPARTITION p_level2_5 VALUES LESS THAN (80000),  SUBPARTITION p_level2_6 VALUES LESS THAN (maxvalue)  ),   PARTITION p_level1_3 VALUES LESS THAN (TO_DATE('2013-10-1', 'YYYY-MM-DD'))  (  SUBPARTITION p_level2_7 VALUES LESS THAN (40000),  SUBPARTITION p_level2_8 VALUES LESS THAN (80000),  SUBPARTITION p_level2_9 VALUES LESS THAN (maxvalue)  )   );  --注意，如果加了maxvalue，那么就不能add的方式添加分区了！

5、11g自动分区技术

  CREATE TABLE auto_partition (      OBJECT_ID NUMBER,      OBJECT_TYPE VARCHAR2(128),      CREATED DATE)  PARTITION BY RANGE (CREATED)  INTERVAL(NUMTOYMINTERVAL(1, 'month'))  (PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2011', 'dd-mm-yyyy')));    --这样，就会每个月系统自动生成一个分区  --INTERVAL (NUMTODSINTERVAL(1,'day'))   表示每天  --INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))   表示每年

三、分区操作

1、添加分区

  --添加分区  ALTER TABLE mytest ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));    --添加子分区  alter table mytest modify partition p3 add subpartition values less than(TO_DATE('2003-06-01','YYYY-MM-DD'));    说明：如果采用了maxvalue，则不能用add，而应该采用split

2、删除分区

  --删除分区  ALTER TABLE mytest DROP PARTITION P3;    --删除子分区  ALTER TABLE mytest DROP SUBPARTITION P4SUB1;    说明：如果表只剩下一个分区了，则不能drop，而应该drop表。在复合分区中，如果一个分区只剩下一个子分区，也不能drop

3、truncate 分区

  --truncate分区  ALTER TABLE mytest TRUNCATE PARTITION P2;    --truncate子分区  ALTER TABLE mytest TRUNCATE SUBPARTITION P2SUB1;

4、合并分区

  ALTER TABLE mytest MERGE PARTITIONS P1,P2 INTO PARTITION P2;  注意：不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并

5、拆分分区

  ALTER TABLE mytest split PARTITION P2 AT(TO_DATE('2013-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6、接合分区

  ALTER TABLE mytest COALESCA PARTITION;  说明：这个只能用于hash分区

7、重命名分区

  ALTER TABLE mytest RENAME PARTITION P21 TO P2;

8、分区表的查询

  --查询分区  select * from r_r_table partition(p_level1_1);        --查询子分区  select * from r_r_table subpartition(p_level2_1); 

9、分区表索引重建

需要了解更多数据库技术:Oracle分区表详解，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!

  Alter index ind_mytest rebuild partition p1 ;  alter index ind_created_test rebuild subpartition p_level2_5 online;