ORACLE分區表發揮效能

標籤：blog   http   os   io   使用   ar   for   資料   art   

1.1 分區表PARTITION table

在ORACLE裡如果遇到特別大的表，可以使用分區的表來改變其應用程式的效能。

1.1.1 分區表的建立：

某公司的每年產生巨大的銷售記錄，DBA向公司建議每季度的資料放在一個分區內，以下示範的是該公司1999年的資料(假設每月產生30M的資料)，操作如下：

定界分割表：

CREATE TABLE sales

(invoice_no NUMBER,

...

sale_date DATE NOT NULL )

PARTITION BY RANGE (sale_date)

(PARTITION sales1999_q1

VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q1,

PARTITION sales1999_q2

VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q2,

PARTITION sales1999_q3

VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q3,

PARTITION sales1999_q4

VALUES LESS THAN (TO_DATE(‘2000-01-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale1999q4 );

--values less than (maxvalue)

列表分區表：

create table emp (

empno number(4),

ename varchar2(30),

location varchar2(30))

partition by list (location)

(partition p1 values (‘北京‘),

partition p2 values (‘上海‘,‘天津‘,‘重慶‘),

partition p3 values (‘廣東‘,‘福建‘)

partition p0 values (default)

);

雜湊分割：

create table emp (

empno number(4),

ename varchar2(30),

sal number)

partition by hash (empno)

partitions 8

store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

組合分區：

範圍雜湊組合分區：

create table emp (

empno number(4),

ename varchar2(30),

hiredate date)

partition by range (hiredate)

subpartition by hash (empno)

subpartitions 2

(partition e1 values less than (to_date(‘20020501‘,‘YYYYMMDD‘)),

partition e2 values less than (to_date(‘20021001‘,‘YYYYMMDD‘)),

partition e3 values less than (maxvalue));

範圍列表組合分區：

CREATE TABLE customers_part (

customer_id NUMBER(6),

cust_first_name VARCHAR2(20),

cust_last_name VARCHAR2(20),

nls_territory VARCHAR2(30),

credit_limit NUMBER(9,2))

PARTITION BY RANGE (credit_limit)

SUBPARTITION BY LIST (nls_territory)

SUBPARTITION TEMPLATE

(SUBPARTITION east VALUES (‘CHINA‘, ‘JAPAN‘, ‘INDIA‘, ‘THAILAND‘),

SUBPARTITION west VALUES (‘AMERICA‘, ‘GERMANY‘, ‘ITALY‘, ‘SWITZERLAND‘),

SUBPARTITION other VALUES (DEFAULT))

(PARTITION p1 VALUES LESS THAN (1000),

PARTITION p2 VALUES LESS THAN (2500),

PARTITION p3 VALUES LESS THAN (MAXVALUE));

 

create table t1 (id1 number,id2 number)

partition by range (id1) subpartition by list (id2)

(partition p11 values less than (11)

(subpartition subp1 values (1))

);

索引分割區：

CREATE INDEX month_ix ON sales(sales_month) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION pm1_ix VALUES LESS THAN (2) PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

1.1.2 分區表的維護：

增加分區：

ALTER TABLE sales ADD PARTITION sales2000_q1

VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’)

TABLESPACE ts_sale2000q1;

如果已有maxvalue分區，不能增加分區，可以採取分裂分區的辦法增加分區！

刪除分區：

ALTER TABLE sales DROP PARTION sales1999_q1;

截短分區:

alter table sales truncate partiton sales1999_q2;

合并分區：

alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;

alter index ind_t2 rebuild partition p123 parallel 2;

分裂分區：

ALTER TABLE sales

SPLIT PARTITON sales1999_q4

AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’)

INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) ;

alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);

交換分區:

alter table x exchange partition p0 with table bsvcbusrundatald ;

訪問指定分區：

select * from sales partition(sales1999_q2)

EXPORT指定分區：

exp sales/sales_password tables=sales:sales1999_q1

file=sales1999_q1.dmp

IMPORT指定分區：

imp sales/sales_password FILE =sales1999_q1.dmp

TABLES = (sales:sales1999_q1) IGNORE=y

查看分區資訊：

user_tab_partitions, user_segments

 

