轉載來源地址：http://blog.csdn.net/delphi99/article/details/6913505

此文從以下幾個方面來整理關於分割槽表的概念及操作:
        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;

一. 分割槽表理論知識

Oracle提供了分割槽技術以支援VLDB(Very Large DataBase)。分割槽表透過對分割槽列的判斷，把分割槽列不同的記錄，放到不同的分割槽中。分割槽完全對應用透明。

Oracle的分割槽表可以包括多個分割槽，每個分割槽都是一個獨立的段（SEGMENT），可以存放到不同的表空間中。查詢時可以透過查詢表來訪問各個分割槽中的資料，也可以透過在查詢時直接指定分割槽的方法來進行查詢。

When to Partition a Table什麼時候需要分割槽表，官網的2個建議如下：

（1）Tables greater than 2GB should always be considered for partitioning.

（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

在oracle 10g中最多支援：1024k-1個分割槽：

Tables can be partitioned into up to 1024K-1 separate partitions

聯機文件上有關分割槽表和索引的說明：

Partitioned Tables and Indexes

分割槽提供以下優點：

（1）由於將資料分散到各個分割槽中，減少了資料損壞的可能性；

（2）可以對單獨的分割槽進行備份和恢復；

（3）可以將分割槽對映到不同的物理磁碟上，來分散IO；

（4）提高可管理性、可用性和效能。

Oracle 10g提供了以下幾種分割槽型別：

（1）範圍分割槽（range）；

（2）雜湊分割槽（hash）；

（3）列表分割槽（list）；

（4）範圍－雜湊複合分割槽（range-hash）；

（5）範圍－列表複合分割槽（range-list）。

Range分割槽：

　　Range分割槽是應用範圍比較廣的表分割槽方式，它是以列的值的範圍來做為分割槽的劃分條件，將記錄存放到列值所在的range分割槽中。

如按照時間劃分，2010年1月的資料放到a分割槽，2月的資料放到b分割槽，在建立的時候，需要指定基於的列，以及分割槽的範圍值。

在按時間分割槽時，如果某些記錄暫無法預測範圍，可以建立maxvalue分割槽，所有不在指定範圍內的記錄都會被儲存到maxvalue所在分割槽中。

如：

create table pdba (id number, time date) partition by range (time)

(

partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 values less than (maxvalue)

)

Hash分割槽：

　　對於那些無法有效劃分範圍的表，可以使用hash分割槽，這樣對於提高效能還是會有一定的幫助。hash分割槽會將表中的資料平均分配到你指定的幾個分割槽中，列所在分割槽是依據分割槽列的hash值自動分配，因此你並不能控制也不知道哪條記錄會被放到哪個分割槽中，hash分割槽也可以支援多個依賴列。

如：

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

在這裡，我們指定了每個分割槽的表空間。

List分割槽：

　　List分割槽也需要指定列的值，其分割槽值必須明確指定，該分割槽列只能有一個，不能像range或者hash分割槽那樣同時指定多個列做為分割槽依賴列，但它的單個分割槽對應值可以是多個。

　　在分割槽時必須確定分割槽列可能存在的值，一旦插入的列值不在分割槽範圍內，則插入/更新就會失敗，因此通常建議使用list分割槽時，要建立一個default分割槽儲存那些不在指定範圍內的記錄，類似range分割槽中的maxvalue分割槽。

在根據某欄位，如城市程式碼分割槽時，可以指定default，把非分割槽規則的資料，全部放到這個default分割槽。

如：

create table custaddr
(

id varchar2(15 byte) not null,

areacode varchar2(4 byte)
)

partition by list (areacode)
( partition t_list025 values ('025'),
partition t_list372 values ('372') ,
partition t_list510 values ('510'),

partition p_other values (default)

)

組合分割槽：

如果某表按照某列分割槽之後，仍然較大，或者是一些其它的需求，還可以透過分割槽內再建子分割槽的方式將分割槽再分割槽，即組合分割槽的方式。

　　組合分割槽呢在10g中有兩種：range-hash，range-list。注意順序，根分割槽只能是range分割槽，子分割槽可以是hash分割槽或list分割槽。

