oracle表分割槽詳解

此文從以下幾個方面來整理關於分割槽表的概念及操作:

        1.表空間及分割槽表的概念

        2.表分割槽的具體作用

        3.表分割槽的優缺點

        4.表分割槽的幾種型別及操作方法

        5.對錶分割槽的維護性操作.

(1.) 表空間及分割槽表的概念

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

 

分割槽表：
當表中的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時就應該考慮對錶進行分割槽。表進行分割槽後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個表空間(物理檔案上)，這樣查詢資料時，不至於每次都掃描整張表。

 

( 2).表分割槽的具體作用

Oracle的表分割槽功能透過改善可管理性、效能和可用性，從而為各式應用程式帶來了極大的好處。通常，分割槽可以使某些查詢以及維護操作的效能大大提高。此外,分割槽還可以極大簡化常見的管理任務，分割槽是構建千兆位元組資料系統或超高可用性系統的關鍵工具。

 

分割槽功能能夠將表、索引或索引組織表進一步細分為段，這些資料庫物件的段叫做分割槽。每個分割槽有自己的名稱，還可以選擇自己的儲存特性。從資料庫管理員的角度來看，一個分割槽後的物件具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分割槽後的物件時有相當大的靈活性。但是，從應用程式的角度來看，分割槽後的表與非分割槽表完全相同，使用 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;