註：若分區表跨不同資料表空間，做匯出、匯入時目標資料庫必須預建這些資料表空間。分表區各區所在資料表空間在做匯入時目標資料庫一定要預建這些資料表空間！這些資料表空間不一定是使用者的預設資料表空間，只要存在即可。如果有一個不存在，就會報錯！

預設時，對分區表的許多表維護操作會使全域索引不可用，標記成UNUSABLE。 那麼就必須重建整個全域索引或其全部分區。如果已被分區，Oracle 允許在用於維護操作的ALTER TABLE 語句中指定UPDATE GLOBAL INDEXES 來重載這個預設特性，指定這個子句也就告訴Oracle 當它執行維護操作的DDL 語句時更新全域索引，這提供了如下好處： 1.在操作基礎資料表的同時更新全域索引這就不需要後來單獨地重建全域索引； 2.因為沒有被標記成UNUSABLE， 所以全域索引的可用性更高了，甚至正在執行分區的DDL 語句時仍然可用索引來訪問表中的其他分區,避免了查詢所有失效的全域索引的名字以便重建它們； 另外在指定UPDATE GLOBAL INDEXES 之前還要考慮如下效能因素: 1.因為要更新事先被標記成UNUSABLE 的索引，所以分區的DDL 語句要執行更長時間，當然這要與先不更新索引而執行DDL 然後再重建索引所花的時間做個比較，一個適用的規則是如果分區的大小小於表的大小的5% ，則更新索引更快一點； 2.DROP TRUNCATE 和EXCHANGE 操作也不那麼快了，同樣這必須與先執行DDL 然後再重建所有全域索引所花的時間做個比較； 3.要登記對索引的更新併產生重做記錄和撤消記錄，重建整個索引時可選擇NOLOGGING； 4.重建整個索引產生一個更有效索引，因為這更利於使用空間，再者重建索引時允許修改儲存選項。 注意分區索引結構表不支援UPDATE GLOBAL INDEXES 子句。

1.1.3 普通表變為分區表

 

將已存在資料的普通錶轉變為分區表，沒有辦法通過修改屬性的方式直接轉化為分區表，必須通過重建的方式進行轉變，一般可以有三種方法，視不同情境使用：

用例：

方法一：利用原表重建分區表。

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);  INSERT INTO T SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000; COMMIT;

CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)  (PARTITION P1 VALUES LESS THAN (TO_DATE(‘2000-1-1‘, ‘YYYY-MM-DD‘)),  PARTITION P2 VALUES LESS THAN (TO_DATE(‘2002-1-1‘, ‘YYYY-MM-DD‘)),  PARTITION P3 VALUES LESS THAN (TO_DATE(‘2005-1-1‘, ‘YYYY-MM-DD‘)),  PARTITION P4 VALUES LESS THAN (MAXVALUE)) AS SELECT ID, TIME FROM T;

 

RENAME T TO T_OLD;

RENAME T_NEW TO T;

SELECT COUNT(*) FROM T;

COUNT(*) ---------- 5000

SELECT COUNT(*) FROM T PARTITION (P1);

COUNT(*) ---------- 2946

SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*) ---------- 731

SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*) ---------- 1096

優點：方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後資料已經在分布到各個分區中了。

不足：對於資料的一致性方面還需要額外的考慮。由於幾乎沒有辦法通過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對資料進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。

適用於修改不頻繁的表，在閑時進行操作，表的資料量不宜太大。

 

方法二：使用交換分區的方法。

Drop table t; CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);  INSERT INTO T SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000; COMMIT;

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)  (PARTITION P1 VALUES LESS THAN (TO_DATE(‘2005-9-1‘, ‘YYYY-MM-DD‘)),  PARTITION P2 VALUES LESS THAN (MAXVALUE));

 

ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

RENAME T TO T_OLD;

RENAME T_NEW TO T;

 

優點：只是對資料字典中分區和表的定義進行了修改，沒有資料的修改或複製，效率最高。如果對資料在分區中的分布沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在資料，如果存在的話，直接將這些資料插入到T中，可以保證對T插入的操作不會丟失。

不足：仍然存在一致性問題，交換分區之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到資料。如果要求資料分布到多個分區中，則需要進行分區的SPLIT操作，會增加操作的複雜度，效率也會降低。

適用於包含大資料量的錶轉到分區表中的一個分區的操作。應盡量在閑時進行操作。

 

方法三：Oracle9i以上版本，利用線上重定義功能