如：

create table test

(

transaction_id number primary key,

transaction_date date

)

partition by range(transaction_date) subpartition by hash(transaction_id)

subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

(

partition part_01 values less than(to_date(’2009-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)

);

create table emp_sub_template (deptno number, empname varchar(32), grade number)

partition by range(deptno) subpartition by hash(empname)

subpartition template

(subpartition a tablespace ts1,

subpartition b tablespace ts2,

subpartition c tablespace ts3,

subpartition d tablespace ts4

)

(partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (maxvalue)

);

create table quarterly_regional_sales

(deptno number, item_no varchar2(20),

txn_date date, txn_amount number, state varchar2(2))

tablespace ts4

partition by range (txn_date)

subpartition by list (state)

(partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))

(subpartition q1_1999_northwest values ('or', 'wa'),

subpartition q1_1999_southwest values ('az', 'ut', 'nm'),

subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q1_1999_southeast values ('fl', 'ga'),

subpartition q1_1999_northcentral values ('sd', 'wi'),

subpartition q1_1999_southcentral values ('ok', 'tx')

),

partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))

(subpartition q2_1999_northwest values ('or', 'wa'),

subpartition q2_1999_southwest values ('az', 'ut', 'nm'),

subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q2_1999_southeast values ('fl', 'ga'),

subpartition q2_1999_northcentral values ('sd', 'wi'),

subpartition q2_1999_southcentral values ('ok', 'tx')

),

partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))

(subpartition q3_1999_northwest values ('or', 'wa'),

subpartition q3_1999_southwest values ('az', 'ut', 'nm'),

subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q3_1999_southeast values ('fl', 'ga'),

subpartition q3_1999_northcentral values ('sd', 'wi'),

subpartition q3_1999_southcentral values ('ok', 'tx')

),

partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))

(subpartition q4_1999_northwest values ('or', 'wa'),

subpartition q4_1999_southwest values ('az', 'ut', 'nm'),

subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),

subpartition q4_1999_southeast values ('fl', 'ga'),

subpartition q4_1999_northcentral values ('sd', 'wi'),

subpartition q4_1999_southcentral values ('ok', 'tx')

)

);

在Oracle 11g中，組合分割槽功能這塊有所增強，又增加了range-range,list-range,

list-list,list-hash，並且 11g裡面還支援Interval分割槽和虛擬列分割槽。

這塊可以參考Blog：

Oracle 11g 新特性簡介

http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx

分割槽表之 Interval分割槽和虛擬列按星期分割槽表

http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx

二. 普通錶轉分割槽表方法

將普通錶轉換成分割槽表有4種方法：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

具體參考：

How to Partition a Non-partitioned Table [ID 1070693.6]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

邏輯匯出匯入這裡就不做說明，我們看看其他三種方法。

2.1 插入： Insert with a subquery method

這種方法就是使用insert 來實現。當然在建立分割槽表的時候可以一起插入資料，也可以建立好後在insert 進去。這種方法採用DDL語句，不產生UNDO，只產生少量REDO，建表完成後資料已經在分佈到各個分割槽中。

SQL> select count(*) from dba;

COUNT(*)

----------

2713235

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select time_fee from dba where rownum<5;

TIME_FEE

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

2011-02-17 19:29:09

2011-02-17 19:29:15

2011-02-17 19:29:18

2011-02-17 19:29:20

SQL>

2.1.1 Oracle 11g的Interval

在11g裡的Interval建立，這種方法對沒有寫全的分割槽會自動建立。比如我這裡只寫了1月日期，如果插入的資料有其他月份的，會自動生成對應的分割槽。

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */

CREATE TABLE intervaldave

PARTITION BY RANGE (time_fee)

INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )

(PARTITION part1

VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))

AS

SELECT ID, TIME_FEE FROM DAVE;

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE';

TABLE_NAME PARTITION_NAME

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

INTERVALDAVE PART1

INTERVALDAVE SYS_P24

