oracle表分區(qū)詳解

1、oracle表分區(qū)詳解從以下幾個方面來整理關(guān)于分區(qū)表的概念及操作:1. 表空間及分區(qū)表的概念2. 表分區(qū)的具體作用3. 表分區(qū)的優(yōu)缺點4. 表分區(qū)的幾種類型及操作方法5. 對表分區(qū)的維護性操作 1.表空間及分區(qū)表的概念表空間：是一個或多個數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。 分區(qū)表：當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下降，這時就應(yīng)該考慮對表進行分區(qū)。表進行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。&#

2、160;2.表分區(qū)的具體作用Oracle的表分區(qū)功能通過改善可管理性、性能和可用性，從而為各式應(yīng)用程序帶來了極大的好處。通常，分區(qū)可以使某些查詢以及維護操作的性能大大提高。此外,分區(qū)還可以極大簡化常見的管理任務(wù)，分區(qū)是構(gòu)建千兆字節(jié)數(shù)據(jù)系統(tǒng)或超高可用性系統(tǒng)的關(guān)鍵工具。 分區(qū)功能能夠?qū)⒈?、索引或索引組織表進一步細(xì)分為段，這些數(shù)據(jù)庫對象的段叫做分區(qū)。每個分區(qū)有自己的名稱，還可以選擇自己的存儲特性。從數(shù)據(jù)庫 管理員的角度來看，一個分區(qū)后的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使數(shù)據(jù)庫管理員在管理分區(qū)后的對象時有相當(dāng)大的靈活性。但 是，從應(yīng)用程序的角度來看，分區(qū)后的表與非分

3、區(qū)表完全相同，使用 SQL DML 命令訪問分區(qū)后的表時，無需任何修改。 什么時候使用分區(qū)表：1)   表的大小超過2GB。2)   表中包含歷史數(shù)據(jù)，新的數(shù)據(jù)被增加都新的分區(qū)中。 3.表分區(qū)的優(yōu)缺點優(yōu)點：1)   改善查詢性能：對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。2)   增強可用性：如果表的某個分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用；3)   維護方便：如果表的某個分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可；4)   均衡I/O

4、：可以把不同的分區(qū)映射到磁盤以平衡I/O，改善整個系統(tǒng)性能。 缺點：分區(qū)表相關(guān)，已經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過 Oracle 提供了在線重定義表的功能。 4.表分區(qū)的幾種類型及操作方法1.范圍分區(qū)范圍分區(qū)將數(shù)據(jù)基于范圍映射到每一個分區(qū)，這個范圍是你在創(chuàng)建分區(qū)時指定的分區(qū)鍵決定的。這種分區(qū)方式是最為常用的，并且分區(qū)鍵經(jīng)常采用日期。舉個例子：你可能會將銷售數(shù)據(jù)按照月份進行分區(qū)。當(dāng)使用范圍分區(qū)時，請考慮以下幾個規(guī)則：1)   每一個分區(qū)都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區(qū)中的上限值。分區(qū)鍵的任何值等于或者

5、大于這個上限值的記錄都會被加入到下一個高一些的分區(qū)中。2)   所有分區(qū)，除了第一個，都會有一個隱式的下限值，這個值就是此分區(qū)的前一個分區(qū)的上限值。3)   在最高的分區(qū)中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高于其它分區(qū)中的任何分區(qū)鍵的值，也可以理解為高于任何分區(qū)中指定的VALUE LESS THEN的值，同時包括空值。例1：假設(shè)有一個CUSTOMER表，表中有數(shù)據(jù)200000行，我們將此表通過CUSTOMER_ID進行分區(qū)，每個分區(qū)存儲100000行，我們將每個分區(qū)保存到單獨的表空間中，這樣數(shù)據(jù)文件就可以跨越多個物理磁