Drop table t; CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);  INSERT INTO T SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000; COMMIT;

 

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, ‘T‘);

PL/SQL 過程已成功完成。

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)  (PARTITION P1 VALUES LESS THAN (TO_DATE(‘2004-7-1‘, ‘YYYY-MM-DD‘)),  PARTITION P2 VALUES LESS THAN (TO_DATE(‘2005-1-1‘, ‘YYYY-MM-DD‘)),  PARTITION P3 VALUES LESS THAN (TO_DATE(‘2005-7-1‘, ‘YYYY-MM-DD‘)),  PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已建立。

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, ‘T‘, ‘T_NEW‘);

PL/SQL 過程已成功完成。

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, ‘T‘, ‘T_NEW‘);

PL/SQL 過程已成功完成。

SELECT COUNT(*) FROM T;

COUNT(*) ---------- 5000

SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*) ---------- 1096

優點：保證資料的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

不足：實現上比上面兩種略顯複雜。

適用於各種情況。

這裡只給出了線上重定義表的一個最簡單的例子，詳細的描述和例子可以參考下面兩篇文章。

Oracle的線上重定義表功能：http://blog.itpub.net/post/468/12855

Oracle的線上重定義表功能（二）：http://blog.itpub.net/post/468/12962

 

XSB:

把一個已存在資料的大表改成分區表：

第一種（表不是太大）：

1.把原表改名： rename xsb1 to xsb2; 2.建立分區表： CREATE TABLE xsb1 PARTITION BY LIST (c_test)  (PARTITION xsb1_p1 VALUES (1), PARTITION xsb1_p2 VALUES (2), PARTITION xsb1_p0 VALUES (default)) nologging AS SELECT * FROM xsb2; 3.將原表上的觸發器、主鍵、索引等應用到分區表上； 4.刪除原表： drop table xsb2;

第二種(表很大)：

1. 建立分區表： CREATE TABLE x PARTITION BY LIST (c_test) [range ()] (PARTITION p0 VALUES [less than ](1) tablespace tbs1, PARTITION p2 VALUES (2) tablespace tbs1, PARTITION xsb1_p0 VALUES ([maxvalue]default)) AS SELECT * FROM xsb2 [where 1=2];

2. 交換分區 alter table x exchange partition p0 with table bsvcbusrundatald ;

3. 原表改名alter table bsvcbusrundatald rename to x0;

4. 新表改名alter table x rename to bsvcbusrundatald ;

5. 刪除原表drop table x0;

6. 建立新表觸發器和索引create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ;

 

或者：

1. 規劃原大表中資料分區的界限，原則上將原表中近期少量資料複製至另一表；

2. 暫停原大表中的相關觸發器；

3. 刪除原大表中近期資料；

4. 改名原大表名稱；

5. 建立分區表；

6. 交換分區；

7. 重建相關索引及觸發器（先刪除之再重建）.

參考指令碼：

select count(*) from t1 where recdate>sysdate-2

create table x2 nologging as select * from t1 where recdate>trunc(sysdate-2)

alter triger trg_t1 disable

delete t1 where recdate>sysdate-2

commit

rename t1 to x1

create table t1 [nologging] partition by range(recdate)

(partition pbefore values less than (trunc(sysdate-2)),

partition pmax values less than (maxvalue))

as select * from x1 where 1=2

alter table t1 exchange partition pbefore with table x1

alter table t1 exchange partition pmax with table x2

drop table x2

[重建觸發器]

drop table x1

 

1.1.4 參考材料：

如果表中預期的資料量較大，通常都需要考慮使用分區表，確定使用分區表後，還要確定什麼類型的分區（range partition、hash partition、list partition等）、分區區間大小等。分區的建立最好與程式有某種默契，偶曾經建立分區表，按自然月份定義分區的，但程式卻在查詢時預設的開始時間與結束時間是：當前日期-30至當前日期，比如當天是9.18號，那查詢條件被產生為8.18-9.18，結果分區後並不沒有大幅提高效能，後來對程式的查詢日期做了調整，按自然月查詢，系統的負載小了很多。

從Oracle8.0開始支援表分區（MSSQL2005開始支援表分區）。

Oracle9i 分區能夠提高許多應用程式的可管理性、效能與可用性。分區可以將表、索引及索引編排表進一步劃分，從而可以更精細地對這些資料庫物件進行管理和訪問。Oracle 提供了種類繁多的資料分割配置以滿足所有的業務需要。另外，由於在 SQL 陳述式中是完全透明的，所以分區可以用於幾乎所有的應用程式。