INTERVALDAVE SYS_P25

INTERVALDAVE SYS_P26

INTERVALDAVE SYS_P33

INTERVALDAVE SYS_P27

INTERVALDAVE SYS_P28

2.1.2 Oracle 10g 版本

在10g裡面，我需要寫全所有的分割槽。

sql> create table pdba (id, time) partition by range (time)

2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

3 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

4 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

5 partition p4 values less than (maxvalue))

6 as select id, time_fee from dba;

表已建立。

SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA';

TABLE_NAME PARTITION_NAME

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

PDBA P1

PDBA P2

PDBA P3

PDBA P4

sql> select count(*) from pdba partition (p1);

count(*)

----------

1718285

sql> select count(*) from pdba partition (p2);

count(*)

----------

183667

sql> select count(*) from pdba partition (p3);

count(*)

----------

188701

sql> select count(*) from pdba partition (p4);

count(*)

----------

622582

sql>

現在分割槽表已經建好了，但是表名不一樣，需要用rename對錶重新命名一下：

SQL> rename dba to dba_old;

表已重新命名。

SQL> rename pdba to dba;

表已重新命名。

SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA';

TABLE_NAME PARTITION_NAME

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

DBA P1

DBA P2

DBA P3

DBA P4

2.2 . 交換分割槽：Partition exchange method

這種方法只是對資料字典中分割槽和表的定義進行了修改，沒有資料的修改或複製，效率最高。適用於包含大資料量的錶轉到分割槽表中的一個分割槽的操作。儘量在閒時進行操作。

交換分割槽的操作步驟如下：

1. 建立分割槽表，假設有2個分割槽，P1，P2.

2. 建立表A存放P1規則的資料。

3. 建立表B 存放P2規則的資料。

4. 用表A 和P1 分割槽交換。把表A的資料放到到P1分割槽

5. 用表B 和p2 分割槽交換。把表B的資料存放到P2分割槽。

建立分割槽表：

sql> create table p_dba

2 (id number,time date)

3 partition by range(time)

4 (

5 partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')),

6 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd'))

7 );

表已建立。

注意：我這裡只建立了2個分割槽，沒有建立存放其他資料的分割槽。

建立2個分別對應分割槽的基表：

SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE time_fee

表已建立。

SQL> CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE time_feeTO_DATE('2010-09-1', 'YYYY-MM-DD');

表已建立。

SQL> select count(*) from dba_p1;

COUNT(*)

----------

1536020

SQL> select count(*) from dba_p2;

COUNT(*)

----------

365932

SQL>

講2個基表與2個分割槽進行交換：

SQL> alter table p_dba exchange partition p1 with table dba_p1;

表已更改。

SQL> alter table p_dba exchange partition p2 with table dba_p2;

表已更改。

查詢2個分割槽：

SQL> select count(*) from p_dba partition(p1);

COUNT(*)

----------

1536020

SQL> select count(*) from p_dba partition(p2);

COUNT(*)

----------

365932

注意：資料和之前的基表一致。

查詢原來的2個基表：

SQL> select count(*) from dba_p2;

COUNT(*)

----------

0

SQL> select count(*) from dba_p1;

COUNT(*)

----------

0

注意： 2個基表的資料變成成0。

在這裡我們看一個問題，一般情況下，我們在建立分割槽表的時候，都會有一個其他分割槽，用來存放不匹配分割槽規則的資料。在這個例子中，我只建立了2個分割槽，沒有建立maxvalue分割槽。現在我來插入一條不滿足規則的資料，看結果：

SQL> insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'));

insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd'))

*

第 1 行出現錯誤:

ORA-14400: 插入的分割槽關鍵字未對映到任何分割槽

SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd'));

已建立 1 行。

SQL> select * from p_dba where id=999999;

ID TIME

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

999999 29-12月-09

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select * from p_dba where id=999999;

ID TIME

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

999999 2009-12-29 00:00:00

SQL>

透過這個測試可以清楚，如果插入的資料不滿足分割槽規則，會報ORA-14400錯誤。

2.3 . 使用線上重定義：DBMS_REDEFINITION