6、盤。下面是創(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(1)PARTITION BY RANGE (CUSTOMER_ID)( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPA

7、CE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02) 例2：按時間劃分CREATE TABLE ORDER_ACTIVITIES(ORDER_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 THA

8、N (TO_DATE('01- MAY -2003','DD-MON-YYYY') TABLESPACEORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY') TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY') TABLESPAC

9、E ORD_TS03) 例3：MAXVALUECREATE TABLE RangeTable( idd INT PRIMARY KEY , iNAME VARCHAR(10), grade INT )PARTITION BY RANGE (grade)( PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb, PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb); 2.列表分區(qū)：該分區(qū)的特點是某列的值只有幾個，基于這樣的特點我們可以采

10、用列表分區(qū)。例1CREATE TABLE PROBLEM_TICKETS( PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR2(2000), CUSTOMER_ID NUMBER(7) NOT NULL, DATE_ENTERED DATE NOT NULL, STATUS VARCHAR2(20)PARTITION BY LIST (STATUS)( PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01, PARTITION PROB

11、_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02 例2CREATE TABLE ListTable( id INT PRIMARY KEY , name VARCHAR (20), area VARCHAR (10)PARTITION BY LIST (area)( PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb, PARTITION part2 VALUES ('shanghai',

12、'nanjing') TABLESPACE Part2_tb);)  3.散列分區(qū):這類分區(qū)是在列值上使用散列算法，以確定將行放入哪個分區(qū)中。當(dāng)列的值沒有合適的條件時，建議使用散列分區(qū)。散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型，因為通過在I/O設(shè)備上進行散列分區(qū)，使得這些分區(qū)大小一致。例1：CREATE TABLE HASH_TABLE( COL NUMBER(8), INF VARCHAR2(100)PARTITION BY HASH (COL)( PARTITION PART01 TABLESPACE HASH_TS01, PARTITIO

13、N PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03) 簡寫：CREATE TABLE emp( empno NUMBER (4), ename VARCHAR2 (30), sal NUMBER)PARTITION BY HASH (empno) PARTITIONS 8STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);  hash分區(qū)最主要的機制是根據(jù)hash算法來計算具體某條紀(jì)錄應(yīng)該插入到哪個分區(qū)中,hash算法中最重要的是

14、hash函數(shù)，Oracle中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使得各個分區(qū)間數(shù)據(jù)分布更加均勻。 4.組合范圍散列分區(qū)這種分區(qū)是基于范圍分區(qū)和列表分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行列表分區(qū)，分區(qū)之中的分區(qū)被稱為子分區(qū)。CREATE TABLE SALES(PRODUCT_ID VARCHAR2(5),SALES_DATE DATE,SALES_COST NUMBER(10),STATUS VARCHAR2(20)PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (S

15、TATUS)( PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD')TABLESPACE rptfact2009 ( SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 ), PARTITION P2 VALUES LESS THAN (TO_DATE('

16、;2003-03-01','YYYY-MM-DD') TABLESPACE rptfact2009 ( SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 )  5.復(fù)合范圍散列分區(qū)：這種分區(qū)是基于范圍分區(qū)和散列分區(qū)，表首先按某列進行范圍分區(qū)，然后再按某列進行散列分區(qū)。create table dinya_test ( tr

17、ansaction_id number primary key, item_id number(8) not null, item_description varchar2(300), transaction_date date ) partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) ( partition part_01 values less than(to_d

18、ate(2006-01-01,yyyy-mm-dd), partition part_02 values less than(to_date(2010-01-01,yyyy-mm-dd), partition part_03 values less than(maxvalue) );  5.有關(guān)表分區(qū)的一些維護性操作1)   添加分區(qū)以下代碼給SALES表添加了一個P3分區(qū)ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-M

19、M-DD');  注意：以上添加的分區(qū)界限應(yīng)該高于最后一個分區(qū)界限。以下代碼給SALES表的P3分區(qū)添加了一個P3SUB1子分區(qū)ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 2)   刪除分區(qū)以下代碼刪除了P3表分區(qū)：ALTER TABLE SALES DROP PARTITION P3; 在以下代碼刪除了P4SUB1子分區(qū)：ALTER TABLE SALES DROP SUBPARTITION P4SU

20、B1; 注意：如果刪除的分區(qū)是表中唯一的分區(qū)，那么此分區(qū)將不能被刪除，要想刪除此分區(qū)，必須刪除表。3)   截斷分區(qū)截斷某個分區(qū)是指刪除某個分區(qū)中的數(shù)據(jù)，并不會刪除分區(qū)，也不會刪除其它分區(qū)中的數(shù)據(jù)。當(dāng)表中即使只有一個分區(qū)時，也可以截斷該分區(qū)。通過以下代碼截斷分區(qū)：ALTER TABLE SALES TRUNCATE PARTITION P2; 通過以下代碼截斷子分區(qū)：ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 4)   合并分區(qū)合并分區(qū)是將相鄰的分區(qū)合并成一個分區(qū)，結(jié)果分區(qū)

21、將采用較高分區(qū)的界限，值得注意的是，不能將分區(qū)合并到界限較低的分區(qū)。以下代碼實現(xiàn)了P1 P2分區(qū)的合并：ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2; 5)   拆分分區(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,PARTI

22、TION P22);  6)   接合分區(qū)(coalesca)結(jié)合分區(qū)是將散列分區(qū)中的數(shù)據(jù)接合到其它分區(qū)中，當(dāng)散列分區(qū)中的數(shù)據(jù)比較大時，可以增加散列分區(qū)，然后進行接合，值得注意的是，接合分區(qū)只能用于散列分區(qū)中。通過以下代碼進行接合分區(qū)：ALTER TABLE SALES COALESCA PARTITION; 7)   重命名表分區(qū)以下代碼將P21更改為P2ALTER TABLE SALES RENAME PARTITION P21 TO P2; 8)   相關(guān)查詢跨分區(qū)查詢select sum( *)

23、from(select count(*) cn from t_table_SS PARTITION (P200709_1)union allselect count(*) cn from t_table_SS PARTITION (P200709_2);  查詢表上有多少分區(qū)SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'  查詢索引信息select object_name,object_type,tablespace_name,sum(value)from v$segment_st

24、atisticswhere statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'group by object_name,object_type,tablespace_nameorder by 4 desc   -顯示數(shù)據(jù)庫所有分區(qū)表的信息：select * from DBA_PART_TABLES  -顯示當(dāng)前用戶可訪問的所有分區(qū)表信息:select * from ALL_PART_TABLES  -顯示當(dāng)前用戶所有分區(qū)表的信息： select * from USER_PART_TABLES  -顯示表分區(qū)信息 顯示數(shù)據(jù)庫所有分區(qū)表的詳細(xì)分區(qū)信息：select * from DBA_TAB_PARTITIONS  -顯示當(dāng)前用戶可訪問的所有分區(qū)表的詳細(xì)分區(qū)信息：select * from ALL_TAB_PARTITIONS  -顯示當(dāng)前用戶所有分區(qū)表的詳細(xì)分區(qū)信息：select *