ORACLE 分區表簡介

ORACLE-分區表

此文從以下幾個方面來整理關于分區表的概念及操作：

1.表空間及分區表的概念

2.表分區的具體作用

3.表分區的優缺點

4.表分區的幾種類型及操作方法

5.對表分區的維護性操作.

6.表的分區做過改變後索引問題

7.将一個非分區表轉換為分區表

(1) 表空間及分區表的概念

表空間：

　　是一個或多個資料檔案的集合，所有的資料對象都存放在指定的表空間中，但主要存放的是表， 是以稱作表空間。

分區表： 

       當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的性能就會下降，這時就應該考慮對表進行分區。表進行分區後，邏輯上表仍然是一張完整的表，隻是将表中的資料在實體上存放到多個“表空間”(實體檔案上)，這樣查詢資料時，不至于每次都掃描整張表而隻是從目前的分區查到所要的資料大大提高了資料查詢的速度。

(2).表分區的具體作用

Oracle的表分區功能通過改善可管理性、性能和可用性，進而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的性能大大提高。此外,分區還可以極大簡化常見的管理任務，分區是建構千兆位元組資料系統或超高可用性系統的關鍵工具。 分區功能能夠将表、索引或索引組織表進一步細分為段，這些資料庫對象的段叫做分區。每個分區有自己的名稱，還可以選擇自己的存儲特性。從資料庫管理者的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理者在管理分區後的對象時有相當大的靈活性。但是，從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 指令通路分區後的表時，無需任何修改。

 什麼時候使用分區表：

       1、表的大小超過2GB。

2、表中包含曆史資料，新的資料被增加到新的分區中。

(3).表分區的優缺點

表分區有以下優點： 

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

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

3、維護友善：如果表的某個分區出現故障，需要修複資料，隻修複該分區即可；

4、均衡I/O：可以把不同的分區映射到不同磁盤以平衡I/O，改善整個系統性能。

 缺點： 

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

(4).表分區的幾種類型及操作方法

一.範圍分區：

       範圍分區将資料基于範圍映射到每一個分區，這個範圍是你在建立分區時指定的分區鍵決定的。這種分區方式是最為常用的，并且分區鍵經常采用日期。舉個例子：你可能會将銷售資料按照月份進行分區。

當使用範圍分區時，請考慮以下幾個規則：

1、每一個分區都必須有一個VALUES LESS THAN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等于或者大于這個上限值的記錄都會被加入到下一個高一些的分區中。

2、所有分區，除了第一個，都會有一個隐式的下限值，這個值就是此分區的前一個分區的上限值。

3、在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不确定的值。這個值高于其它分區中的任何分區鍵的值，也可以了解為高于任何分區中指定的VALUE LESS THAN的值，同時包括空值。

例一：

假設有一個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  THAN (1000) TABLESPACE  Part1_tb, 

      PARTITION  part2 VALUES  LESS  THAN (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 

);

三.散列分區（也稱HASH分區）：

       這類分區是在列值上使用雜湊演算法，以确定将行放入哪個分區中。當列的值沒有合适的條件時，建議使用散列分區。

散列分區為通過指定分區編号來均勻分布資料的一種分區類型，因為通過在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 (tbs01,tbs02,tbs03,tbs04,tbs05,tbs06,tbs07,tbs08);

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'))  TABLESPACE  SPACE_NAME;

注意：增加一個分區的時候，增加的分區的條件必須大于現有分區的最大值，否則系統将提示ORA-14074 partition bound must collate higher than that of the last partition 錯誤。

以下代碼給SALES表的P3分區添加了一個P3SUB1子分區

ALTER  TABLE  SALES  MODIFY  PARTITION  P3  ADD SUBPARTITION P3SUB1 VALUES('COMPLETE')  TABLESPACE  SPACE_NAME;

二、删除分區 

以下代碼删除了SALES表中名為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;

五、拆分分區 

拆分分區将一個分區拆分兩個新分區，拆分後原來分區不再存在但是如果表存在PMAX分區那麼原來的分區還是可以存在的。注意不能對 HASH類型的分區進行拆分。

ALTER  TABLE  SALES  SBLIT  PARTITION P2  AT(TO_DATE('2003-02-01','YYYY-MM-DD'))  INTO  (PARTITION P21,PARTITION P22);

此拆分語句是将P2分區拆分為PARTITION P21與PARTITION P22兩個分區，日期小于2003-02-01的資料存于 P21分區中否則存于 P22分區中。

--包含PMAX分區的表拆分分區

ALTER TABLE  hs_his.HISHOLDSINFO  SPLIT  PARTITION  PMAX  AT(20100900)  INTO (partition P201008, partition PMAX);

此拆分語句是将 PMAX 分區拆分為P201008與  PMAX 兩個分區，資料小于20100900 的資料存于  P201008 分區中否則存于 PMAX 分區中。

六、接合分區(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;

6.表的分區做過改變後索引問題

注意，在維護分區的時候可能會對索引産生一定的影響，會引起分區表的全局索引無效，需要重建索引。

  對分區表做了維護操作後，必須檢查相關索引，如檢查hisdeliverx表的非分區索引：

   select owner,index_name,status from dba_indexes where table_name='HISDELIVERX';

  若有索引的狀态為unusable，則必須使用alter index index_name rebuild online;重建該索引。

一般建議在拆分分區、truncate 分區中的資料、删除分區等功作時最好在語句後面加上update indexes子句，

這樣索引為unusable狀态的可能性就會很低，如下語句：

ALTER TABLE  table_name  SPLIT  PARTITION  partition_name1  AT(20000)  INTO (

 partition partition_name2,partition partition_name3) update indexes;

alter table table_name truncate partition  partition_name update indexes;

alter table table_name drop partition partition_name update indexes;

7.将一個非分區表轉換為分區表

使用exchange partition方法

假設把hisdeliverx由非分區表改為分區表。

基本思路：hisdeliverx是(資料量上百萬條，列比較多)一個非分區表，此時建立一個與hisdeliverx同結構的分區表t_hisdeliverx，交換和維護資料，删除hisdeliverx表，将t_hisdeliverx更名為hisdeliverx。然後再重建hisdeliverx表上的索引。

--具體步驟：

--建立分區表（結構和非分區表hisdeliverx相同）

create table t_HISDELIVERX

(

  INIT_DATE        NUMBER(10) default to_number(to_char(sysdate,'yyyymmdd')) not null,

  SERIAL_NO        NUMBER(10) default 0 not null,

……

……

---交換資料（資料從非分區表到分區表）

SQL> alter table t_hisdeliverx exchange partition pmax with table hisdeliverx;

Table altered

SQL> drop table hisdeliverx;

---删除非分區表     

Table dropped

---将分區表重命名為原非分區表名     

SQL> alter table t_hisdeliverx rename to hisdeliverx;

Table altered

---檢查hideliverx表是否為分區表

select table_owner,table_name,partition_name from dba_tab_partitions a where a.table_owner='HS_HIS' and

a.table_name='HISDELIVERX';

注意，在做exchange partition操作前先對該表進行備份。