oracle 分区表详解
一、分区表的概述：
Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。
1、分区表的优点：
（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
（2）可以对单独的分区进行备份和恢复；
（3）可以将分区映射到不同的物理磁盘上，来分散IO；
（4）提高可管理性、可用性和性能。
2、什么时候用分区表
(1) 单表过大，超过一定范围，建议以g计算表，均可考虑用分区
（2）历史数据据需要剥离的
（3）查询特征非常明显，比如是按整年、整月或者按某个范围！
3、分区表的类型
1、range分区，按范围
2、list分区，列举分区
3、hash分区，根据hash值进行的散列分区
4、复合分区，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')) 表示每年
三、分区操作
--添加分区
添加分区
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
--删除分区
删除分区
ALTER TABLE mytest DROP PARTITION P3;
删除子分区
ALTER TABLE mytest DROP SUBPARTITION P4SUB1;
说明，如果表只剩下一个分区了，则不能drop，而应该drop表。在复合分区中，如果一个分区只剩下一个子分区，也不能drop
--truncate 分区
truncate分区
ALTER TABLE mytest TRUNCATE PARTITION P2;
truncate子分区
ALTER TABLE mytest TRUNCATE SUBPARTITION P2SUB1;
--合并分区
ALTER TABLE mytest MERGE PARTITIONS P1,P2 INTO PARTITION P2;
注意：不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并
--拆分分区
ALTER TABLE mytest split PARTITION P2 AT(TO_DATE('2013-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
--接合分区
ALTER TABLE mytest COALESCA PARTITION;
说明：这个只能用于hash分区
--重命名分区
ALTER TABLE mytest RENAME PARTITION P21 TO P2;
--分区表的查询
SQL> select * from r_r_table subpartition(p_level2_1); --查询子分区
no rows selected
SQL> select * from r_r_table partition(p_level1_1); --查询分区
no rows selected
--分区表索引重建
Alter index ind_mytest rebuild partition p1 ;
alter index ind_created_test rebuild subpartition p_level2_5 online;
五、性能比较
SQL> set autot trace exp;
SQL> set linesize 400;
5.1非并行下的表范围扫描
--分区表扫描的执行计划
SQL> select * from penggj.p_table where obj_id between 10000 and 30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1095083444
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14865 | 1872K| 99 (8)| 00:00:02 | | |
| 1 | PARTITION RANGE ITERATOR| | 14865 | 1872K| 99 (8)| 00:00:02 | 1 | 2 |
|* 2 | TABLE ACCESS FULL | P_TABLE | 14865 | 1872K| 99 (8)| 00:00:02 | 1 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJ_ID">=10000 AND "OBJ_ID"<=30000)
Note
-----
- dynamic sampling used for this statement (level=2)
--普通表全表扫描的执行计划
SQL> select * from penggj.n_table where obj_id between 10000 and 30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2316247272
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22714 | 2861K| 193 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| N_TABLE | 22714 | 2861K| 193 (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJ_ID">=10000 AND "OBJ_ID"<=30000)
Note
-----
- dynamic sampling used for this statement (level=2)
说明：从上面两个查询的执行计划来看分区表效率更高，因为在整个扫描过程中，分区表只扫描了两个分区，而普通表进行了全表扫描！
5.1并行下的表范围扫描
--分区表范围扫描
SQL> select /*+ parallel(p_table,4)*/ * from penggj.p_table where obj_id between 10000 and 30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2269943597
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14865 | 1872K| 27 (4)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14865 | 1872K| 27 (4)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14865 | 1872K| 27 (4)| 00:00:01 | 1 | 2 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| P_TABLE | 14865 | 1872K| 27 (4)| 00:00:01 | 1 | 2 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJ_ID">=10000 AND "OBJ_ID"<=30000)
Note
-----
- dynamic sampling used for this statement (level=2)
--普通表采用并行的方式进行
SQL> select /*+ parallel(p_table,4)*/ * from penggj.n_table where obj_id between 10000 and 30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2316247272
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22714 | 2861K| 193 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| N_TABLE | 22714 | 2861K| 193 (2)| 00:00:03 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJ_ID">=10000 AND "OBJ_ID"<=30000)
Note
-----
- dynamic sampling used for this statement (level=2)
比较可以看到分区表采用并行的方式，其存在明显的性能优势！
