Oracle分割槽表的管理

分割槽表PARTITION table

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

1.1.1 分割槽表的建立：

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

範圍分割槽表：

CREATE TABLE sales

(invoice_no NUMBER,

...

[@more@]

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