線上重定義能保證資料的一致性，在大部分時間內，表都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

關於DBMS_REDEFINITION的介紹，參考官方連線：

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBFDJBC

關於用線上重定義建立分割槽表，參考：

How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218693.aspx

這個功能只在9.2.0.4以後的版本才有，線上重定義表具有以下功能：

（1）修改表的儲存引數；

（2）將錶轉移到其他表空間；

（3）增加並行查詢選項；

（4）增加或刪除分割槽；

（5）重建表以減少碎片；

（6）將堆表改為索引組織表或相反的操作；

（7）增加或刪除一個列。

使用線上重定義的一些限制條件：

（1） There must be enough space to hold two copies of the table.

（2） Primary key columns cannot be modified.

（3） Tables must have primary keys.

（4） Redefinition must be done within the same schema.

（5） New columns added cannot be made NOT NULL until after the redefinition operation.

（6） Tables cannot contain LONGs, BFILEs or User Defined Types.

（7） Clustered tables cannot be redefined.

（8） Tables in the SYS or SYSTEM schema cannot be redefined.

（9） Tables with materialized view logs or materialized views defined on them cannot be redefined.

（10） Horizontal sub setting of data cannot be performed during the redefinition.

在Oracle 10.2.0.4和11.1.0.7 版本下，線上重定義可能會遇到如下bug：

Bug 7007594 - ORA-600 [12261]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

線上重定義的大致操作流程如下：

（1）建立基礎表A，如果存在，就不需要操作。

（2）建立臨時的分割槽表B。

（3）開始重定義，將基表A的資料匯入臨時分割槽表B。

（4）結束重定義，此時在DB的 Name Directory裡，已經將2個表進行了交換。即此時基表A成了分割槽表，我們建立的臨時分割槽表B 成了普通表。此時我們可以刪除我們建立的臨時表B。它已經是普通表。

下面看一個示例：

1. 建立基本表和索引

sql> conn icd/icd;

已連線。

sql> create table unpar_table (

2 id number(10) primary key,

3 create_date date

4 );

表已建立。

sql> insert into unpar_table select rownum, created from dba_objects;

已建立72288行。

sql> create index create_date_ind on unpar_table(create_date);

索引已建立。

sql> commit;

提交完成。

2. 收集表的統計資訊

sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);

pl/sql 過程已成功完成。

3. 建立臨時分割槽表

sql> create table par_table (id number primary key, time date) partition by range (time)

2 (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')),

3 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),

4 partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')),

5 partition p4 values less than (maxvalue));

表已建立。

4. 進行重定義操作

4.1 檢查重定義的合理性

sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');

pl/sql 過程已成功完成。

4.2 如果4.1 沒有問題，開始重定義，這個過程可能要等一會。

這裡要注意：如果分割槽表和原表列名相同，可以用如下方式進行：

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => 'ICD',

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

如果分割槽表的列名和原表不一致，那麼在開始重定義的時候，需要重新指定對映關係：

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(

'ICD',

'unpar_table',

'par_table',

'ID ID, create_date TIME', -- 在這裡指定新的對映關係

DBMS_REDEFINITION.CONS_USE_PK);

這一步操作結束後，資料就已經同步到這個臨時的分割槽表裡來了。

4.3 同步新表，這是可選的操作

SQL> BEGIN

2 dbms_redefinition.sync_interim_table(

3 uname => 'ICD',

4 orig_table => 'unpar_table',

5 int_table => 'par_table');

6 END;

7 /

PL/SQL 過程已成功完成。

4.4 建立索引，線上重定義只重定義資料，索引還需要單獨建立。

sql> create index create_date_ind2 on par_table(time);

索引已建立。

4.5 收集新表的統計資訊

sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

pl/sql 過程已成功完成。

4.6 結束重定義

SQL> BEGIN

2 dbms_redefinition.finish_redef_table(

3 uname => 'ICD',

4 orig_table => 'unpar_table',

5 int_table => 'par_table');

6 END;

7 /

PL/SQL 過程已成功完成。

