資料庫最佳化-oracle表分區的建立和分類

標籤：oracle   資料庫   表分區   

當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個資料表空間(物理檔案上)，這樣查詢資料時，有可能不需要每次都掃描整張表。

 

Oracle的表資料分割函數通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的效能大大提高。此外,分區還可以極大簡化常見的管理工作，分區是構建千MB資料系統或超高可用性系統的關鍵工具。

 

資料分割函數能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分區。每個分區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改。

 

什麼時候使用分區表：
1、表的大小超過2GB或者表的行數有可能超過500萬。

2、對不同的資料需要進行不同的批量處理。

 

表分區的優缺點

表分區優點： 

1、改善效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高使用速度。

2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

3、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

4、均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能。

 

表分區缺點： 
1.如果使用不當，會降低效能；比如沒有指定分區的讀操作；

2.在重要資料場合，一個分區出錯，這個服務（事務）必須要停下來，比如支付系統；

3.使用分區後，同樣的資料，備份資料比沒有分區前要慢很多，體積要大很多；

4.如果使用不當，會降低輸送量

 

表分區的幾種類型及操作方法

一、定界分割：
定界分割將資料基於範圍映射到每一個分區，這個範圍是你在建立分區時指定的分區鍵決定的。這種分區方式是最為常用的，並且分區鍵經常採用日期。舉個例子：你可能會將銷售資料按照月份進行分區。

當使用定界分割時，請考慮以下幾個規則：

1、每一個分區都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分區中。

2、所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。

3、在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值，也可以理解為高於任何分區中指定的VALUE LESS THEN的值，同時包括空值。

例一：

假設有一個tel表，表中有資料2000萬行，我們將此表通過id進行分區，每個分區儲存1000萬行，我們將每個分區儲存到單獨的資料表空間中，這樣資料檔案就可以跨越多個物理磁碟。下面是建立表和分區的代碼，如下：

CREATE TABLE TEL
( 
    ID NUMBER NOT NULL PRIMARY KEY, 
    PHONE VARCHAR2(15) NOT NULL
) 
PARTITION BY RANGE (ID) 
( 
    PARTITION TEL_PART1 VALUES LESS THAN (100000000) TABLESPACE TEL_TS01, 
    PARTITION TEL_PART2 VALUES LESS THAN (200000000) TABLESPACETEL_TS02 
)

 

二.列表分區：

該分區的特點是某列的值只有幾個，基於這樣的特點我們可以採用列表分區。比如我們的tel表，這張表由上海，北京兩個地方主要使用，

create table TEL

(

  ID       INTEGER not null,

  PROV_REGION_CODE     INTEGER,

  CITY_REGION_CODE     INTEGER,

  SUB_CITY_REGION_CODE INTEGER,

  PHONE            VARCHAR2(16)

)

partition by list(PROV_REGION_CODE)

(

  partition PART11 values (110000),

  partition PART31 values (310000),

  partition PARTDFT values (default)

); 

這個分區建立在PROV_REGION_CODE欄位上。

partition PART11values (110000) 表示北京；

partition PART31values (310000) 表示上海；

partition PARTDFTvalues (default)，其他地方的資料，都放在這個預設分區。

110000 和310000 是中國國家規劃的省一級代碼。

 

 

三.散列分區：
這類分區是在列值上使用散列演算法，以確定將行放入哪個分區中。當列的值沒有合適的條件時，建議使用散列分區。

散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。

例一：

CREATE TABLE HASH_TEL 
( 
  COL NUMBER(8), 
  INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 
);

 

hash分區最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分區中,hash演算法中最重要的是hash函數，Oracle中如果你要使用hash分區，只需指定分區的數量即可。建議分區的數量採用2的n次方，這樣可以使得各個分區間資料分布更加均勻。

hash分區寫入的效率是非常不錯的，但是讀出來的效率是不樂觀的，因此要注意業務情境。

 

四.複合分區
複合分區是一種分區，嵌入另外一種分區，下面這個例子是基於定界分割和列表分區，表首先按某列進行定界分割，然後再按某列進行列表分區，分區之中的分區被稱為子分區。

CREATE TABLESALES

(

ID INTEGER,

SALES_DATEDATE,

STATUSVARCHAR2(20)

)

PARTITION BYRANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)

(

   PARTITION P1 VALUES LESSTHAN(TO_DATE(‘2012-01-01‘,‘YYYY-MM-DD‘))

   (

              SUBPARTITION P1SUB1 VALUES(‘ACTIVE‘),

              SUBPARTITION P1SUB2 VALUES(‘INACTIVE‘)),

   PARTITION P2 VALUES LESS THAN (TO_DATE(‘2013-01-01‘,‘YYYY-MM-DD‘))

          (

              SUBPARTITION P2SUB1 VALUES(‘ACTIVE‘),

              SUBPARTITION P2SUB2 VALUES(‘INACTIVE‘)

          )

) 

 

使用的時候必須謹慎，越複雜的對象，要把握好越難。

 

一些特殊的使用情境：

1.  利用表分區記錄日誌，一般日誌保留1年，一年以上的日誌刪除，如果要刪除資料並且釋放空間，如果不做特殊處理，一般需要停應用；這個時候可以利用表分區，一年建立一個分區，然後把到期的分區刪除就可以；

ALTER TABLE SALES TRUNCATE PARTITION P2;

如果一個重要的項目需要使用分區，那麼最好閱讀一些專業的書籍。比如“Oracle10g SQL和PL/SQL編程指南”之內。沒事看看總歸有收穫。

資料庫最佳化-oracle表分區的建立和分類