oracle表分區(qū)的幾種方法以及維護

1、表分區(qū)有以下優(yōu)點： 1、數(shù)據(jù)查詢：數(shù)據(jù)被存儲到多個文件上，減少了I/O負載，查詢速度提高。 2、數(shù)據(jù)修剪：保存歷史數(shù)據(jù)非常的理想。 3、備份：將大表的數(shù)據(jù)分成多個文件，方便備份和恢復。 4、并行性：可以同時向表中進行DML操作，并行性性能提高。 當表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下降，這時就應(yīng)該考慮對表進行分區(qū)。表進行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。 Oracle中提供了以下幾種表分區(qū)： 一、范圍分區(qū)：這種類型的分區(qū)是使用列的一組值，通常將該列成為分區(qū)鍵。 示例1

2、：假設(shè)有一個CUSTOMER表，表中有數(shù)據(jù)行，我們將此表通過CUSTOMER_ID進行分區(qū)，每個分區(qū)存儲行，我們將每個分區(qū)保存到單獨的表空間中，這樣數(shù)據(jù)文件就可以跨越多個物理磁盤。下面是創(chuàng)建表和分區(qū)的代碼，如下： CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(

3、1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN () TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN () TABLESPACE CUS_TS02 ) 在創(chuàng)建表進行分區(qū)時，表空間必須先存在，而且建議將不同的分區(qū)放入不同的表空間中。 示例2：假設(shè)有ORDER_ACTIVITIES表，每6個月對訂單進行清理，我們可以按月份對表進行分區(qū)，分區(qū)代碼如下： CREATE TABLE ORDER_ACTIVITIES ( ORDER_

4、ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE(01-MAY-2003,DD-MON-YYYY) TABLESPACE ORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(01-JUN-2003,DD-MON

5、-YYYY) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(01-JUL-2003,DD-MON-YYYY) TABLESPACE ORD_TS03 ) 二、列表分區(qū)：該分區(qū)的特點是某列的值只有幾個，基于這樣的特點我們可以采用列表分區(qū)。 示例1： CREATE TABLE PROBLEM_TICKETS ( PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR2(2000), CUSTOMER_ID NUMBER(7) NOT

6、 NULL, DATE_ENTERED DATE NOT NULL, STATUS VARCHAR2(20) ) PARTITION BY LIST (STATUS) ( PARTITION PROB_ACTIVE VALUES (ACTIVE) TABLESPACE PROB_TS01, PARTITION PROB_INACTIVE VALUES (INACTIVE) TABLESPACE PROB_TS02 ) 三、散列分區(qū)：這類分區(qū)是在列值上使用散列算法，以確定將行放入哪個分區(qū)中。當列的值沒有合適的條件時，建議使用散列分區(qū)。請看下列示例： 示例1： CREATE TABLE HASH_

