oracle表分區詳解__oracle

一、Oracle分區簡介

ORACLE的分區是一種處理超大型表、索引等的技術。分區是一種“分而治之”的技術，通過將大表和索引分成可以管理的小塊，從而避免了對每個表作為一個大的、單獨的對象進行管理，為大量資料提供了可伸縮的效能。分區通過將操作分配給更小的儲存單元，減少了需要進行管理操作的時間，並通過增強平行處理提高了效能，通過屏蔽故障資料的分區，還增加了可用性。

二、Oracle分區優缺點

優點：

增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；

維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可；

均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能；

改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。

缺點：

分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle提供了線上重定義表的功能。

三、Oracle分區方法

定界分割：

定界分割就是對資料表中的某個值的範圍進行分區，根據某個值的範圍，決定將該資料存放區在哪個分區上。如根據序號分區，根據業務記錄的建立日期進行分區等。

Hash分區（散列分區）：

散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。

List分區（列表分區）：

當你需要明確地控制如何將行映射到分區時，就使用列表分區方法。與定界分割和散列分區所不同，列表分區不支援多列分區。如果要將表按列分區，那麼分區鍵就只能由表的一個單獨的列組成，然而可以用定界分割或散列分區方法進行分區的所有的列,都可以用列表分區方法進行分區。

範圍-散列分區（複合分區）：

有時候我們需要根據定界分割後，每個分區內的資料再散列地分布在幾個資料表空間中，這樣我們就要使用複合分區。複合分區是先使用定界分割，然後在每個分區內再使用散列分區的一種分區方法（注意：先一定要進行定界分割）

範圍-列表分區（複合分區）：

範圍和列表技術的組合，首先對錶進行定界分割，然後用列表技術對每個定界分割再次分區。與組合範圍-散列分區不同的是，每個子分區的所有內容表示資料的邏輯子集，由適當的範圍和列表分區設定來描述。（注意：先一定要進行定界分割）

-------------------------------

Oracle表分區和索引分割區匯總

為了簡化資料庫大表的管理,例如在資料倉儲中一般都是TB級的數量級.ORACLE8以後推出了分區選項.分區將表分離在若於不同的資料表空間上,用分而治之的方法來支撐元限膨脹的大表,組大表在物理一級的可管理性.將大表分割成較小的分區可以改善表的維護、備份、恢複、事務及查詢效能。

分區的優點：

1、  增強可用性：如果表的一個分區由於系統故障而不能使用，表的其餘好的分區仍可以使用；

2、  減少關閉時間：如果系統故障隻影響表的一部份分區，那麼只有這部份分區需要修複，礦能比整個大表修複花的時間更少；

3、  維護輕鬆：如果需要得建表，獨產管理每個公區比管理單個大表要輕鬆得多；

4、  均衡I/O：可以把表的不同分區分配到不同的磁碟來平衡I/O改善效能；

5、  改善效能：對大表的查詢、增加、修改等操作可以分解到表的不同分區來並存執行，可使運行速度更快，在資料倉儲的TP查詢特別有用。

6、  分區對使用者透明，終端使用者感覺不到分區的存在。

 

