詳細講解Oracle表分割槽相關概念及優點

Oracle 8i以後推出了分割槽選項。分割槽將表分離在若於不同的表空間上,用分而治之的方法來支撐元限膨脹的大表,組大表在物理一級的可管理性.將大表分割成較小的分割槽可以改善表的維護、備份、恢復、事務及查詢效能。

　　分割槽的具體優點：

　　1、 增強可用性：如果表的一個分割槽由於系統故障而不能使用，表的其餘好的分割槽仍可以使用；

　　2、 減少關閉時間：如果系統故障隻影響表的一部份分割槽，那麼只有這部份分割槽需要修復，可能比整個大表修復花的時間更少；

　　3、 維護輕鬆：如果需要得建表，獨產管理每個公區比管理單個大表要輕鬆得多；

　　4、 均衡I/O：可以把表的不同分割槽分配到不同的磁碟來平衡I/O改善效能；

　　5、 改善效能：對大表的查詢、增加、修改等操作可以分解到表的不同分割槽來並行執行，可使執行速度更快，在資料倉儲的TP查詢特別有用。

　　6、 分割槽對使用者透明，終端使用者感覺不到分割槽的存在。

　　列表分割槽表create table BS_CDR_WLAN_LOC_01

　　(

　　DAY_NUMBER NUMBER(2) not null,

　　......

　　TPREMARK VARCHAR2(200)

　　)

　　partition by list (DAY_NUMBER)

　　(

　　partition P_BS_CDR_OTH_LOC_01 values (1),

　　partition P_BS_CDR_OTH_LOC_02 values (2),

　　.......

　　partition P_BS_CDR_OTH_LOC_31 values (31)

　　);

　　按範圍分割槽

　　注：就是按一定range來分割槽

　　SQL> create table niegc_part

　　2 (

　　3 part_id integer primary key,

　　4 part_date date,

　　5 part_dec varchar2(100)

　　6 )

　　7 partition by range(part_date)

　　8 (

　　9 partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')) ,

　　10 partition part_02 values less than(to_date('2007-01-01','yyyy-mm-dd')) ,

　　11 partition part_03 values less than(maxvalue)

　　12 );

　　Hash分割槽（雜湊分割槽）

　　雜湊分割槽通過指定分割槽編號來均勻分佈資料的一種分割槽型別，因為通過在I/O裝置上進行雜湊分割槽，使行這些分割槽大小一致。

　　如將part_id的資料根據自身的情況雜湊地存放在指定的三個表空間中：

　　create table niegc_part

　　(

　　part_id integer primary key,

　　part_date date,

　　part_dec varchar2(100)

　　)

　　partition by hash(part_id)

　　(

　　partition part_01 tablespace dw1,

　　partition part_02 tablespace dw2

　　);

　　複合分割槽

　　create table tab_students

　　(c_id number,

　　c_name varchar2(10),

　　c_age number,

　　c_birthday date,

　　c_nation varchar2(10)

　　)

　　partition by range(c_id)

　　subpartition by list(c_nation)

　　SUBPARTITION TEMPLATE

　　(SUBPARTITION part_hanzu VALUES ('漢族'),

　　SUBPARTITION part_others VALUES (DEFAULT)

　　)

　　(

　　PARTITION part_id1 VALUES LESS THAN (1000),

　　PARTITION part_id2 VALUES LESS THAN (2500),

　　PARTITION part_id3 VALUES LESS THAN (MAXVALUE)

　　);

　　索引分割槽：

　　注意： 對某個欄位已做了分割槽了，是不允許再建立索引分割槽的。這一點要非常注意。

　　全域性索引建立時global子句允許指定索引的範圍值，這個範圍值為索引欄位的範圍值：

　　create index idx_part_id on niegc_part(part_dec)

　　global partition by range(part_dec)

　　(

　　partition idx_1 values less than('1000') tablespace dw,

　　partition idx_2 values less than(maxvalue) tablespace dw

　　)

　　區域性索引分割槽的建立：

　　（注：表必須存在分割槽，此分割槽的個數必須和分割槽表的分割槽個數一樣，不然是建立不起來的）

