IT技術互動交流平臺

ORACLE分區表梳理系列(一) 分區表概述 分類 使用方法及注意事項

作者:Yumiko_Sunny  來源:IT165收集  發布日期:2016-11-24 21:08:08

版權聲明:本文發布于http://www.cnblogs.com/yumiko/,版權由Yumiko_sunny所有,歡迎轉載。轉載時,請在文章明顯位置注明原文鏈接。若在未經作者同意的情況下,將本文內容用于商業用途,將保留追究其法律責任的權利。如果有問題,請以郵箱方式聯系作者(793113046@qq.com)。


前言

本文著重闡述分區表的概念、優勢,以及常用分區表的使用方法及使用過程中的注意事項。 對于分區表的日常維護方法,如:索引的維護、分區的合并等,將在后續文章中再詳細介紹。 本文涉及的相應演示,使用的數據庫版本為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表的大致數據情況如下:

--測試表emp的數據總記錄條數為14條
Yumiko@sunny >select count(*) from emp;

  COUNT(*)
----------
        14



Yumiko@sunny >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表結構創建語法的提取工作,并進而修改。

Yumiko@sunny >CREATE TABLE 'EMPLOYEE_RANGE_PART'
  2  ( 'EMPNO' NUMBER(4,0),
  3    'ENAME' VARCHAR2(10),
  4    'JOB' VARCHAR2(9),
  5    'MGR' NUMBER(4,0),
  6    'HIREDATE' DATE,
  7    'SAL' NUMBER(7,2),
  8    'COMM' NUMBER(7,2),
  9    'DEPTNO' NUMBER(2,0)
 10  )
 11  PARTITION BY RANGE (HIREDATE)
 12  (
 13  PARTITION EMPLOYEE_PART01 VALUES LESS THAN (TO_DATE('1981-01-01','yyyy-mm-dd'))
 14  TABLESPACE test_tbs_01,
 15  PARTITION EMPLOYEE_PART02 VALUES LESS THAN (TO_DATE('1982-01-01','yyyy-mm-dd'))
 16  TABLESPACE test_tbs_02,
 17  PARTITION EMPLOYEE_PART03 VALUES LESS THAN (MAXVALUE)
 18  TABLESPACE test_tbs_03
 19  );  

表已創建。



Yumiko@sunny >insert into EMPLOYEE_RANGE_PART select * from emp;
已創建 14 行。


Yumiko@sunny >commit;
提交完成。

查看此時分區表的狀態,以及emp表的數據在分區表中的分布情況。

注意,新建的數據表,統計信息未必收集,可通過analyze table tablename compute statistics進行收集。

Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS
  2  from dba_part_tables
  3  where TABLE_NAME='EMPLOYEE_RANGE_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_RANGE_PART            RANGE                   3 VALID




Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  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

查詢單獨分區的數據信息。

Yumiko@sunny >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列中,目前涉及的工作分類有哪些。

Yumiko@sunny >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職位的數據,以及未來可能出現的新職位的數據,存放在分區三;

CREATE TABLE 'EMPLOYEE_LIST_PART'
  2  ( 'EMPNO' NUMBER(4,0),
  3    'ENAME' VARCHAR2(10),
  4    'JOB' VARCHAR2(9),
  5    'MGR' NUMBER(4,0),
  6    'HIREDATE' DATE,
  7    'SAL' NUMBER(7,2),
  8    'COMM' NUMBER(7,2),
  9    'DEPTNO' NUMBER(2,0)
 10  )
 11  PARTITION BY LIST (JOB)
 12  (
 13  PARTITION EMPLOYEE_PART01 VALUES ('PRESIDENT','MANAGER','ANALYST')
 14  TABLESPACE test_tbs_01,
 15  PARTITION EMPLOYEE_PART02 VALUES ('CLERK')
 16  TABLESPACE test_tbs_02,
 17  PARTITION EMPLOYEE_PART03 VALUES (DEFAULT)
 18  TABLESPACE test_tbs_03
 19  );   

表已創建。


Yumiko@sunny >insert into EMPLOYEE_LIST_PART select * from emp;
已創建 14 行。


Yumiko@sunny >commit;
提交完成。

首先觀察一下EMPLOYEE_PART02分區。

Yumiko@sunny >select SEGMENT_NAME,PARTITION_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS
  2  from dba_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、對于組合分區表,同一張表的子分區命名,不能夠相同。(創建時會有提示,此處只給出結論,不再進行演示)

查看此時分區表的狀態以及數據在分區間的分布情況

Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS
  2  from dba_part_tables
  3  where TABLE_NAME='EMPLOYEE_LIST_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_LIST_PART             LIST                    3 VALID




Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  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

查詢單獨分區的數據信息。

Yumiko@sunny >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分區的創建,有標準寫法以及簡易寫法。

標準寫法示例:

Yumiko@sunny >CREATE TABLE 'EMPLOYEE_HASH_PART'
  2  ( 'EMPNO' NUMBER(4,0),
  3    'ENAME' VARCHAR2(10),
  4    'JOB' VARCHAR2(9),
  5    'MGR' NUMBER(4,0),
  6    'HIREDATE' DATE,
  7    'SAL' NUMBER(7,2),
  8    'COMM' NUMBER(7,2),
  9    'DEPTNO' NUMBER(2,0)
 10  )
 11  PARTITION BY HASH (ENAME)
 12  (
 13  PARTITION EMPLOYEE_PART01 TABLESPACE test_tbs_01,
 14  PARTITION EMPLOYEE_PART02 TABLESPACE test_tbs_02
 15  );   

表已創建。


Yumiko@sunny >insert into EMPLOYEE_HASH_PART select * from emp;
已創建 14 行。



Yumiko@sunny >commit;
提交完成。

查看標準寫法下,HASH分區表的狀態及數據分布情況

Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS
  2  from dba_part_tables
  3  where TABLE_NAME='EMPLOYEE_HASH_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT STATUS
------------------------------ --------- --------------- --------
EMPLOYEE_HASH_PART             HASH                    2 VALID



Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  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

簡單寫法示例:

Yumiko@sunny >CREATE TABLE 'EMPLOYEE_HASH_PART_SAMPLE'
  2  ( 'EMPNO' NUMBER(4,0),
  3    'ENAME' VARCHAR2(10),
  4    'JOB' VARCHAR2(9),
  5    'MGR' NUMBER(4,0),
  6    'HIREDATE' DATE,
  7    'SAL' NUMBER(7,2),
  8    'COMM' NUMBER(7,2),
  9    'DEPTNO' NUMBER(2,0)
 10  )
 11  PARTITION BY HASH (ENAME) PARTITIONS 2
 12  STORE IN (test_tbs_01,test_tbs_02);

表已創建。




Yumiko@sunny >select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED
  2  from dba_tab_partitions
  3  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再次進行子分區。具體如下:

Yumiko@sunny >CREATE TABLE 'EMPLOYEE_LIST_LIST_PART'
  2  ( 'EMPNO' NUMBER(4,0),
  3    'ENAME' VARCHAR2(10),
  4    'JOB' VARCHAR2(9),
  5    'MGR' NUMBER(4,0),
  6    'HIREDATE' DATE,
  7    'SAL' NUMBER(7,2),
  8    'COMM' NUMBER(7,2),
  9    'DEPTNO' NUMBER(2,0)
 10  )
 11  PARTITION BY LIST (DEPTNO)
 12  SUBPARTITION BY LIST (JOB)
 13  (
 14  PARTITION EMPLOYEE_DEPTNO_10 VALUES (10) TABLESPACE test_tbs_01
 15    ( SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER'),
 16      SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)
 17    ),
 18  PARTITION EMPLOYEE_DEPTNO_20 VALUES (20) TABLESPACE test_tbs_02
 19    ( SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER'),
 20      SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)
 21    ),
 22  PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT) TABLESPACE test_tbs_03
 23    ( SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER'),
 24      SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)
 25    )
 26  );

表已創建。




Yumiko@sunny >insert into EMPLOYEE_LIST_LIST_PART select * from emp;
已創建 14 行。




Yumiko@sunny >commit;
提交完成。

需要注意:關于表空間(tablespace)的指定,可以放在分區一級指定,也可以放在子分區一級指定。

本示例中,表空間的指定,放在了分區一級指定。對于子分區直接套用分區指定。

查看分區創建情況

Yumiko@sunny >select PARTITION_NAME,SUBPARTITION_COUNT,COMPOSITE,NUM_ROWS,
  2  TABLESPACE_NAME,SEGMENT_CREATED
  3  from dba_tab_partitions
  4  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




Yumiko@sunny >select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,
  2  SUBPARTITIONING_TYPE,STATUS
  3  from dba_part_tables
  4  where TABLE_NAME='EMPLOYEE_LIST_LIST_PART';

TABLE_NAME                     PARTITION PARTITION_COUNT SUBPARTIT STATUS
------------------------------ --------- --------------- --------- --------
EMPLOYEE_LIST_LIST_PART        LIST                    3 LIST      VALID

從上圖可以看到,對于組合分區,雖然成功創建了分區,但oracle并未對分區一級,分配實際的segment。

下面再觀察下子分區的創建情況:

Yumiko@sunny >select PARTITION_NAME,SUBPARTITION_NAME,
  2  TABLESPACE_NAME,NUM_ROWS,SEGMENT_CREATED 
  3  from USER_TAB_SUBPARTITIONS 
  4  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用于數據的存儲。

此外,需要再次強調一點,對于同一張組合分區表而言,子分區的名字不能相同,即使不在相同的分區里

Tag標簽: 分區表   使用方法   注意事項  
  • 專題推薦

About IT165 - 廣告服務 - 隱私聲明 - 版權申明 - 免責條款 - 網站地圖 - 網友投稿 - 聯系方式
本站內容來自于互聯網,僅供用于網絡技術學習,學習中請遵循相關法律法規
香港最快开奖现场直播结果