結束重定義的意義：

基表unpar_table 和臨時分割槽表par_table 進行了交換。此時臨時分割槽表par_table成了普通表，我們的基表unpar_table成了分割槽表。

我們在重定義的時候，基表unpar_table是可以進行DML操作的。只有在2個表進行切換的時候會有短暫的鎖表。

5. 刪除臨時表

SQL> DROP TABLE par_table;

表已刪除。

6. 索引重新命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

索引已更改。

7. 驗證

sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

par

---

yes

sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE';

partition_name

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

p1

p2

p3

p4

sql> select count(*) from unpar_table;

count(*)

----------

72288

sql> select count(*) from unpar_table partition (p4);

count(*)

----------

72288

sql>

三. 分割槽表的其他操作

3.1 新增新的分割槽

新增新的分割槽有2中情況：

（1）原分割槽裡邊界是maxvalue或者default。這種情況下，我們需要把邊界分割槽drop掉，加上新分割槽後，在新增上新的分割槽。或者採用split，對邊界分割槽進行拆分。

（2）沒有邊界分割槽的。這種情況下，直接新增分割槽就可以了。

以邊界分割槽新增新分割槽示例：

（1）分割槽表和索引的資訊如下：

SQL> create table custaddr

2 (

3 id varchar2(15 byte) not null,

4 areacode varchar2(4 byte)

5 )

6 partition by list (areacode)

7 (

8 partition t_list556 values ('556') tablespace icd_service,

9 partition p_other values (default)tablespace icd_service

10 );

表已建立。

SQL> create index ix_custaddr_id on custaddr(id)

2 local (

3 partition t_list556 tablespace icd_service,

4 partition p_other tablespace icd_service

5 );

索引已建立。

（2）插入幾條測試資料：

SQL> insert into custaddr values('1','556');

已建立 1 行。

SQL> insert into custaddr values('2','551');

已建立 1 行。

SQL> insert into custaddr values('3','555');

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from custaddr;

ID AREA

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

1 556

2 551

3 555

SQL> select * from custaddr partition(t_list556);

ID AREA

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

1 556

SQL>

（3）刪除default分割槽

sql> alter table custaddr drop partition p_other;

表已更改。

sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

table_name partition_name

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

custaddr t_list556

（4）新增新分割槽

SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR T_LIST556

CUSTADDR T_LIST551

（5）新增default 分割槽

SQL> alter table custaddr add partition p_other values (default) tablespace icd_service;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR T_LIST556

CUSTADDR T_LIST551

CUSTADDR P_OTHER

（6）對於區域性索引，oracle會自動增加一個區域性分割槽索引。驗證一下：

sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id';

owner index_name table_name

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

icd ix_custaddr_id custaddr

sql> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id';

index_owner index_name partition_name

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

icd ix_custaddr_id p_other

icd ix_custaddr_id t_list551

icd ix_custaddr_id t_list556

分割槽索引自動建立了。

3.2 split 分割槽拆分

在3.1 中，我們說明了可以使用split的方式來新增分割槽。這裡我們用split方法繼續上面的實驗。

sql> alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service);

表已更改。

--注意這裡紅色的地方，如果是Range型別的，使用at，List使用Values。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR T_LIST556

CUSTADDR T_LIST551

CUSTADDR T_LIST552

CUSTADDR P_OTHER

SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID';

index_owner index_name partition_name

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

icd ix_custaddr_id p_other

icd ix_custaddr_id t_list551

icd ix_custaddr_id t_list552

icd ix_custaddr_id t_list556

注意：分割槽表會自動維護區域性分割槽索引。全域性索引會失效，需要進行rebuild。

3.3 合併分割槽Merge

相鄰的分割槽可以merge為一個分割槽，新分割槽的下邊界為原來邊界值較低的分割槽，上邊界為原來邊界值較高的分割槽，原先的區域性索引相應也會合並，全域性索引會失效，需要rebuild。

SQL> alter table custaddr merge partitions t_list552,p_other into partition p_other;

表已更改。

SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID';

index_owner index_name partition_name

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