　　create index idx_part_id on niegc_part(part_dec)

　　local

　　(

　　partition idx_1 tablespace dw1,

　　partition idx_2 tablespace dw2

　　)

分割槽維護：(只對範圍分割槽)

　　（1）、增加一個分割槽：分割槽範圍只能往上增，不能增加一個少於原有的分割槽：

　　alter table tablename add partition new_partitionname values less than(maxvalue)

　　（2）、合併/拆分分割槽：（合併後的分割槽必須指下最後一個大value的分割槽）

　　alter table tablename merge partitions partitionname1,partitionname2 into partition partitionname2;

　　alter table tablename split partition partitionname1 at (xx) into (

　　partition newpartition1 ,partition newpartition2) ;

　　注意：xx為分割點

　　（3）、刪除一個分割槽：

　　alter table niegc_part drop partition partitionname;

　　(4)將分割槽改名

　　alter table table_name rename Partition partition_name to partition_name

　　(5)將分割槽改表空間

　　alter table table_name move partition_name

　　tablespace tablespace_name nologging

　　(6)查詢特定分割槽

　　select count(*) from table_name partition (partition_name);

　　(7)新增資料

　　insert into table_name select * from table_name partition (partition_name)

　　(8)分割槽表的匯出

　　userid=USER/PWD

　　buffer=102400

　　tables=table_name:partition_name,

　　file=E:exp_paraxxx.dmp

　　log=E:exp_paraxxx.log

　　(9)技巧：刪除表中一個欄位

　　alter table table_name set unused column column_name;

　　(10)加一個欄位

　　alter table table_name add column_name number(1);

　　六、總結：

　　分割槽表是將大表的資料分成稱為分割槽的許多小的子集，9i提供四種分割槽方法：列表分割槽，範圍分割槽，雜湊分割槽和混合分割槽；

　　· 範圍分割槽是根椐分割槽鍵的不同取值範圍來劃分子集的，關鍵字RANGE， VALUES LESS THAN；

　　· 列表分割槽是根椐分割槽鍵的一些離散的取值來劃分子集的，關鍵字LIST， VALUES；

　　· 雜湊分割槽是應用雜湊演算法將分割槽鍵對應到某個子集中去，關鍵字HASH， PARTITIONS；

　　· 混合分割槽只能有兩層，第一層是範圍分割槽，第二層可以是列表分割槽或者雜湊分割槽；

　　· 範圍分割槽和列表分割槽中，如果插入記錄的分割槽鍵沒有對應的容納分割槽，會產生ORA-14400；

　　· update操作如果會使記錄從一個分割槽遷移到另一個分割槽，且分割槽表的ROW MOVEMENT屬性是DISABLE，會產ORA-14402；

　　· 分割槽表上的索引有兩大類：普通的二叉樹索引，分割槽索引，下面講到的都是分割槽索引：

　　· 按索引分割槽和表分割槽間的對應關係可以分為區域性索引和全域性索引；

　　· 區域性索引的索引分割槽和表分割槽間是一一對應的，全域性索引則相反；

　　· 區域性索引的分割槽方法可以用上面提到四種的任何一種，全域性索引的分割槽方法只有範圍分割槽（而且最高的分割槽必須用MAXVALUE來定義）；

　　· ORACLE自動維護區域性索引的分割槽，當表分割槽被合併，分裂或刪除時，關聯的索引分割槽也會被合併，分裂或刪除；對分割槽表執行管理操作時會使其上的全域性索引失效；

　　· 建在分割槽表的點陣圖索引必須是區域性分割槽索引；

　　· ORACLE推薦儘可能地使用區域性索引；

　　·按索引欄位和分割槽鍵間的關係分為字首索引和非字首索引；

　　· 字首索引最前面的欄位是分割槽鍵欄位，非字首索引相反；

　　· 在這兩種分類方法的四種組合中，只有三種有效（區域性字首索引，區域性非字首索引，全域性字首索引），不存在全域性非字首索引；

　　· 分割槽表給CBO帶來很多選項，如分割槽排除，並行分割槽連線等。

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