1.1 分區(qū)表PARTITION table

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

1.1.1 分區(qū)表的建立：

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

范圍分區(qū)表：

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)

列表分區(qū)表：

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)

);

哈希分區(qū)：

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);

組合分區(qū)：

范圍哈希組合分區(qū)：

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));

范圍列表組合分區(qū)：

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))

);

索引分區(qū)：

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 分區(qū)表的維護：

增加分區(qū)：

ALTER TABLE sales ADD PARTITION sales2000_q1

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

TABLESPACE ts_sale2000q1;

如果已有maxvalue分區(qū)，不能增加分區(qū)，可以采取分裂分區(qū)的辦法增加分區(qū)！

刪除分區(qū)：

ALTER TABLE sales DROP PARTION sales1999_q1;

截短分區(qū):

alter table sales truncate partiton sales1999_q2;

合并分區(qū)：

alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;

alter index ind_t2 rebuild partition p123 parallel 2;

分裂分區(qū)：

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);

交換分區(qū):

alter table x exchange partition p0 with table bsvcbusrundatald ;

訪問指定分區(qū)：

select * from sales partition(sales1999_q2)

EXPORT指定分區(qū)：

exp sales/sales_password tables=sales:sales1999_q1

file=sales1999_q1.dmp

IMPORT指定分區(qū)：

imp sales/sales_password FILE =sales1999_q1.dmp

TABLES = (sales:sales1999_q1) IGNORE=y

查看分區(qū)信息：

user_tab_partitions, user_segments

注：若分區(qū)表跨不同表空間，做導出、導入時目標數據庫必須預建這些表空間。分表區(qū)各區(qū)所在表空間在做導入時目標數據庫一定要預建這些表空間！這些表空間不一定是用戶的默認表空間，只要存在即可。如果有一個不存在，就會報錯！

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