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

同事的分區表總結，轉載一下。

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 操作涉及大量數據時的性能。

分區技術提高可用性：

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