7、TABLE ( COL NUMBER(8), INF VARCHAR2(100) ) PARTITION BY HASH (COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 ) 四、復合范圍列表分區(qū)：這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行列表分區(qū)，分區(qū)之中的分區(qū)被稱為子分區(qū)。 示例1： CREATE TABLE SALES ( PRODUCT_ID VARCHAR

8、2(5), SALES_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) ( PARTITION P1 VALUES LESS THAN (TO_DATE(2003-01-01,YYYY-MM-DD) TABLESPACE P1_TS ( SUBPARTITION P1SUB1 VALUES (ACTIVE) TABLESPACE SUBP1_TS1, SUBPARTITION P1SUB2 VALUES (IN

9、ACTIVE) TABLESPACE SUBP1_TS2 ), PARTITION P2 VALUES LESS THAN (TO_DATE(2003-03-01,YYYY-MM-DD) TABLESPACE P2_TS ( SUBPARTITION P2SUB1 VALUES (ACTIVE) TABLESPACE SUBP2_TS1, SUBPARTITION P2SUB2 VALUES (INACTIVE) TABLESPACE SUBP2_TS2 ) ) 示例2：使用TEMPLATE模板 CREATE TABLE SALES ( PRODUCT_ID VARCHAR2(5), SALE

10、S_DATE DATE, SALES_COST NUMBER(10), STATUS VARCHAR2(20) ) PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) SUBPARTITION TEMPLATE ( SUBPARTITION SUB1 VALUES (ACTIVE) TABLESPACE SUBP1_TS1, SUBPARTITION SUB2 VALUES (INACTIVE) TABLESPACE SUBP2_TS2 ) ( PARTITION P1 VALUES LESS THAN (TO_DATE(2

11、003-01-01,YYYY-MM-DD) TABLESPACE P1_TS, PARTITION P2 VALUES LESS THAN (TO_DATE(2003-03-01,YYYY-MM-DD) TABLESPACE P2_TS ) 五、復合范圍散列分區(qū)：這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行散列分區(qū)。與上面的定義方式非常的類似，在此不單獨舉例。 表分區(qū)對于用戶來說是透明的，在插入數(shù)據(jù)時Oracle會自動判斷插入的數(shù)據(jù)，然后放入相應(yīng)的表分區(qū)中。但有時想單獨查詢某個分區(qū)中的數(shù)據(jù)時，就必須手工指定分區(qū)的名稱。 示例1：(此示例基于：四、復合范圍列表分區(qū)

12、的示例一) 向SALES表插入記錄，不必指定表分區(qū)。 INSERT INTO SALES VALUES(00001,01-1月-02,100,ACTIVE) / INSERT INTO SALES VALUES(00002,01-1月-01,200,ACTIVE) / INSERT INTO SALES VALUES(00003,01-2月-03,300,INACTIVE) / INSERT INTO SALES VALUES(00004,04-2月-03,300,INACTIVE) / INSERT INTO SALES VALUES(00005,04-2月-02,300,INACTIVE)

13、 / 不指定表分區(qū)查看SALES表信息： SELECT * FROM SALES; 結(jié)果如下所示： 指定P1表分區(qū)查詢SALES表信息： SELECT * FROM SALES PARTITION(P1); 結(jié)果如下所示： 指定P1SUB1子分區(qū)查詢SALES表信息: SELECT * FROM SALES SUBPARTITION(P1SUB1); 結(jié)果如下所示： 示例2：(此示例基于：四、復合范圍列表分區(qū)的示例二) 示例2基于TEMPLATE模板的表分區(qū)，查詢稍稍煩瑣一點。 指定P1表分區(qū)查詢SALES表信息： SELECT * FROM SALES PARTITION(P1); 結(jié)果如下

14、所示,和剛才查詢一致。 指定SUB1子分區(qū)查詢SALES表信息: SELECT * FROM SALES SUBPARTITION(SUB1); 出現(xiàn)如下錯誤信息： 怎么解決以上問題呢？我們通過sys模式查看分區(qū)信息的數(shù)據(jù)字典,如下： 可以看出子分區(qū)不叫SUB1，而是P1_SUB1,重新查詢信息，如下圖所示： 有關(guān)表分區(qū)的一些維護性操作： 一、添加分區(qū) 以下代碼給SALES表添加了一個P3分區(qū) ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE(2003-06-01,YYYY-MM-DD); 注意：以上添加的分區(qū)界限應(yīng)該高于最

15、后一個分區(qū)界限。 以下代碼給SALES表的P3分區(qū)添加了一個P3SUB1子分區(qū) ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES(COMPLETE); 二、刪除分區(qū) 以下代碼刪除了P3表分區(qū)： ALTER TABLE SALES DROP PARTITION P3; 在以下代碼刪除了P4SUB1子分區(qū)： ALTER TABLE SALES DROP SUBPARTITION P4SUB1; 注意：如果刪除的分區(qū)是表中唯一的分區(qū)，那么此分區(qū)將不能被刪除，要想刪除此分區(qū)，必須刪除表。 三、截斷分區(qū) 截斷某個分區(qū)

16、是指刪除某個分區(qū)中的數(shù)據(jù)，并不會刪除分區(qū)，也不會刪除其它分區(qū)中的數(shù)據(jù)。當表中即使只有一個分區(qū)時，也可以截斷該分區(qū)。通過以下代碼截斷分區(qū)： ALTER TABLE SALES TRUNCATE PARTITION P2; 通過以下代碼截斷子分區(qū)： ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 四、合并分區(qū) 合并分區(qū)是將相鄰的分區(qū)合并成一個分區(qū)，結(jié)果分區(qū)將采用較高分區(qū)的界限，值得注意的是，不能將分區(qū)合并到界限較低的分區(qū)。以下代碼實現(xiàn)了P1 P2分區(qū)的合并： ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO

17、PARTITION P2; 五、拆分分區(qū) 拆分分區(qū)將一個分區(qū)拆分兩個新分區(qū)，拆分后原來分區(qū)不再存在。注意不能對HASH類型的分區(qū)進行拆分。 ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE(2003-02-01,YYYY-MM-DD) INTO (PARTITION P21,PARTITION P22); 六、接合分區(qū)(coalesca) 結(jié)合分區(qū)是將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中，當散列分區(qū)中的數(shù)據(jù)比較大時，可以增加散列分區(qū)，然后進行接合，值得注意的是，接合分區(qū)只能用于散列分區(qū)中。通過以下代碼進行接合分區(qū)： ALTER TABLE SALES

18、COALESCA PARTITION; 七、重命名表分區(qū) 以下代碼將P21更改為P2 ALTER TABLE SALES RENAME PARTITION P21 TO P2; 九、跨分區(qū)查詢 select sum( *) from ( (select count(*) cn from t_table_SS PARTITION (P_1) union all select count(*) cn from t_table_SS PARTITION (P_2); 十、查詢表上有多少分區(qū) SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME=tabl

19、eName 十一、查詢索引信息 select object_name,object_type,tablespace_name,sum(value) from v$segment_statistics where statistic_name IN (physical reads,physical write,logical reads)and object_type=INDEX group by object_name,object_type,tablespace_name order by 4 desc -顯示數(shù)據(jù)庫所有分區(qū)表的信息： select * from DBA_PART_TABLE

20、S -顯示當前用戶可訪問的所有分區(qū)表信息: select * from ALL_PART_TABLES -顯示當前用戶所有分區(qū)表的信息： select * from USER_PART_TABLES -顯示表分區(qū)信息 顯示數(shù)據(jù)庫所有分區(qū)表的詳細分區(qū)信息： select * from DBA_TAB_PARTITIONS -顯示當前用戶可訪問的所有分區(qū)表的詳細分區(qū)信息： select * from ALL_TAB_PARTITIONS -顯示當前用戶所有分區(qū)表的詳細分區(qū)信息： select * from USER_TAB_PARTITIONS -顯示子分區(qū)信息 顯示數(shù)據(jù)庫所有組合分區(qū)表的子分區(qū)信息： select * from DBA_TAB_SUB