分區表允許將資料分成被稱為分區甚至子分區的更小的更好管理的塊。索引也可以這麼分區。每個分區可以被單獨管理，可以不依賴於其他分區而單獨發揮作用，因此提供了一個更有利於可用性和效能的結構。

分區可以提高可管理性、效能與可用性，從而給各種各樣的應用程式帶來極大的好處。通常，分區可以使某些查詢以及維護操作的效能大大提高。此外，分區還能夠在很大程度上簡化日常管理工作。分區還使資料庫設計人員和管理員能夠解決尖端應用程式帶來的最難的問題。分區是建立上億萬位元組資料系統或需要極高可用性系統的關鍵工具。

在多CPU配置環境下，如果打算使用並存執行，則分區提供了另一種並行的方法。通過給表或索引的不同分區分配不同的並存執行伺服器，就可以並存執行對分區表和分區索引的操作。

表或索引的分區和子分區都共用相同的邏輯屬性。例如表的所有分區或子分區共用相同的列和約束定義，一個索引的分區或子分區共用相同的索引選項。然而它們可以具有不同的物理屬性如資料表空間。

儘管不需要將表或索引的每個分區或子分區放在不同的資料表空間，但這樣做更好。將分區儲存到不同的資料表空間能夠

l 減少資料在多個分區中衝突的可能性

l 可以單獨備份和恢複每個分區

l 控制分區與磁碟機之間的映射對平衡I/O 負載是重要的

l 改善可管理性可用性和效能

分區操作對現存的應用和運行在分區表上的標準DML 語句來說是透明的。但是可以通過在DML 中使用分區擴充表或索引的名字來對應用編程，使其利用分區的優點。

可以使用SQL*Loader、Import 和Export 工具來裝載或卸載分區表中的資料。這些工具都是支援分區和子分區的。

 

分區的方法

Oracle9i 提供了如下5種分區方法：

l 定界分割Range

l 散列分區Hash

l 列表分區List

l 組合範圍-散列分區Range-Hash

l 組合範圍-列表分區Range-List

可對索引和表分區。全域索引只能定界分割，但可以將其定義在任何類型的分區或非分區表上。通常全域索引比局部索引需要更多的維護。

一般組建局部索引，以便反映其基礎資料表的結構。它與基礎資料表是等同分區的，即它與基礎

表在同樣的列上分區，建立同樣數量的分區或子分區，設定與基礎資料表相對應的同樣的分區邊界。對局部索引而言，當維護活動影響分區時，會自動維護索引分割區。這保證了索引與基礎資料表之間的等同分區。

關於定界分割Range：

要想將行映射到基於列值範圍的分區，就使用定界分割方法。當資料可以被劃分成邏輯範圍時如年度中的月份，這種類型的分區就有用了。當資料在整個範圍中能被均等地劃分時效能最好。如果靠範圍的分區會由於不均等的劃分而導致分區在大小上明顯不同時，就需要考慮其他的分區方法。

關於散列分區Hash：

如果資料不那麼容易進行定界分割，但為了效能和管理的原因又想分區時，就使用散列分區方法。散列分區提供了一種在指定數量的分區中均等地劃分資料的方法。基於分區鍵的散列值將行映射到分區中。建立和使用散列分區會給你提供了一種很靈活的放置資料的方法，因為你可以通過在I/O 磁碟機之間播撒(摘掉)這些均等定量的分區，來影響可用性和效能。

關於列表分區List：

當你需要明確地控制如何將行映射到分區時，就使用列表分區方法。可以在每個分區的描述中為該分區列指定一列離散值，這不同於定界分割，在那裡一個範圍與一個分區相關，這也不同於散列分區，在那裡使用者不能控制如何將行映射到分區。列表分區方法是特意為遵從離散值的模組化資料劃分而設計的。定界分割或散列分區不那麼容易做到這一點。進一步說列表分區可以非常自然地將無序的和不相關的資料集進行分組和組織到一起。

與定界分割和散列分區所不同，列表分區不支援多列分區。如果要將表按列分區，那麼分區鍵就只能由表的一個單獨的列組成，然而可以用定界分割或散列分區方法進行分區的所有的列,都可以用列表分區方法進行分區。

