把一個已存在資料的大表改成分割槽表

把一個已存在資料的大表改成分割槽表[@more@]

第一種（表不是太大）：

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

如果表中預期的資料量較大，通常都需要考慮使用分割槽表，確定使用分割槽表後，還要確定什麼型別的分割槽（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 操作涉及大量資料時的效能。

分割槽技術提高可用性：

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