icd ix_custaddr_id p_other

icd ix_custaddr_id t_list551

icd ix_custaddr_id t_list556

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

table_name partition_name

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

custaddr t_list556

custaddr t_list551

custaddr p_other

3.4 . 移動分割槽

SQL> alter table custaddr move partition P_OTHER tablespace system;

表已更改。

SQL> alter table custaddr move partition P_OTHER tablespace icd_service;

表已更改。

注意：分割槽移動會自動維護區域性分割槽索引，oracle不會自動維護全域性索引，所以需要我們重新rebuild分割槽索引，具體需要rebuild哪些索引，可以透過dba_part_indexes,dba_ind_partitions去判斷。

SQL> Select index_name,status From user_indexes Where table_name='CUSTADDR';

INDEX_NAME STATUS

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

IX_CUSTADDR_ID N/A

3.5. Truncate分割槽

SQL> select * from custaddr partition(T_LIST556);

ID AREA

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

1 556

SQL> alter table custaddr truncate partition(T_LIST556);

表被截斷。

SQL> select * from custaddr partition(T_LIST556);

未選定行

說明：

Truncate相對delete操作很快，資料倉儲中的大量資料的批次資料載入可能會有用到；截斷分割槽同樣會自動維護區域性分割槽索引，同時會使全域性索引unusable,需要重建

3.6. Drop分割槽

SQL> alter table custaddr drop partition T_LIST551;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

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

CUSTADDR T_LIST556

CUSTADDR P_OTHER

同樣會自動維護區域性分割槽索引，同時會使全域性索引unusable,需要重建

四. 分割槽表的索引

分割槽索引分為本地(local index)索引和全域性索引(global index)。區域性索引比全域性索引容易管理, 而全域性索引比較快。

與索引有關的表：

dba_part_indexes 分割槽索引的概要統計資訊，可以得知每個表上有哪些分割槽索引，分割槽索引的型別(local/global)

dba_ind_partitions 每個分割槽索引的分割槽級統計資訊

dba_indexes/dba_part_indexes 可以得到每個表上有哪些非分割槽索引

Local索引肯定是分割槽索引，Global索引可以選擇是否分割槽，如果分割槽，只能是有字首的分割槽索引。

分割槽索引分2類：有字首(prefix)的分割槽索引和無字首(nonprefix)的分割槽索引:

（1）有字首的分割槽索引指包含了分割槽鍵，並且將其作為引導列的索引。

如：

create index i_id_global on PDBA(id) global --引導列

2 partition by range(id) --分割槽鍵

3 (partition p1 values less than (200),

4 partition p2 values less than (maxvalue)

5 );

這裡的ID 就是分割槽鍵，並且分割槽鍵id 也是索引的引導列。

（2）無字首的分割槽索引的列不是以分割槽鍵開頭，或者不包含分割槽鍵列。

如：

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list556 tablespace icd_service,

partition p_other tablespace icd_service

)

這個分割槽是按照areacode來的。但是索引的引導列是ID。所以它就是非字首分割槽索引。

全域性分割槽索引不支援非字首的分割槽索引，如果建立，報錯如下：

SQL> create index i_time_global on PDBA(id) global --索引引導列

2 partition by range(time) --分割槽建

3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

4 partition p2 values less than (maxvalue)

5 );

partition by range(time)

*

第 2 行出現錯誤:

ORA-14038: GLOBAL 分割槽索引必須加上字首

4.1 Local 本地索引

對於local索引，當表的分割槽發生變化時，索引的維護由Oracle自動進行。

注意事項：

（1）區域性索引一定是分割槽索引，分割槽鍵等同於表的分割槽鍵。

（2）字首和非字首索引都可以支援索引分割槽消除，前提是查詢的條件中包含索引分割槽鍵。

（3）區域性索引只支援分割槽內的唯一性，無法支援表上的唯一性，因此如果要用區域性索引去給表做唯一性約束，則約束中必須要包括分割槽鍵列。