關於組合範圍-散列分區：

範圍和散列技術的組合，首先對錶進行定界分割，然後用散列技術對每個定界分割再次分區。給定的定界分割的所有子分區加在一起表示資料的邏輯子集。

關於組合範圍-列表分區：

範圍和列表技術的組合，首先對錶進行定界分割，然後用列表技術對每個定界分割再次分區。與組合範圍-散列分區不同的是，每個子分區的所有內容表示資料的邏輯子集，由適當的範圍和列表分區設定來描述。

建立或更改分區表時可以指定行移動子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，當其鍵被更改時，該子句啟用或停用將行遷移到一個新的分區。預設值為DISABLE ROW MOVEMENT。本產品(項目)使用ENABLE ROW MOVEMENT子句。

 

分區技術能夠提高資料庫的可管理性：

使用分區技術，維護操作可集中於表的特定部分。例如，資料庫管理員可以只對錶的一部分做備份，而不必對整個表做備份。對整個資料庫物件的維護操作，可以在每個分區的基礎上進行，從而將維護工作分解成更容易管理的小塊。

分區技術提高可管理性的一個典型用法是支援資料倉儲中的‘滾動視窗’載入進程。假設資料庫管理員每周向表中載入新資料。該表可以是定界分割，以便每個分區包含一周的資料。載入進程只是簡單地添加新的分區。添加一個新分區的操作比修改整個表效率高很多，因為資料庫管理員不需要修改任何其他分區。從分區後的表中去除資料也是一樣。你只要用一個很簡便快捷的資料字典操作刪掉一個分區，而不必發出使用大量資源和調動所有要刪除的資料的 ‘DELETE’ 命令。

分區技術能夠提高資料庫的效能:

由於減少了所檢查或操作的資料數量，同時允許並存執行，Oracle9i 的資料分割函數提供了效能上的優勢。這些效能包括：

l 分區修整：分區修整是用分區技術提高效能的最簡單最有價值的手段。分區修整常常能夠將查詢效能提高几個數量級。例如，假定應用程式中有包含定單記錄的定單表，該表用周進行了分區。查詢一周的定單只需訪問該定單表的一個分區。如果該定單表包含兩年的記錄，這個查詢只需要訪問一個而不是一百零四個分區。該查詢的執行速度因為分區修整而有可能快一百倍。分區修整能與所有其他 Oracle 效能特性協作。Oracle 公司將把分區修整技術與索引技術、連結技術和並行存取方法一起聯合使用。

l 分區智能聯結：資料分割函數可以通過稱為分區智能聯結的技術提高多表聯結的效能。當兩個表要聯結在一起，而且每個表都用聯結關鍵字來分區時，就可以使用分區智能聯結。分區智能聯結將大型聯結分解成較小的發生在各個分區間的聯結，從而用較少的時間完成全部聯結。這就給串列和並行的執行都能帶來顯著的效能改善。

l 更新和刪除的並存執行：資料分割函數能夠無限地並存執行 UPDATE、DELETE 與 MERGE 語句。當訪問分區或未分區的資料庫物件時Oracle 將平行處理 SELECT 與 INSERT 語句。當不使用位元影像索引時，也可以對分區或未分區的資料庫物件平行處理 UPDATE、DELETE 和 MERGE 語句。為了對有位元影像索引的對象平行處理那些操作，目標表必須先分區。這些 SQL 陳述式的並存執行可以大大提高效能，特別是提高 UPDATE 與 DELETE 或 MERGE 操作涉及大量資料時的效能。

分區技術提高可用性：

分區的資料庫物件具有分區獨立性。該分區獨立性特點可能是高可用性戰略的一個重要部分，例如，如果分區表的分區不能用，但該表的所有其他分區仍然保持線上並可用。那麼這個應用程式可以繼續針對該分區表執行查詢和交易處理，只要不是訪問那個停用分區，資料庫操作仍然能夠成功運行。 資料庫管理員可以指定各分區存放在不同的資料表空間裡，從而讓管理員獨立於其它表分區針對每個分區進行備份與恢複操作。 還有，資料分割函數可以減少計劃停機時間。效能由於資料分割函數得到了改善，使資料庫管理員在相對較小的批處理視窗完成大型資料庫物件的維護工作。

ORACLE分區表發揮效能