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)

比較可以看到分割槽表採用並行的方式，其存在明顯的效能優勢！