（4）區域性分割槽索引是對單個分割槽的，每個分割槽索引只指向一個表分割槽；全域性索引則不然，一個分割槽索引能指向n個表分割槽，同時，一個表分割槽，也可能指向n個索引分割槽，對分割槽表中的某個分割槽做truncate或者move，shrink等，可能會影響到n個全域性索引分割槽，正因為這點，區域性分割槽索引具有更高的可用性。

（5）點陣圖索引必須是區域性分割槽索引。

（6）區域性索引多應用於資料倉儲環境中。

（7） B樹索引和點陣圖索引都可以分割槽，但是HASH索引不可以被分割槽。

示例：

sql> create index ix_custaddr_local_id on custaddr(id) local;

索引已建立。

和下面SQL 效果相同，因為local索引就是分割槽索引：

create index ix_custaddr_local_id_p on custaddr(id)

local (

partition t_list556 tablespace icd_service,

partition p_other tablespace icd_service

)

SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local;

索引已建立。

驗證2個索引的型別：

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR';

index_name table_name partition locali alignment

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

ix_custaddr_local_areacode custaddr list local prefixed

ix_custaddr_local_id custaddr list local non_prefixed

因為我們的custaddr表是按areacode進行分割槽的，所以索引ix_custaddr_local_areacode是有字首的索引（prefixed）。而ix_custaddr_local_id是非字首索引。

4.2 Global索引

對於global索引，可以選擇是否分割槽，而且索引的分割槽可以不與表分割槽相對應。全域性分割槽索引只能是B樹索引，到目前為止(10gR2)，oracle只支援有字首的全域性索引。

另外oracle不會自動的維護全域性分割槽索引，當我們在對錶的分割槽做修改之後，如果對分割槽進行維護操作時不加上update global indexes的話，通常會導致全域性索引的INVALDED，必須在執行完操作後 REBUILD。

注意事項：

（1）全域性索引可以分割槽，也可以是不分割槽索引，全域性索引必須是字首索引，即全域性索引的索引列必須是以索引分割槽鍵作為其前幾列。

（2）全域性索引可以依附於分割槽表；也可以依附於非分割槽表。

（3）全域性分割槽索引的索引條目可能指向若干個分割槽，因此，對於全域性分割槽索引，即使只截斷一個分割槽中的資料，都需要rebulid若干個分割槽甚至是整個索引。

（4）全域性索引多應用於oltp系統中。

（5）全域性分割槽索引只按範圍或者雜湊分割槽，hash分割槽是10g以後才支援。

（6） oracle9i以後對分割槽表做move或者truncate的時可以用update global indexes語句來同步更新全域性分割槽索引，用消耗一定資源來換取高度的可用性。

（7）表用a列作分割槽，索引用b做區域性分割槽索引，若where條件中用b來查詢，那麼oracle會掃描所有的表和索引的分割槽，成本會比分割槽更高，此時可以考慮用b做全域性分割槽索引。

注意：Oracle只支援2中型別的全域性分割槽索引：

range partitioned 和 Hash Partitioned.

官網的說明如下：

Global Partitioned Indexes

Oracle offers two types of global partitioned index: range partitioned and hash partitioned.

（1）Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

（2）Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

（3）Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

示例1 全域性索引，全域性索引對所有分割槽型別都支援：

sql> create index ix_custaddr_ global_id on custaddr(id) global;

索引已建立。

示例2：全域性分割槽索引，只支援Range 分割槽和Hash 分割槽：

（1）建立2個測試分割槽表：

sql> create table pdba (id number, time date) partition by range (time)

2 (

3 partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),

4 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),

5 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),

6 partition p4 values less than (maxvalue)

7 );

表已建立。

SQL> create table Thash

2 (

3 id number primary key,

4 item_id number(8) not null

5 )

6 partition by hash(id)

7 (

8 partition part_01,

9 partition part_02,

10 partition part_03

11 );

表已建立。

（2）建立分割槽索引

示例2：全域性分割槽索引

SQL> create index i_id_global on PDBA(id) global

2 partition by range(id)

3 (partition p1 values less than (200),

4 partition p2 values less than (maxvalue)

5 );

