ORACLE分割槽表梳理系列（一）- 分割槽表概述、分類、使用方法及注意事項
http://www.cnblogs.com/yumiko/p/6095036.html
前言:
本文著重闡述分割槽表的概念、優勢，以及常用分割槽表的使用方法及使用過程中的注意事項。
對於分割槽表的日常維護方法，如：索引的維護、分割槽的合併等，將在後續文章中再詳細介紹。
本文涉及的相應演示，使用的資料庫版本為oracle 11.2.0.4。
本文常用分割槽表的使用方法，主要以較為常規的範圍分割槽、列表分割槽、hash分割槽以及組合分割槽為主。
本文主要涉及的資料字典檢視包括XX_TAB_PARTITIONS、XX_TAB_SUBPARTITIONS以及XX_PART_TABLES。
注意示例中，使用紅色標記的說明。
1、分割槽表的概述
分割槽表就是透過使用分割槽技術，將一張大表，拆分成多個表分割槽（獨立的segment），從而提升資料訪問的效能，以及日常的可維護性。
分割槽表中，每個分割槽的邏輯結構必須相同。如：列名、資料型別。
分割槽表中，每個分割槽的物理儲存引數可以不同。如：各個分割槽所在的表空間。
對於應用而言完全透明，分割槽前後沒有變化，不需要進行修改。
需要注意：雖然各個分割槽可以存放在不同的表空間中，但這些表空間所使用的塊大小（block_size）必須一致。
需要注意：除了包含LONG以及LONG RAW欄位的表無法使用分割槽外，其他表均可以使用分割槽，包括含有LOB欄位的表。
2、分割槽表的優點
在維護性方面，可以在分割槽級別，針對單獨的分割槽，進行索引的維護、資料的載入以及備份恢復等操作。大大降低了維護時長。
在可用性方面，由於各個分割槽相對獨立，當一個分割槽處於維護或者出現故障時，不會影響到其他分割槽的正常使用。
在效能方面，oracle對於使用者的請求，只檢索需要的分割槽，從而提升效能。
在其他方面，由於分割槽表對於使用者是透明的，因此，不需要在分割槽後，對程式碼進行修改。
3、分割槽鍵的簡介
分割槽鍵就是決定表中的資料行，屬於哪一個分割槽的一組資料列。在執行DML操作時，ORACLE會根據分割槽鍵選擇分割槽。
4、常用分割槽表簡介及使用方法（含注意事項）
4.1、範圍分割槽（range partition）
範圍分割槽特點：
範圍分割槽主要依據分割槽鍵定義時給出的鍵值範圍，根據實際的取值，進行分割槽的選擇，進而在相應分割槽中儲存資料。
範圍分割槽比較合適存在以數字為導向，方便進行數字範圍劃分的資料列。如：員工表的僱傭日期列、工資列等。
範圍分割槽的資料分佈可能不均勻。
範圍分割槽定義規則：
1、在定義範圍分割槽時，每個分割槽定義必須使用 values less than（value）子句。其中（value）表示該分割槽的上限值。
2、在定義範圍分割槽時，最後一個分割槽可以是values less than（maxvalue）。其中（maxvalue）表示該分割槽儲存高於其他分割槽上限值的資料行。
示例：
示例涉及的測試資料來源，來自於ORACLE資料庫的測試使用者scott下的emp表。
emp表的大致資料情況如下：
SQL>select count(*) from emp;
COUNT(*)
----------
14
SQL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
下面採用範圍分割槽的方法建立分割槽，並將emp表的資料匯入該分割槽表。
這裡，使用HIREDATE列作為分割槽鍵進行分割槽操作。
建議，使用dbms_metadata.get_ddl的方法進行emp表結構建立語法的提取工作，並進而修改。
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>CREATE TABLE "EMPLOYEE_RANGE_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION EMPLOYEE_PART01 VALUES LESS THAN (TO_DATE('1981-01-01','yyyy-mm-dd'))
TABLESPACE USERS,
PARTITION EMPLOYEE_PART02 VALUES LESS THAN (TO_DATE('1982-01-01','yyyy-mm-dd'))
TABLESPACE USERS,
PARTITION EMPLOYEE_PART03 VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS
);
表已建立。
SQL >insert into EMPLOYEE_RANGE_PART select * from emp;
已建立 14 行。
SQL >commit;
提交完成。
檢視此時分割槽表的狀態，以及emp表的資料在分割槽表中的分佈情況。
注意，新建的資料表，統計資訊未必收集，可透過analyze table tablename compute statistics進行收集。
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_RANGE_PART';
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_RANGE_PART RANGE 3 VALID
SQL >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_RANGE_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
------------------------------ ---------- ------------------------------ ----
EMPLOYEE_PART03 3 TEST_TBS_03 YES
EMPLOYEE_PART02 10 TEST_TBS_02 YES
EMPLOYEE_PART01 1 TEST_TBS_01 YES
查詢單獨分割槽的資料資訊。
SQL >select * from EMPLOYEE_RANGE_PART partition(EMPLOYEE_PART01);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20
4.2、列表分割槽（list partition）
列表分割槽特點： 列表分割槽主要依據分割槽鍵定義時給出的取值列表，根據實際的取值，進行分割槽的選擇，進而在相應分割槽中儲存資料。
列表分割槽比較合適列唯一取值有限，且較為固定的資料列。如：員工表的部門列。
列表分割槽的資料分佈可能不均勻。
列表分割槽定義規則：
1、在定義範圍分割槽時，每個分割槽定義必須使用 values（'value01','value02'....）子句。表示該分割槽儲存包含相關value值的資料行。
2、在定義範圍分割槽時，最後一個分割槽可以是values（DEFAULT）。表示該分割槽儲存未在其他分割槽定義的資料行。
示例：
本示例資料來源，與上一節相同，均為emp表。
本示例中，將使用JOB列作為分割槽鍵進行分割槽操作。
首先，看一下JOB列中，目前涉及的工作分類有哪些。
SQL >select job,count(*) job from emp group by job ;
JOB JOB
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
從上面的資訊可以看出，目前涉及五種職位。
下面採用列表分割槽的方法進行分割槽表的建立，並倒入emp中的資料。其中，涉及PRESIDENT，MANAGER以及ANALYST三種職位的資料，存放在分割槽一；涉及CLERK職位的資料，存放在分割槽二；
涉及SALESMAN職位的資料，以及未來可能出現的新職位的資料，存放在分割槽三；
SQL > CREATE TABLE "EMPLOYEE_LIST_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_PART01 VALUES ('PRESIDENT','MANAGER','ANALYST')
TABLESPACE USERS,
PARTITION EMPLOYEE_PART02 VALUES ('CLERK')
TABLESPACE USERS,
PARTITION EMPLOYEE_PART03 VALUES (DEFAULT)
TABLESPACE USERS
);
表已建立。
SQL >insert into EMPLOYEE_LIST_PART select * from emp;
已建立 14 行。
SQL>commit;
提交完成。
首先觀察一下EMPLOYEE_PART02分割槽。
SQL >select SEGMENT_NAME,PARTITION_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS from user_segments where PARTITION_NAME='EMPLOYEE_PART02';
SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- -------------------- ----------- ------------ ----------
EMPLOYEE_LIST_PART EMPLOYEE_PART02 10 1169 1024
EMPLOYEE_RANGE_PART EMPLOYEE_PART02 10 145 1024
從上面的查詢結果可以看到，前面建立的兩張分割槽表，都採用相同的EMPLOYEE_PART02名字，進行了分割槽的命名。
雖然分割槽均處於相同的表空間下，但相互之前並未受到影響。
由此可以得出結論：
1、對於普通分割槽表，只要不是同一張的分割槽表，分割槽的命名可以相同；
2、對於組合分割槽表，同一張表的子分割槽命名，不能夠相同。（建立時會有提示，此處只給出結論，不再進行演示）
檢視此時分割槽表的狀態以及資料在分割槽間的分佈情況:
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_LIST_PART';
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_LIST_PART LIST 3 VALID
SQL >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_LIST_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART03 4 TEST_TBS_03 YES
EMPLOYEE_PART02 4 TEST_TBS_02 YES
EMPLOYEE_PART01 6 TEST_TBS_01 YES
查詢單獨分割槽的資料資訊:
SQL >select * from EMPLOYEE_LIST_PART partition(EMPLOYEE_PART01);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已選擇6行。
4.3、HASH分割槽（hash partition）
HASH分割槽特點： HASH分割槽主要透過hash演算法確定相應資料行應該被存放到哪個分割槽中。
HASH分割槽比較適合列差異值很多的資料列。
HASH分割槽的注意事項：
對於HASH分割槽，無法控制一條資料在分割槽間的具體分佈。具體分佈由hash演算法決定。 對於HASH分割槽，如果更改分割槽的數量，將導致所有資料在分割槽間的重新分佈。
HASH分割槽定義規則：
在定義HASH分割槽時，其分割槽數量應為2的N次方，如：2，4，8，16等
示例說明：
此示例的資料來源依然來源於emp表。
關於HASH分割槽的建立，有標準寫法以及簡易寫法。
標準寫法示例：
SQL >CREATE TABLE "EMPLOYEE_HASH_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY HASH (ENAME)
(
PARTITION EMPLOYEE_PART01 TABLESPACE users,
PARTITION EMPLOYEE_PART02 TABLESPACE users
);
表已建立。
SQL >insert into EMPLOYEE_HASH_PART select * from emp;
已建立 14 行。
SQL >commit;
提交完成。
檢視標準寫法下，HASH分割槽表的狀態及資料分佈情況
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_HASH_PART';
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_HASH_PART HASH 2 VALID
SQL > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_HASH_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART02 6 TEST_TBS_02 YES
EMPLOYEE_PART01 8 TEST_TBS_01 YES
簡單寫法示例：
CREATE TABLE "EMPLOYEE_HASH_PART_SAMPLE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY HASH (ENAME) PARTITIONS 2
STORE IN (USERS,USERS);
SQL > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_HASH_PART_SAMPLE';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
-------------------- ---------- ------------------------------ ----
SYS_P11074 6 TEST_TBS_02 YES
SYS_P11073 8 TEST_TBS_01 YES
從上圖可以看到，相較於標準寫法，簡單寫法建立的分割槽名字，由oracle自動指定。
4.4、組合分割槽（composite partition）
組合分割槽的特點：
組合分割槽中，主要透過在不同列上，使用“範圍分割槽”、“列表分割槽”以及“HASH分割槽”不同組合方式，進而實現組合分割槽。
組合分割槽中，分割槽本身沒有相應的segment，可以認為是一個邏輯容器，只有子分割槽擁有實際的segment，用於存放資料。
組合分割槽的注意事項：
在11g以前，組合分割槽主要有兩種組合方式：“RANGE-HASH”以及“RANGE-LIST”。
在11g以後，組合分割槽新增了四種組合方式：“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”。
示例說明：
關於本示例的資料來源依然選擇emp表為參考。
關於本示例的演示，將採用LIST-LIST的組合方式，建立組合分割槽。
關於其他組合方式效仿即可。
示例：
首先按照部門（deptno）進行分割槽，針對相同部門，再按照職位（JOB）是否為manager再次進行子分割槽。具體如下:
SQL >CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (DEPTNO)
SUBPARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE users
( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE users
( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE users
( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
)
);
表已建立。
SQL > insert into EMPLOYEE_LIST_LIST_PART select * from emp;
已建立 14 行。
SQL > commit;
提交完成。
需要注意：關於表空間（tablespace）的指定，可以放在分割槽一級指定，也可以放在子分割槽一級指定。
本示例中，表空間的指定，放在了分割槽一級指定。對於子分割槽直接套用分割槽指定。
SQL >select PARTITION_NAME,SUBPARTITION_COUNT,COMPOSITE,NUM_ROWS, TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';
PARTITION_NAME SUBPARTITION_COUNT COM NUM_ROWS TABLESPACE_NAME SEGM
----------------------- ------------------ --- ---------- --------------- ----
EMPLOYEE_DEPTNO_OTHERS 2 YES 6 TEST_TBS_03 NONE
EMPLOYEE_DEPTNO_20 2 YES 5 TEST_TBS_02 NONE
EMPLOYEE_DEPTNO_10 2 YES 3 TEST_TBS_01 NONE
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT, SUBPARTITIONING_TYPE,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';
TABLE_NAME PARTITION PARTITION_COUNT SUBPARTIT STATUS
------------------------------ --------- --------------- --------- --------
EMPLOYEE_LIST_LIST_PART LIST 3 LIST VALID
從上圖可以看到，對於組合分割槽，雖然成功建立了分割槽，但oracle並未對分割槽一級，分配實際的segment。
下面再觀察下子分割槽的建立情況：
SQL >select PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,SEGMENT_CREATED from USER_TAB_SUBPARTITIONS where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';
PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME NUM_ROWS SEG
----------------------- ------------------------------ --------------- ---------- ---
EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_MAGAGER TEST_TBS_01 1 YES
EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_DEFAULT TEST_TBS_01 2 YES
EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_MAGAGER TEST_TBS_02 1 YES
EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_DEFAULT TEST_TBS_02 4 YES
EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER TEST_TBS_03 1 YES
EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT TEST_TBS_03 5 YES
從上圖可以明顯看到，針對子分割槽一級，oracle確實分配了實際的segment用於資料的儲存。
此外，需要再次強調一點，對於同一張組合分割槽表而言，子分割槽的名字不能相同，即使不在相同的分割槽裡！
前言:
本文著重闡述分割槽表的概念、優勢，以及常用分割槽表的使用方法及使用過程中的注意事項。
對於分割槽表的日常維護方法，如：索引的維護、分割槽的合併等，將在後續文章中再詳細介紹。
本文涉及的相應演示，使用的資料庫版本為oracle 11.2.0.4。
本文常用分割槽表的使用方法，主要以較為常規的範圍分割槽、列表分割槽、hash分割槽以及組合分割槽為主。
本文主要涉及的資料字典檢視包括XX_TAB_PARTITIONS、XX_TAB_SUBPARTITIONS以及XX_PART_TABLES。
注意示例中，使用紅色標記的說明。
1、分割槽表的概述
分割槽表就是透過使用分割槽技術，將一張大表，拆分成多個表分割槽（獨立的segment），從而提升資料訪問的效能，以及日常的可維護性。
分割槽表中，每個分割槽的邏輯結構必須相同。如：列名、資料型別。
分割槽表中，每個分割槽的物理儲存引數可以不同。如：各個分割槽所在的表空間。
對於應用而言完全透明，分割槽前後沒有變化，不需要進行修改。
需要注意：雖然各個分割槽可以存放在不同的表空間中，但這些表空間所使用的塊大小（block_size）必須一致。
需要注意：除了包含LONG以及LONG RAW欄位的表無法使用分割槽外，其他表均可以使用分割槽，包括含有LOB欄位的表。
2、分割槽表的優點
在維護性方面，可以在分割槽級別，針對單獨的分割槽，進行索引的維護、資料的載入以及備份恢復等操作。大大降低了維護時長。
在可用性方面，由於各個分割槽相對獨立，當一個分割槽處於維護或者出現故障時，不會影響到其他分割槽的正常使用。
在效能方面，oracle對於使用者的請求，只檢索需要的分割槽，從而提升效能。
在其他方面，由於分割槽表對於使用者是透明的，因此，不需要在分割槽後，對程式碼進行修改。
3、分割槽鍵的簡介
分割槽鍵就是決定表中的資料行，屬於哪一個分割槽的一組資料列。在執行DML操作時，ORACLE會根據分割槽鍵選擇分割槽。
4、常用分割槽表簡介及使用方法（含注意事項）
4.1、範圍分割槽（range partition）
範圍分割槽特點：
範圍分割槽主要依據分割槽鍵定義時給出的鍵值範圍，根據實際的取值，進行分割槽的選擇，進而在相應分割槽中儲存資料。
範圍分割槽比較合適存在以數字為導向，方便進行數字範圍劃分的資料列。如：員工表的僱傭日期列、工資列等。
範圍分割槽的資料分佈可能不均勻。
範圍分割槽定義規則：
1、在定義範圍分割槽時，每個分割槽定義必須使用 values less than（value）子句。其中（value）表示該分割槽的上限值。
2、在定義範圍分割槽時，最後一個分割槽可以是values less than（maxvalue）。其中（maxvalue）表示該分割槽儲存高於其他分割槽上限值的資料行。
示例：
示例涉及的測試資料來源，來自於ORACLE資料庫的測試使用者scott下的emp表。
emp表的大致資料情況如下：
SQL>select count(*) from emp;
COUNT(*)
----------
14
SQL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
下面採用範圍分割槽的方法建立分割槽，並將emp表的資料匯入該分割槽表。
這裡，使用HIREDATE列作為分割槽鍵進行分割槽操作。
建議，使用dbms_metadata.get_ddl的方法進行emp表結構建立語法的提取工作，並進而修改。
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>CREATE TABLE "EMPLOYEE_RANGE_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION EMPLOYEE_PART01 VALUES LESS THAN (TO_DATE('1981-01-01','yyyy-mm-dd'))
TABLESPACE USERS,
PARTITION EMPLOYEE_PART02 VALUES LESS THAN (TO_DATE('1982-01-01','yyyy-mm-dd'))
TABLESPACE USERS,
PARTITION EMPLOYEE_PART03 VALUES LESS THAN (MAXVALUE)
TABLESPACE USERS
);
表已建立。
SQL >insert into EMPLOYEE_RANGE_PART select * from emp;
已建立 14 行。
SQL >commit;
提交完成。
檢視此時分割槽表的狀態，以及emp表的資料在分割槽表中的分佈情況。
注意，新建的資料表，統計資訊未必收集，可透過analyze table tablename compute statistics進行收集。
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_RANGE_PART';
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_RANGE_PART RANGE 3 VALID
SQL >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_RANGE_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
------------------------------ ---------- ------------------------------ ----
EMPLOYEE_PART03 3 TEST_TBS_03 YES
EMPLOYEE_PART02 10 TEST_TBS_02 YES
EMPLOYEE_PART01 1 TEST_TBS_01 YES
查詢單獨分割槽的資料資訊。
SQL >select * from EMPLOYEE_RANGE_PART partition(EMPLOYEE_PART01);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20
4.2、列表分割槽（list partition）
列表分割槽特點： 列表分割槽主要依據分割槽鍵定義時給出的取值列表，根據實際的取值，進行分割槽的選擇，進而在相應分割槽中儲存資料。
列表分割槽比較合適列唯一取值有限，且較為固定的資料列。如：員工表的部門列。
列表分割槽的資料分佈可能不均勻。
列表分割槽定義規則：
1、在定義範圍分割槽時，每個分割槽定義必須使用 values（'value01','value02'....）子句。表示該分割槽儲存包含相關value值的資料行。
2、在定義範圍分割槽時，最後一個分割槽可以是values（DEFAULT）。表示該分割槽儲存未在其他分割槽定義的資料行。
示例：
本示例資料來源，與上一節相同，均為emp表。
本示例中，將使用JOB列作為分割槽鍵進行分割槽操作。
首先，看一下JOB列中，目前涉及的工作分類有哪些。
SQL >select job,count(*) job from emp group by job ;
JOB JOB
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
從上面的資訊可以看出，目前涉及五種職位。
下面採用列表分割槽的方法進行分割槽表的建立，並倒入emp中的資料。其中，涉及PRESIDENT，MANAGER以及ANALYST三種職位的資料，存放在分割槽一；涉及CLERK職位的資料，存放在分割槽二；
涉及SALESMAN職位的資料，以及未來可能出現的新職位的資料，存放在分割槽三；
SQL > CREATE TABLE "EMPLOYEE_LIST_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_PART01 VALUES ('PRESIDENT','MANAGER','ANALYST')
TABLESPACE USERS,
PARTITION EMPLOYEE_PART02 VALUES ('CLERK')
TABLESPACE USERS,
PARTITION EMPLOYEE_PART03 VALUES (DEFAULT)
TABLESPACE USERS
);
表已建立。
SQL >insert into EMPLOYEE_LIST_PART select * from emp;
已建立 14 行。
SQL>commit;
提交完成。
首先觀察一下EMPLOYEE_PART02分割槽。
SQL >select SEGMENT_NAME,PARTITION_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS from user_segments where PARTITION_NAME='EMPLOYEE_PART02';
SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK BLOCKS
-------------------- -------------------- ----------- ------------ ----------
EMPLOYEE_LIST_PART EMPLOYEE_PART02 10 1169 1024
EMPLOYEE_RANGE_PART EMPLOYEE_PART02 10 145 1024
從上面的查詢結果可以看到，前面建立的兩張分割槽表，都採用相同的EMPLOYEE_PART02名字，進行了分割槽的命名。
雖然分割槽均處於相同的表空間下，但相互之前並未受到影響。
由此可以得出結論：
1、對於普通分割槽表，只要不是同一張的分割槽表，分割槽的命名可以相同；
2、對於組合分割槽表，同一張表的子分割槽命名，不能夠相同。（建立時會有提示，此處只給出結論，不再進行演示）
檢視此時分割槽表的狀態以及資料在分割槽間的分佈情況:
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_LIST_PART';
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_LIST_PART LIST 3 VALID
SQL >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_LIST_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART03 4 TEST_TBS_03 YES
EMPLOYEE_PART02 4 TEST_TBS_02 YES
EMPLOYEE_PART01 6 TEST_TBS_01 YES
查詢單獨分割槽的資料資訊:
SQL >select * from EMPLOYEE_LIST_PART partition(EMPLOYEE_PART01);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已選擇6行。
4.3、HASH分割槽（hash partition）
HASH分割槽特點： HASH分割槽主要透過hash演算法確定相應資料行應該被存放到哪個分割槽中。
HASH分割槽比較適合列差異值很多的資料列。
HASH分割槽的注意事項：
對於HASH分割槽，無法控制一條資料在分割槽間的具體分佈。具體分佈由hash演算法決定。 對於HASH分割槽，如果更改分割槽的數量，將導致所有資料在分割槽間的重新分佈。
HASH分割槽定義規則：
在定義HASH分割槽時，其分割槽數量應為2的N次方，如：2，4，8，16等
示例說明：
此示例的資料來源依然來源於emp表。
關於HASH分割槽的建立，有標準寫法以及簡易寫法。
標準寫法示例：
SQL >CREATE TABLE "EMPLOYEE_HASH_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY HASH (ENAME)
(
PARTITION EMPLOYEE_PART01 TABLESPACE users,
PARTITION EMPLOYEE_PART02 TABLESPACE users
);
表已建立。
SQL >insert into EMPLOYEE_HASH_PART select * from emp;
已建立 14 行。
SQL >commit;
提交完成。
檢視標準寫法下，HASH分割槽表的狀態及資料分佈情況
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_HASH_PART';
TABLE_NAME PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_HASH_PART HASH 2 VALID
SQL > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_HASH_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
-------------------- ---------- ------------------------------ ----
EMPLOYEE_PART02 6 TEST_TBS_02 YES
EMPLOYEE_PART01 8 TEST_TBS_01 YES
簡單寫法示例：
CREATE TABLE "EMPLOYEE_HASH_PART_SAMPLE"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY HASH (ENAME) PARTITIONS 2
STORE IN (USERS,USERS);
SQL > select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_HASH_PART_SAMPLE';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGM
-------------------- ---------- ------------------------------ ----
SYS_P11074 6 TEST_TBS_02 YES
SYS_P11073 8 TEST_TBS_01 YES
從上圖可以看到，相較於標準寫法，簡單寫法建立的分割槽名字，由oracle自動指定。
4.4、組合分割槽（composite partition）
組合分割槽的特點：
組合分割槽中，主要透過在不同列上，使用“範圍分割槽”、“列表分割槽”以及“HASH分割槽”不同組合方式，進而實現組合分割槽。
組合分割槽中，分割槽本身沒有相應的segment，可以認為是一個邏輯容器，只有子分割槽擁有實際的segment，用於存放資料。
組合分割槽的注意事項：
在11g以前，組合分割槽主要有兩種組合方式：“RANGE-HASH”以及“RANGE-LIST”。
在11g以後，組合分割槽新增了四種組合方式：“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”。
示例說明：
關於本示例的資料來源依然選擇emp表為參考。
關於本示例的演示，將採用LIST-LIST的組合方式，建立組合分割槽。
關於其他組合方式效仿即可。
示例：
首先按照部門（deptno）進行分割槽，針對相同部門，再按照職位（JOB）是否為manager再次進行子分割槽。具體如下:
SQL >CREATE TABLE "EMPLOYEE_LIST_LIST_PART"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
PARTITION BY LIST (DEPTNO)
SUBPARTITION BY LIST (JOB)
(
PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE users
( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE users
( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
),
PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE users
( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
)
);
表已建立。
SQL > insert into EMPLOYEE_LIST_LIST_PART select * from emp;
已建立 14 行。
SQL > commit;
提交完成。
需要注意：關於表空間（tablespace）的指定，可以放在分割槽一級指定，也可以放在子分割槽一級指定。
本示例中，表空間的指定，放在了分割槽一級指定。對於子分割槽直接套用分割槽指定。
SQL >select PARTITION_NAME,SUBPARTITION_COUNT,COMPOSITE,NUM_ROWS, TABLESPACE_NAME,SEGMENT_CREATED from user_tab_partitions where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';
PARTITION_NAME SUBPARTITION_COUNT COM NUM_ROWS TABLESPACE_NAME SEGM
----------------------- ------------------ --- ---------- --------------- ----
EMPLOYEE_DEPTNO_OTHERS 2 YES 6 TEST_TBS_03 NONE
EMPLOYEE_DEPTNO_20 2 YES 5 TEST_TBS_02 NONE
EMPLOYEE_DEPTNO_10 2 YES 3 TEST_TBS_01 NONE
SQL >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT, SUBPARTITIONING_TYPE,STATUS from user_part_tables where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';
TABLE_NAME PARTITION PARTITION_COUNT SUBPARTIT STATUS
------------------------------ --------- --------------- --------- --------
EMPLOYEE_LIST_LIST_PART LIST 3 LIST VALID
從上圖可以看到，對於組合分割槽，雖然成功建立了分割槽，但oracle並未對分割槽一級，分配實際的segment。
下面再觀察下子分割槽的建立情況：
SQL >select PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME,NUM_ROWS,SEGMENT_CREATED from USER_TAB_SUBPARTITIONS where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';
PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME NUM_ROWS SEG
----------------------- ------------------------------ --------------- ---------- ---
EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_MAGAGER TEST_TBS_01 1 YES
EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_DEFAULT TEST_TBS_01 2 YES
EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_MAGAGER TEST_TBS_02 1 YES
EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_DEFAULT TEST_TBS_02 4 YES
EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER TEST_TBS_03 1 YES
EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT TEST_TBS_03 5 YES
從上圖可以明顯看到，針對子分割槽一級，oracle確實分配了實際的segment用於資料的儲存。
此外，需要再次強調一點，對於同一張組合分割槽表而言，子分割槽的名字不能相同，即使不在相同的分割槽裡！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2143147/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE分割槽表梳理系列（二）- 分割槽表日常維護及注意事項Oracle
- ORACLE分割槽表梳理系列Oracle
- index_oracle索引梳理系列及分割槽表梳理IndexOracle索引
- Oracle分割槽表及分割槽索引Oracle索引
- 非分割槽錶轉換成分割槽表以及注意事項
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- SQL Server 表分割槽注意事項HXSQLServer
- oracle分割槽表的分類及測試Oracle
- oracle分割槽表和分割槽表exchangeOracle
- Oracle表分割槽技術概述Oracle
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽表和非分割槽表exchangeOracle
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 分割槽表及分割槽索引建立示例索引
- 如何查詢分割槽表的分割槽及子分割槽
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle
- oracle表分割槽Oracle
- Oracle 分割槽表Oracle
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- mysql 分表 分割槽MySql
- 全面學習分割槽表及分割槽索引(8)--增加和收縮表分割槽索引
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 全面學習分割槽表及分割槽索引(15)--修改表分割槽屬性和模板索引
- Oracle 建立分割槽表Oracle
- ORACLE分割槽表管理Oracle
- 全面學習分割槽表及分割槽索引(1)索引
- 深入學習分割槽表及分割槽索引(1)索引
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- oracle分割槽表學習（一）Oracle
- INTERVAL分割槽表鎖分割槽操作