--------------------------------- 此文從以下幾個方面來整理關於分區表的概念及操作:         1.資料表空間及分區表的概念         2.表分區的具體作用         3.表分區的優缺點         4.表分區的幾種類型及操作方法         5.對錶分區的維護性操作. (1.) 資料表空間及分區表的概念 資料表空間：
　　是一個或多個資料檔案的集合，所有的資料對象都存放在指定的資料表空間中，但主要存放的是表， 所以稱作資料表空間。 分區表：
當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個資料表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表。 ( 2).表分區的具體作用 Oracle的表資料分割函數通過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的效能大大提高。此外,分區還可以極大簡化常見的管理工作，分區是構建千MB資料系統或超高可用性系統的關鍵工具。 資料分割函數能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分區。每個分區有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。但是，從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改。 什麼時候使用分區表：
1、表的大小超過2GB。 2、表中包含曆史資料，新的資料被增加都新的分區中。 (3).表分區的優缺點 表分區有以下優點：
1、改善查詢效能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。 2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用； 3、維護方便：如果表的某個分區出現故障，需要修複資料，只修複該分區即可； 4、均衡I/O：可以把不同的分區映射到磁碟以平衡I/O，改善整個系統效能。 缺點：
分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。 (4).表分區的幾種類型及操作方法 一.定界分割：
定界分割將資料基於範圍映射到每一個分區，這個範圍是你在建立分區時指定的分區鍵決定的。這種分區方式是最為常用的，並且分區鍵經常採用日期。舉個例子：你可能會將銷售資料按照月份進行分區。 當使用定界分割時，請考慮以下幾個規則： 1、每一個分區都必須有一個VALUES LESS THEN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分區中。 2、所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。 3、在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值，也可以理解為高於任何分區中指定的VALUE LESS THEN的值，同時包括空值。 例一： 假設有一個CUSTOMER表，表中有資料200000行，我們將此表通過CUSTOMER_ID進行分區，每個分區儲存100000行，我們將每個分區儲存到單獨的資料表空間中，這樣資料檔案就可以跨越多個物理磁碟。下面是建立表和分區的代碼，如下： 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) TABLESPACE CUS_TS01,
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
) 例二：按時間劃分 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 THAN (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')) TABLESPACE ORD_TS03 ) 例三：MAXVALUE CREATE 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
); 二.列表分區： 該分區的特點是某列的值只有幾個，基於這樣的特點我們可以採用列表分區。 例一 CREATE 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_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02 例二 CREATE  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','nanjing')  TABLESPACE  Part2_tb
); ) 三.散列分區：
這類分區是在列值上使用散列演算法，以確定將行放入哪個分區中。當列的值沒有合適的條件時，建議使用散列分區。 散列分區為通過指定分區編號來均勻分布資料的一種分區類型，因為通過在I/O裝置上進行散列分區，使得這些分區大小一致。 例一： CREATE TABLE HASH_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
) 簡寫： 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); hash分區最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分區中,hash演算法中最重要的是hash函數，Oracle中如果你要使用hash分區，只需指定分區的數量即可。建議分區的數量採用2的n次方，這樣可以使得各個分區間資料分布更加均勻。 四.組合範圍散列分區
這種分區是基於定界分割和列表分區，表首先按某列進行定界分割，然後再按某列進行列表分區，分區之中的分區被稱為子分區。 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 (STATUS) (    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('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
          (
              SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
              SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
          )
) 五.複合範圍散列分區： 這種分區是基於定界分割和散列分區，表首先按某列進行定界分割，然後再按某列進行散列分區。 create table dinya_test
(
transaction_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_date(‘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).有關表分區的一些維護性操作：
一、添加分區
以下代碼給SALES表添加了一個P3分區 ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD')); 注意：以上添加的分區界限應該高於最後一個分區界限。 以下代碼給SALES表的P3分區添加了一個P3SUB1子分區 ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 二、刪除分區
以下代碼刪除了P3表分區： ALTER TABLE SALES DROP PARTITION P3; 在以下代碼刪除了P4SUB1子分區： ALTER TABLE SALES DROP SUBPARTITION P4SUB1; 注意：如果刪除的分區是表中唯一的分區，那麼此分區將不能被刪除，要想刪除此分區，必須刪除表。 三、截斷分區
截斷某個分區是指刪除某個分區中的資料，並不會刪除分區，也不會刪除其它分區中的資料。當表中即使只有一個分區時，也可以截斷該分區。通過以下代碼截斷分區： ALTER TABLE SALES TRUNCATE PARTITION P2; 通過以下代碼截斷子分區： ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 四、合并分區
合并分區是將相鄰的分區合并成一個分區，結果分區將採用較高分區的界限，值得注意的是，不能將分區合并到界限較低的分區。以下代碼實現了P1 P2分區的合并： ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2; 五、拆分分區
拆分分區將一個分區拆分兩個新分區，拆分後原來分區不再存在。注意不能對HASH類型的分區進行拆分。 ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22); 六、接合分區(coalesca)
結合分區是將散列分區中的資料接合到其它分區中，當散列分區中的資料比較大時，可以增加散列分區，然後進行接合，值得注意的是，接合分區只能用於散列分區中。通過以下代碼進行接合分區： ALTER TABLE SALES COALESCA PARTITION; 七、重新命名表分區
以下代碼將P21更改為P2 ALTER TABLE SALES RENAME PARTITION P21 TO P2; 八、相關查詢
跨分區查詢 select sum( *) from (select count(*) cn from t_table_SS PARTITION (P200709_1) union all select count(*) cn from t_table_SS PARTITION (P200709_2) ); 查詢表上有多少分區 SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName' 查詢索引資訊 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 --顯示資料庫所有分區表的資訊： select * from DBA_PART_TABLES --顯示目前使用者可訪問的所有分區表資訊: select * from ALL_PART_TABLES --顯示目前使用者所有分區表的資訊： select * from USER_PART_TABLES --顯示表分區資訊 顯示資料庫所有分區表的詳細分區資訊： select * from DBA_TAB_PARTITIONS --顯示目前使用者可訪問的所有分區表的詳細分區資訊： select * from ALL_TAB_PARTITIONS --顯示目前使用者所有分區表的詳細分區資訊： select * from USER_TAB_PARTITIONS --顯示子分區資訊 顯示資料庫所有組合分區表的子分區資訊： select * from DBA_TAB_SUBPARTITIONS --顯示目前使用者可訪問的所有組合分區表的子分區資訊： select * from ALL_TAB_SUBPARTITIONS --顯示目前使用者所有組合分區表的子分區資訊： select * from USER_TAB_SUBPARTITIONS --顯示分區列 顯示資料庫所有分區表的分區列資訊： select * from DBA_PART_KEY_COLUMNS --顯示目前使用者可訪問的所有分區表的分區列資訊： select * from ALL_PART_KEY_COLUMNS --顯示目前使用者所有分區表的分區列資訊： select * from USER_PART_KEY_COLUMNS --顯示子分區列 顯示資料庫所有分區表的子分區列資訊： select * from DBA_SUBPART_KEY_COLUMNS --顯示目前使用者可訪問的所有分區表的子分區列資訊： select * from ALL_SUBPART_KEY_COLUMNS --顯示目前使用者所有分區表的子分區列資訊： select * from USER_SUBPART_KEY_COLUMNS --怎樣查詢出oracle資料庫中所有的的分區表 select * from user_tables a where a.partitioned='YES' --刪除一個表的資料是 truncate table table_name; --刪除分區表一個分區的資料是 alter table table_name truncate partition p5;

 

Oracle表分區實戰： http://love-flying-snow.iteye.com/blog/573303