【DB筆試面試470】分區表有什麼優點？分區表有哪幾類？如何選擇用哪種類型的分區表？

• 2019 年 10 月 11 日
• 筆記

題目部分

分區表有什麼優點？分區表有哪幾類？如何選擇用哪種類型的分區表？

答案部分

當表中的數據量不斷增大時，查詢數據的速度就會變慢，應用程式的性能就會下降，這時就應該考慮對錶進行分區。當對錶進行分區後，在邏輯上，表仍然是一張完整的表，只是將表中的數據在物理上可能存放到多個表空間或物理文件上。當查詢數據時，不至於每次都掃描整張表。Oracle可以將大表或索引分成若干個更小、更方便管理的部分，每一部分稱為一個分區，這樣的表稱為分區表。SQL語句使用分區表比全表能提供更好的數據處理與訪問的性能。即使某些分區不可用，其它分區仍然可用，這叫做分區獨立性。

分區表的一些限制條件：

①　簇表不能進行分區。

②　不能分割含有LONG或LONG RAW列的表。

③　索引組織表不能進行範圍分區。

何時考慮分區？

對大表進行分區，將有益於大表操作的性能和大表的數據的維護。官方文檔說通常當表的大小超過2GB，或對於OLTP系統，當表的記錄超過1000萬時，都應考慮對錶進行分區。

分區表有什麼優點？

分區表有如下的優點：

①　增強可用性：如果表的一個分區由於系統故障而不能使用，那麼表的其餘好的分區仍可以使用。

②　減少關閉時間：如果系統故障隻影響表的一部分分區，那麼只有這部分分區需要修復，可能比整個大表修復花的時間更少。

③　維護輕鬆：單獨管理每個分區比管理單個大表要輕鬆得多。

④　均衡I/O：可以把表的不同分區分配到不同的磁碟來平衡I/O，改善性能。

⑤　改善性能：對大表的查詢、增加、修改等操作可以分解到表的不同分區來並行執行，可使運行速度更快。

⑥　分區對用戶透明，最終用戶感覺不到分區的存在。

有哪些類型的分區？如何選擇用哪種類型的分區表？

Oracle的分區可以分為：

● 範圍分區（RANGE PARTITION）

● 哈希分區（HASH PARTITION）

● 列表分區（LIST PARTITION）

● 引用分區（REFERENCE PARTITION）

● 複合分區（組合分區）

● INTERVAL分區（間隔分區）

● 系統分區

INTERVAL分區和系統分區是Oracle 11g的新特性，由於篇幅原因，本書中不講解這2個分區，讀者可自行查閱官方文檔進行學習。尤其對於INTERVAL分區在生產環境中還是比較實用的。下面作者將對範圍、哈希、列表和複合分區分別進行講解。

1、RANGE（範圍）分區

在如下幾種情況下會使用到範圍分區：

（1）頻繁地被一個時間範圍謂詞掃描。

（2）維護一個時間滾動的數據窗口（Rolling Window Of Data）。

（3）保存歷史數據的表。

例如，在下面的例子中，給數據表SALE_DATA在時間欄位SALES_DATE上按照每個月一個分區的方式來創建一個範圍分區。

CREATE TABLE SALE_DATA  (SALE_ID NUMBER(5),  SALESMAN_NAME VARCHAR2(30),  SALES_AMOUNT NUMBER(10),  SALES_DATE DATE)  PARTITION BY RANGE(SALES_DATE)  (  PARTITION SALES_2009_1 VALUES LESS THAN(TO_DATE('01/02/2009','DD/MM/YYYY')),  PARTITION SALES_2009_2 VALUES LESS THAN(TO_DATE('01/03/2009','DD/MM/YYYY')),  PARTITION SALES_2009_3 VALUES LESS THAN(TO_DATE('01/04/2009','DD/MM/YYYY')),  PARTITION SALES_2009_4 VALUES LESS THAN(TO_DATE('01/05/2009','DD/MM/YYYY')),  PARTITION SALES_2009_5 VALUES LESS THAN(TO_DATE('01/06/2009','DD/MM/YYYY')),  PARTITION SALES_2009_6 VALUES LESS THAN(TO_DATE('01/07/2009','DD/MM/YYYY')),  PARTITION SALES_2009_7 VALUES LESS THAN(TO_DATE('01/08/2009','DD/MM/YYYY')),  PARTITION SALES_2009_8 VALUES LESS THAN(TO_DATE('01/09/2009','DD/MM/YYYY')),  PARTITION SALES_2009_9 VALUES LESS THAN(TO_DATE('01/10/2009','DD/MM/YYYY')),  PARTITION SALES_2009_10 VALUES LESS THAN(TO_DATE('01/11/2009','DD/MM/YYYY')),  PARTITION SALES_2009_11 VALUES LESS THAN(TO_DATE('01/12/2009','DD/MM/YYYY')),  PARTITION SALES_2009_12 VALUES LESS THAN(TO_DATE('01/01/2010','DD/MM/YYYY')),  PARTITION SALES_2010_1 VALUES LESS THAN(TO_DATE('01/02/2010','DD/MM/YYYY'))  );

