oracle分割槽表

--==================

--  Oracle 分割槽表

--==================

 

一、分割槽表：

    隨著表的不斷增大，對於新紀錄的增加、查詢、刪除等(DML)的維護也更加困難。對於資料庫中的超大型表，可透過把它的資料分成若干個小表，從而簡化資料庫的管理活動。對於每一個簡化後的小表，我們稱為一個單個的分割槽。

 

    對於分割槽的訪問，我們不需要使用特殊的SQL查詢語句或特定的DML語句，而且可以單獨的操作單個分割槽，而不是整個表。同時可以將不同分割槽的資料放置到不同的表空間，比如將不同年份的銷售資料，存放在不同的表空間，即年的銷售資料存放到TBS_2001,2002年的銷售資料存放到TBS_2002,依次類推，從而實現了分散儲存，這將大大的簡化大容量表的管理，提高查詢效能及I/O併發等。

   

    對於外部應用程式來說，雖然存在不同的分割槽，且資料位於不同的表空間，但邏輯上仍然是一張表

     

    可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具來裝載或解除安裝分割槽表中的資料

   

    關於分割槽表的功能實際上同SQL server 中的分割槽表是同樣的概念，只不過SQL server中的資料存放到了檔案組，相當於Oracle概念中的表空間，

    有興趣的可以參考：

        SQL server 2005基於已存在的表建立分割槽

        SQL server 2005 切換分割槽表

 

二、何時分割槽

    當表達到GB大小且繼續增長

    需要將歷史資料和當前的資料分開單獨處理，比如歷史資料僅僅需要只讀，而當前資料則實現DML

   

三、分割槽的條件及特性

    共性：不同的分割槽之間必須有相同的邏輯屬性，比如表名，列名，資料型別，約束等，

   

    個性：各個分割槽可以有不同的物理屬性，比如pctfree, pctused, and tablespaces.

 

　　分割槽獨立性：即使某些分割槽不可用，其他分割槽仍然可用。

 

　　特殊性：含有LONG、LONGRAW資料型別的表不能進行分割槽

 

四、分割槽的優點

    1、提高查詢效能：只需要搜尋特定分割槽，而非整張表，提高查詢速度

    2、節約維護時間：單個分割槽的資料裝載，索引重建，備份，維護等將遠小於整張表的維護時間。

    3、節約維護成本：可以單獨備份和恢復每個分割槽

    4、均衡I/O：將不同的分割槽對映到不同的磁碟以平衡I/O，提高併發

 