索引已建立。

--這個是有字首的分割槽索引。

SQL> create index i_time_global on PDBA(id) global

2 partition by range(time)

3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

4 partition p2 values less than (maxvalue)

5 );

partition by range(time)

*

第 2 行出現錯誤:

ORA-14038: GLOBAL 分割槽索引必須加上字首

SQL> create index i_time_global on PDBA(time) global

2 partition by range(time)

3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')),

4 partition p2 values less than (maxvalue)

5 );

索引已建立。

--有字首的分割槽索引

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA';

index_name table_name partition locali alignment

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

i_id_global pdba range global prefixed

i_time_global pdba range global prefixed

SQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL

2 PARTITION BY HASH (id)

3 (PARTITION p1,

4 PARTITION p2,

5 PARTITION p3,

6 PARTITION p4);

索引已建立。

只要索引的引導列包含分割槽鍵，就是有字首的分割槽索引。

4.3 索引重建問題

（1）分割槽索引

對於分割槽索引，不能整體進行重建，只能對單個分割槽進行重建。語法如下：

Alter index idx_name rebuild partition index_partition_name [online nologging]

說明：

online:表示重建的時候不會鎖表。

nologging：表示建立索引的時候不生成日誌，加快速度。

如果要重建分割槽索引，只能drop表原索引，在重新建立：

SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM;

這個操作要求較大的臨時表空間和排序區。

示例：

SQL> select index_name,partition_name from user_ind_partitions where index_name='I_TIME_GLOBAL';

INDEX_NAME PARTITION_NAME

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

I_TIME_GLOBAL P1

I_TIME_GLOBAL P2

SQL> alter index I_TIME_GLOBAL rebuild partition p1 online nologging;

索引已更改。

SQL> alter index I_TIME_GLOBAL rebuild partition p2 online nologging;

索引已更改。

（2）全域性索引

Oracle 會自動維護分割槽索引，對於全域性索引，如果在對分割槽表操作時，沒有指定update index，則會導致全域性索引失效，需要重建。

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner index_name table_name status

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

sys ix_pdba_global pdba valid

刪除一個分割槽：

SQL> alter table pdba drop partition p2;

表已更改。

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner index_name table_name status

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

sys ix_pdba_global pdba valid

split 分割槽：

SQL> alter table pdba split partition P4 at(TO_DATE('2010-12-21 00:00:00','YYYY-MM-DD HH24:MI:SS')) into (partition P4, partition P5);

表已更改。

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner index_name table_name status

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

sys ix_pdba_global pdba valid

drop 分割槽時使用update indexes

SQL> alter table pdba drop partition P4 UPDATE INDEXES;

表已更改。

SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL';

owner index_name table_name status

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

sys ix_pdba_global pdba valid

做了幾個drop分割槽操作，全域性索引沒有失效，有點奇怪。不過如果在生產環境中，還是小心點。

重建全域性索引命令如下：

Alter index idx_name rebuild [online nologging]

示例：

SQL> Alter index ix_pdba_global rebuild online nologging;

索引已更改。

補充一點，分割槽表儲存空間的問題：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='DBA';

TABLE_NAME PARTITION_NAME                TABLESPACE_NAME
---------- ------------------------------ ------------------------------
DBA       P1                            SYSTEM
DBA       P2                            SYSTEM
DBA       P3                            SYSTEM
DBA       P4                            SYSTEM

透過user_tab_partitions 表可以檢視到每個分割槽對應的tablesapce_name. 但是，如果透過all_tables 表，卻查不到分割槽表對應表空間的資訊。

分割槽表：
SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DBA';

OWNER TABLE_NAME TABLESPACE_NAME             CLUSTER_NAME
----- ---------- ------------------------------ -----------------------------------------------------
SYS DBA

普通表：
SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DAVE';

OWNER TABLE_NAME TABLESPACE_NAME             CLUSTER_NAME
----- ---------- ------------------------------ ---------------------------------------------------
SYS DAVE    SYSTEM

Oracle資料庫中分割槽表的操作方法詳解

相關文章