這樣就可以非常方便地來管理每個月的數據了。例如，要刪除1年前的數據，只需要寫一個JOB來做定時檢查，如果發現有分區是1年以前的，那麼就可以直接將它刪除，這樣的操作對用戶是不可見的。

例如，要刪除2009年第1個月的分區數據，就可以使用這樣的命令：ALTER TABLE SALE_DATA DROP PARTITION SALES_2009_1;。

2、HASH（哈希）分區

HASH分區有如下的優點：

（1） 提高了大表的高可用性和可管理性。

（2） 可以避免數據傾斜，將數據均勻地分布在多個物理設備上，最大化I/O吞吐量。

（3） 分區修剪和分區智慧連接。

（4） 要求分區鍵是高基數列。

（5） 分區修剪不支援基於分區欄位的範圍查詢，只支援等值查詢或IN-LIST查詢。

HASH分區能夠很容易對數據進行分區，因為語法很簡單，很容易實現。在下面這種情況下，使用HASH分區比RANGE分區更好：

（1） 事先不知道需要將多少數據映射到給定範圍的時候。

（2） 分區的範圍大小很難確定，或者很難平衡的時候。

（3） RANGE分區使數據得到不期望的聚集時。

（4） 性能特性，如並行DML、分區修剪和分區連接很重要的時候。

下面的示例創建了一個HASH分區表：

CREATE TABLE T_HASH PARTITION BY HASH(OBJECT_ID) PARTITIONS 8 AS SELECT * FROM DBA_OBJECTS;

上面的SQL語句創建了一個8個分區的表T_HASH，分區類型為哈希分區，按照Oracle官方的要求，分區的數量應該是2的N次方為合適，例如2，4，8…，這裡是8個分區。

從視圖DBA_TAB_PARTITIONS中可以查詢到分區的詳細情況：

  SELECT *    FROM   DBA_TAB_PARTITIONS D    WHERE  D.TABLE_OWNER NOT IN ('SYS', 'SYSTEM') AND D.TABLE_NAME='T_HASH'    ORDER  BY D.TABLE_NAME, D.PARTITION_POSITION, D.PARTITION_NAME;  SELECT PARTITION_NAME, COUNT(*)  FROM   USER_EXTENTS  WHERE  SEGMENT_NAME = 'T_HASH'  GROUP  BY PARTITION_NAME;  上面是每個分區物理上佔用的EXTENTS數。  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P21)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P22)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P23)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P24)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P25)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P26)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P27)  UNION ALL  SELECT COUNT(*)  FROM   T_HASH PARTITION(SYS_P28);

在上面的例子中，不論是從EXTENT佔用的空間數量，還是分區中的數據量，都可以看出，數據在各個分區上分布是非常均勻的。

3、列表分區

列表分區（LIST PARTITION）提供了一種按照欄位的值來進行分區的方法，這種方法非常適合於有高重複率欄位值的表。通過這種方法，可以非常方便地控制將某些特定的數值存放到一個分區。

列表分區有如下特點：

（1） 列表值是離散的。

（2） 列表值是無序的，例如：PARTITION PART_01 VALUES('A','E','F')等。

（3） 分區鍵僅能包含一個列。

