ORACLE分割槽表的使用和管理

1建立和使用分割槽表

Oracle分割槽表分為四類：範圍分割槽表；列表分割槽表；雜湊分割槽表；組合分割槽表

範圍分割槽表

建立一個按欄位資料範圍分割槽的表，分割槽置於指定的不同表空間中

 

示例程式碼：

--為各個分割槽準備獨立的表空間

create tablespace test_space01 datafile‘d:/tbs01.dbf’ size 50m

create tablespace test_space02 datafile‘d:/tbs02.dbf’ size 50m

create tablespace test_space03 datafile‘d:/tbs03.dbf’ size 50m

create tablespace test_space04 datafile‘d:/tbs04.dbf’ size 50m

 

--建立分割槽表，

CREATE TABLE range_example(

    range_key_column DATE,

    DATA VARCHAR2(20),

    ID integer

)

PARTITION BY RANGE(range_key_column)

(  

PARTITION part01 VALUES LESS THAN(TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACEtest_space01,

PARTITION part02 VALUES LESS THAN(TO_DATE('2008-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space02,

PARTITION part03 VALUES LESS THAN(TO_DATE('2008-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space03,

PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test_space04

);

 

--插入測試資料

insert into range_examplevalues(TO_DATE('2008-06-10 00:00:00','yyyy-mm-dd hh24:mi:ss'), '1111', 1);

insert into range_examplevalues(TO_DATE('2008-07-20 00:00:00','yyyy-mm-dd hh24:mi:ss'), '2222', 2);

insert into range_examplevalues(TO_DATE('2008-08-25 00:00:00','yyyy-mm-dd hh24:mi:ss'), '3333', 3);

commit;

 

--在表上執行查詢

select * from range_example;

 

 

--在表分割槽上執行查詢

select * from range_example partition(part01);

 

列表分割槽表

建立一個按欄位資料列表固定可列舉值分割槽的表。插入記錄分割槽欄位的值必須在列表中，否則不能被插入。

示例程式碼：

CREATE TABLE list_example(

dnameVARCHAR2(10),

DATA VARCHAR2(20)

)

PARTITION BY LIST(dname)

(

PARTITION part01VALUES('初始登記','轉移登記'),

PARTITION part02 VALUES('更名登記','樓盤變更'),

PARTITION part03 VALUES('抵押登記'),

PARTITION part03 VALUES('限制登記')

);

雜湊分割槽表

建立一個按欄位資料Hash值分割槽的表

示例程式碼：

CREATE TABLE hash_example(

     hash_key_column DATE,

     DATA VARCHAR2(20)

)

PARTITION BY HASH(hash_key_cloumn)

(

    PARTITION part01,

    PARTITION part02

);

組合分割槽表

在分割槽中可以再建立子分割槽，以實現分割槽組合。可任意對上述各類分割槽進行組合分割槽。

此例中建立了一個由範圍分割槽和雜湊分割槽組合實現分割槽的表。

示例程式碼：

CREATE TABLE range_hash_example(

 range_column_key int,

 hash_column_key INT,

 DATAVARCHAR2(20)

)

PARTITION BY RANGE(range_column_key)

SUBPARTITION BY HASH(hash_column_key)SUBPARTITIONS 2

(

PARTITION part_1 VALUES LESS THAN (100000000)

(

   SUBPARTITION part_1_sub_1,

   SUBPARTITION part_1_sub_2,

   SUBPARTITION part_1_sub_3

    ),

PARTITION part_2 VALUES LESS THAN (200000000)

(

     SUBPARTITION part_2_sub_1,

   SUBPARTITION part_2_sub_2

)

);

--注 subpartitions 2 並不是指定subpartition的個數一定為2，實際上每個分割槽的子分割槽個數可以不同。如果不指定subpartition的具體明細，則系統按照subpartitions的值指定subpartition的個數生成子分割槽，名稱由系統定義 。

2增加表分割槽

-- range partitioned table

ALTER TABLE range_example ADD PARTITIONpart04 VALUES LESS THAN (TO_DATE('2008-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'));

 

--list partitioned table

ALTER TABLE list_example ADD PARTITIONpart04 VALUES ('TE');

 

--Adding Values for a List Partition

ALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES('MIS');

 

--Dropping Values from a List Partition

ALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES('MIS');

 

--hash partitioned table

ALTER TABLE hash_example ADD PARTITIONpart03;

 

--增加subpartition

ALTER TABLE range_hash_example MODIFY PARTITIONpart_1 ADD SUBPARTITION part_1_sub_4;

 

注:hash partitioned table 新增partition時，現有表的中所有data都有重新計算hash值，然後重新分配到分割槽中。所以被重新分配的分割槽的 indexes需要rebuild 。

3刪除分割槽

ALTER TABLE ... DROP PARTITION part_name;

4分割槽合併

合併父分割槽

ALTER TABLE range_example MERGE PARTITIONSpart01_1, part01_2 INTO PARTITION part01 UPDATE INDEXES;

--如果省略update indexes子句的話，必須重建受影響的分割槽的index;

ALTER TABLE range_example MODIFY PARTITIONpart02 REBUILD UNUSABLE LOCAL INDEXES;

合併子分割槽

ALTER TABLE composite_example

MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;

5轉換分割槽

可以將分割槽錶轉換成非分割槽表，或者幾種不同分割槽表之間的轉換。

如下：

CREATE TABLE hash_part02 AS SELECT * FROMhash_example WHERE 1=2;

ALTER TABLE hash_example EXCHANGE PARTITIONpart02 WITH TABLE hash_part02;

這時，分割槽表hash_example中的part02分割槽的資料將被轉移到hash_part02這個非分割槽表中。

6關於分割槽表和索引

在分割槽表上可以建立三種型別的索引：1和普通表一樣的全域性索引；2.全域性分割槽索引；3.本地分割槽索引。

它們之間的區別如下圖示:

 

 

以表range_example為例。

1.建立普通的索引

create index com_index_range_example_id onrange_example(id);

 

2.建立本地分割槽索引

create index  local_index_range_example_id onrange_example(id) local;

 

3.建立全域性分割槽索引

create index gidx_range_exampel_id onrange_example(id)

GLOBAL partition by  range(id)

(

 part_01 values less than(1000),

 part_02 values less than(MAXVALUE)

);

 

對於分割槽索引的刪除，local index 不能指定分割槽名稱，單獨的刪除分割槽索引。

local index 對應的分割槽會伴隨著data分割槽的刪除而一起被刪除。globalpartition index 可以指定分割槽名稱，刪除某一分割槽。但是有一點要注意，如果該分割槽不為空，則會導致更高一級的索引分割槽被置為UNUSABLE 。

 ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句將導致part_02 狀態為UNUSABLE

http://www.cnblogs.com/advocate/archive/2010/09/03/1816628.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24570973/viewspace-751666/，如需轉載，請註明出處，否則將追究法律責任。