五、ORACLE分割槽型別：

    範圍分割槽、雜湊分割槽、列表分割槽、組合分割槽

    可以對索引和表分割槽，全域性索引只能按範圍分割槽，可以將其定義在任何型別的分割槽或非分割槽表上。

    通常全域性索引比區域性索引需要更多的維護

    區域性索引與基礎表是等同分割槽的，用於反映其基礎表的結構

   

    1.Range分割槽：行對映到基於列值範圍的分割槽

        Range 分割槽，又成為範圍分割槽，基於分割槽鍵值的範圍將資料對映到所建立的分割槽上。這是最通用的分割槽型別。

        建立範圍分割槽時，必須指定以下內容

            分割槽方法：range

            分割槽列

            標識分割槽邊界的分割槽描述

           

        使用Range 分割槽的時候，要記住幾條規則：

            每個分割槽都包含VALUES LESS THAN字名，定義了分割槽的上層邊界。任何等於和大於分割槽鍵值的二進位制值都被新增到下一個高層分割槽中。

            所有的分割槽，除了第一個，如果低於VALUES LESS THAN所定義的下層邊界，都放在前面的分割槽中。

            MAXVALUE可以用來定義最高層的分割槽。MAXVALUE表示了虛擬的無限值

 

        示例：

            create table sal_range   

            (salesman_id number(5),

            salesman_name varchar2(30),

            sales_amount number(10),

            sales_date date)

            partition by range (sales_date)   --建立基於日期的範圍分割槽並儲存到不同的表空間

            (

            partition sal_jan2000 values less than(to_date('02/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_jan2000,

            partition sal_feb2000 values less than(to_date('03/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_feb2000,

            partition sal_mar2000 values less than(to_date('04/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_mar2000,

            partition sal_apr2000 values less than(to_date('05/01/2000',

                'DD/MM/YYYY')) tablespace sal_range_apr2000

            );

 

            create table r      --建立基於值範圍的分割槽，分割槽子句未指定表空間時則位於預設的表空間

            (a int)

            partition by range (a)

            (

                partition p1 values less than (10),

                partition p2 values less than (20),

                partition p3 values less than (30),

                partition p4 values less than (maxvalue)

            );

 

            select * from r partition (p1)    --檢視分割槽中的資料

       

            一個分割槽的損壞不會影響其它分割槽的資料：

            alter table r drop partiton p1

            select * from r

            select * from r partition (p4)

            除分割槽資料不見外，其它都正常

 

        partition by 用於指定分割槽方式

        range 表示分割槽的方式是範圍劃分

        partition pn 用於指定分割槽的名字

        values less than 指定分割槽的上界（上限）

 

        新增分割槽：

            ALTER TABLE r

            add partition p5 values less than (xxx ) tablespace xx;

 

        檢視分割槽表相關資訊：

            SELECT table_name,partition_name,subpartition_count,

            tablespace_name,user_stats from user_tab_partitions;

 

        獲取建立分割槽表的後設資料：

              set long 10000

              select dbms_metadata.get_ddl('TABLE','R','SCOTT') from dual;

                                        表  表名  使用者名稱   區分大小寫

 

    2.Hash分割槽：雜湊分割槽

        Hash分割槽能夠很容易對資料進行分割槽，因為語法很簡單，很容易實現。在下面這種

        情況下，使用hash分割槽比range分割槽更好：

            事先不知道需要將多少資料對映到給定範圍的時候

            分割槽的範圍大小很難確定，或者很難平衡的時候

            Range分割槽使資料得到不希望的聚集時

            效能特性，如並行DML、分割槽剪枝和分割槽連線很重要的時候

        建立雜湊分割槽時，必須指定以下資訊

            分割槽方法：hash

            分割槽列

            分割槽數量或單獨的分割槽描述

 

        分裂、刪除和合並分割槽不能應用於Hash分割槽，但是，Hash分割槽能夠合併和新增。

 

        建立hash分割槽有兩種方法：一種方法是指定分割槽數量，另一種方法是指定分割槽的名字，

        但兩者不能同時指定。

 

        方法一：指定分割槽數量

        create table dept2 (deptno number,deptname varchar2(32))

        partition by hash(deptno) partitions 4;

 

        方法二：指定分割槽的名字

        create table dept3 (deptno number,deptname varchar2(32))

        partition by hash(deptno)             

        (partition p1 tablespace p1,

        partition p2 tablespace p2);

 

        create table sales_hash

        (salesman_id number(5),

        salesman_name varchar2(30),

        sales_amount number(10),

        week_no number(2))

        partition by hash (salesman_id)

        partitions 4

        store in (data1,data2,data3,data4)

       

        data1,data2,data3,data4 為表空間名。

        雜湊分割槽表的每個分割槽都被儲存在單獨的段中。

       

    3.List分割槽：列表分割槽

        List分割槽可以控制如何將行對映到分割槽中去。可以在每個分割槽的鍵上定義離散的值

        不同於Range分割槽和Hash分割槽，

            Range分割槽與分割槽相關聯，為分割槽列假設了一個值的自然範圍，故不可能將該值的範圍以外的分割槽組織到一起。

            hash分割槽時不允許對資料的劃分進行控制，因為系統使用的是雜湊函式來劃分資料的。

        List分割槽的優點在於按照自然的方式將無序和不相關的資料集合分組。

        List分割槽不支援多列分割槽，如果將表按列分割槽，那麼分割槽鍵就只能有表的一個單獨列組成。

        Range分割槽和Hash分割槽可以對多列進行分割槽。

        List分割槽時必須指定的以下內容

            分割槽方法：list

            分割槽列

            分割槽描述，每個描述指定一串文字值(值的列表),它們是分割槽列(它們限定將被包括在分割槽中的行)的離散值

       

        示例：

        create table sales_list

        (salesman_id number(5),

        salesman_name varchar2(30),

        sales_state varchar2(20),

        sales_amount number(10),

        sales_date date)

        partition by list (sales_state)

        (

        partition sales_west values ('California','Hawaii') tablespace x,

        partition sales_east values ('New York','Virginia') tablespace y,

        partition sales_central values ('Texas','Illinois') tablespace z,

        partition sales_other values(DEFAULT) tablespace o

        );

 

        新增分割槽：

           alter table sales3 add partition hk values ('HK') tablespace xx

 

    4.Composite Partitioning：合成分割槽、組合分割槽

        組合分割槽使用range方法分割槽，在每個子分割槽中使用hash方法進行再分割槽。

        組合分割槽比range分割槽更容易管理，充分使用了hash分割槽的並行優勢。組合分割槽支援歷史資料和條塊資料兩者。

        如新增新的RANGE分割槽，同時為DML操作提供更高層的並行性。

        建立組合分割槽時，需要指定如下內容：

            分割槽方法：range

            分割槽列

            標識分割槽邊界的分割槽描述

            子分割槽方法：hash

            子分割槽列

            每個分割槽的子分割槽數量，或子分割槽的描述

       

        create table sales_composite

        (salesman_id number(5),

        salesman_name varchar2(30),

        sales_amount number(10),

        sales_date date)

        partition by range(sales_date)

        subpartition by hash(salesman_id)

        subpartitions 4

        store in (tbs1,tbs2,tbs3,tbs4)

        (partition sales_jan2000 values less than(to_date('02/01/2000','DD/MM/YYYY')),

        partition sales_feb2000 values less than(to_date('03/01/2000','DD/MM/YYYY')),

        partition sales_mar2000 values less than(to_date('04/01/2000','DD/MM/YYYY'))

        );

 

        create table T_TRACK 

        (

            N_TRACK_ID           NUMBER(20)     NOT NULL, 

            C_COMP_CDE           VARCHAR2(6),

            T_TRACK_TM           DATE           NOT NULL,

            C_CAR_NO             VARCHAR2(50)

        )

        partition by range(T_TRACK_TM)

        subpartition by list(C_COMP_CDE)

        (

            partition P_2009_11 values less than (to_date('2009-12-01','yyyy-MM-dd'))

                    (

                    subpartition P_2009_11_P1013 values('P1013')

                    )

        );

 

六、表分割槽後的相關操作

    1.新增分割槽

        alter table T_TRACK add partition P_2005_04

        values less than(to_date('2005-05-01','yyyy-MM-dd'))

        (

            subpartition P_2005_04_P1013 values('P1013'),

            subpartition P_2005_04_P1013 values('P1014'),

            subpartition P_2005_04_P1013 values('P1015'),

            subpartition P_2005_04_P1013 values('P1016')

        )

 

    2.刪除分割槽

        alter table T_TRACK drop partition p_2005_04;

 

    3.新增子分割槽

        alter table T_TRACK

        modify partition P_2005_01

        add subpartition P_2005_01_P1017 values('P1017');

 

    4.刪除子分割槽

        alter table T_TRACK drop subpartition p_2005_01_p1017;

 

    5.截斷一個分割槽表中的一個分割槽的資料：

        alter table sales3  truncate partition sp1

            這種方式會使全域性分割槽索引無效

        alter table sales3 truncate partition sp1 update indexes

            這種方式全域性分割槽索引不會無效

 

    6.截斷分割槽表的子分割槽

        alter table comp truncate subpartition sub1

 

    7.截斷帶有約束的分割槽表

        a、禁用約束

          alter table sales disable constraint dname_sales1

        b、截斷分割槽

          alter table sales truncate partitoin dec

        c、啟用約束

          alter table sales enable constraint dname_sales1

 

    8.檢視一個表是不是分割槽表

        select table_name,partitioned from user_tables;

        TABLE_NAME                     PAR

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

        DEPT                           NO

        DEPT3                          YES

 

    9.將一個表的分割槽從一個表空間移動到另一個表空間

        a、檢視分割槽在哪個表空間

          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,

          SUBPARTITION_COUNT

          FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';

 

        b、移動分割槽

          alter table sales move partiton sp1 tablespace tp;

 

        c、檢查是否移動成功

          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,

          SUBPARTITION_COUNT

          FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';

 

        移動表空間後，要重建索引，否則索引會變得無效

        alter index xxx rebuild

 

 

    10.合併分割槽：

        alter table sales3 merge partitons sp1,sp3 into partition sp3

        合併後的分割槽名，不能是邊界值較低的那個

 

    11.刪除分割槽：

        alter table scott.sales_composite drop partition SALES_JAN2000;

 

    與分割槽表相關的資料字典檢視：

        DBA_TAB_PARTITIONS

        DBA_IND_PARTITIONS

        DBA_TAB_SUBPARTITIONS

        DBA_IND_SUBPARTITIONS

        Oracle關於分割槽的線上文件：------------------------&gt>oracle分割槽資訊表羅列:

顯示資料庫所有分割槽表的資訊：DBA_PART_TABLES

顯示當前使用者可訪問的所有分割槽表資訊：ALL_PART_TABLES

顯示當前使用者所有分割槽表的資訊：USER_PART_TABLES

顯示錶分割槽資訊 顯示資料庫所有分割槽表的詳細分割槽資訊：DBA_TAB_PARTITIONS

顯示當前使用者可訪問的所有分割槽表的詳細分割槽資訊：ALL_TAB_PARTITIONS

顯示當前使用者所有分割槽表的詳細分割槽資訊：USER_TAB_PARTITIONS

顯示子分割槽資訊 顯示資料庫所有組合分割槽表的子分割槽資訊：DBA_TAB_SUBPARTITIONS

顯示當前使用者可訪問的所有組合分割槽表的子分割槽資訊：ALL_TAB_SUBPARTITIONS

顯示當前使用者所有組合分割槽表的子分割槽資訊：USER_TAB_SUBPARTITIONS

顯示分割槽列 顯示資料庫所有分割槽表的分割槽列資訊：DBA_PART_KEY_COLUMNS

顯示當前使用者可訪問的所有分割槽表的分割槽列資訊：ALL_PART_KEY_COLUMNS

顯示當前使用者所有分割槽表的分割槽列資訊：USER_PART_KEY_COLUMNS

顯示子分割槽列 顯示資料庫所有分割槽表的子分割槽列資訊：DBA_SUBPART_KEY_COLUMNS

顯示當前使用者可訪問的所有分割槽表的子分割槽列資訊：ALL_SUBPART_KEY_COLUMNS

顯示當前使用者所有分割槽表的子分割槽列資訊：USER_SUBPART_KEY_COLUMNS

------------------------end ---------------------------------