（4） 列表值是低基數的。

下面的例子創建了一個列表分區：

CREATE TABLE SALES_LIST  (SALESMAN_ID NUMBER(5),  SALESMAN_NAME VARCHAR2(30),  SALES_STATE VARCHAR2(20),  SALES_AMOUNT NUMBER(10),  SALES_DATE DATE)  PARTITION BY LIST(SALES_STATE)  (PARTITION SALES_WEST VALUES('CALIFORNIA', 'HAWAII') ,  PARTITION SALES_EAST VALUES('NEW YORK', 'VIRGINIA', 'FLORIDA'),  PARTITION SALES_CENTRAL VALUES('TEXAS', 'ILLINOIS') ,  PARTITION SALES_OTHER VALUES(DEFAULT)  );

在欄位SALES_STATE上創建了列表分區，同時設定了這樣的對應關係：

SALES_WEST('CALIFORNIA','HAWAII')  SALES_EAST('NEWYORK','VIRGINIA', 'FLORIDA')  SALES_CENTRAL('TEXAS','ILLINOIS')

從分區的名字上就能猜出裡面的內容了，例如SALES_WEST（西部地區）包含了「CALIFORNIA」和「HAWAII」，這對於內容重複率很高，並且可能有一些共性的欄位，使用列表分區是有用處的，它有利於數據的管理，例如想導出或者備份西部地區的數據，可以直接操作SALES_WEST分區，非常方便。

4、複合分區

如果某表按照某列分區之後，仍然較大，或者是有一些其它的需求，那麼還可以通過在分區內再建子分區的方式將分區再分區，即複合分區的方式。

複合分區在Oracle 11g之前有兩種：RANGE-HASH與RANGE-LIST。需要注意的是其順序，根分區只能是RANGE分區，子分區可以是HASH分區或LIST分區，而Oracle 11g在複合分區功能這塊有所增強，又推出了RANGE-RANGE、LIST-RANGE、LIST-LIST和LIST-HASH，這就相當於除HASH外三種分區方式的笛卡爾形式都有了，即目前一共有6種分區，但根分區只能是RANGE分區或LIST分區。

在某些時候按照業務要求，上面的幾種分區也可以按照一定的目的創建組合分區，或者叫子分區。下面的例子創建了一個範圍-列表組合分區：

CREATE TABLE T_RANGE_LIST(ID INT,NAME VARCHAR2(100))  PARTITION BY RANGE(ID)  SUBPARTITION BY LIST(NAME)  (  PARTITION P1 VALUES LESS THAN(5)  (SUBPARTITION SP1 VALUES ('A,B,C'),  SUBPARTITION SP2 VALUES ('D,E,F')  ),  PARTITION P2 VALUES LESS THAN(10)  (  SUBPARTITION SP3 VALUES ('A,B,C'),  SUBPARTITION SP4 VALUES ('D,E,F')  ),  PARTITION P3 VALUES LESS THAN(15)  );  SELECT * FROM DBA_PART_TABLES D WHERE D.TABLE_NAME = 'T_RANGE_LIST';

對於海量數據的資料庫設計，分區的設計非常重要。例如，對於一個大表，應該採用哪種類型的分區，對於以後資料庫的性能和管理至關重要。

其實，範圍分區、HASH分區和列表分區這三種分區的特點都非常明顯，如下所示：

l 如果需要進行數據的過期化處理，那麼範圍分區基本上是唯一的選擇。

l 如果需要數據的均勻分布，那麼可以考慮使用HASH分區。

l 如果數據的值可以很好地對應於某個分區，那麼就可以考慮使用列表分區。

在上面的原則基礎上，再結合性能的影響因素，來最終確定使用哪種類型的分區。

如果選擇的分區不能確保各分區內數據量的基本平均，那麼這種分區方式有可能是不恰當的。比如對於RANGE分區，假設分了10個分區，而其中一個分區中的記錄數佔總記錄數的90%，其它9個分區只佔總記錄數的10%，則這個分區方式就起不到數據平衡的作用。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。

About Me：小麥苗

● 本文作者：小麥苗，只專註於資料庫的技術，更注重技術的運用

● 作者部落格地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列題目來源於作者的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步