oracle 分區(qū)表的建立方法

1、.：.；Oracle提供了分區(qū)技術(shù)以支持VLDB(Very Large DataBase)。分區(qū)表經(jīng)過對(duì)分區(qū)列的判別，把分區(qū)列不同的記錄，放到不同的分區(qū)中。分區(qū)完全對(duì)運(yùn)用透明。 Oracle的分區(qū)表可以包括多個(gè)分區(qū)，每個(gè)分區(qū)都是一個(gè)獨(dú)立的段SEGMENT，可以存放到不同的表空間中。查詢時(shí)可以經(jīng)過查詢表來訪問各個(gè)分區(qū)中的數(shù)據(jù)，也可以經(jīng)過在查詢時(shí)直接指定分區(qū)的方法來進(jìn)展查詢。 分區(qū)提供以下優(yōu)點(diǎn)： 由于將數(shù)據(jù)分散到各個(gè)分區(qū)中，減少了數(shù)據(jù)損壞的能夠性； 可以對(duì)單獨(dú)的分區(qū)進(jìn)展備份和恢復(fù)； 可以將分區(qū)映射到不同的物理磁盤上，來分散IO； 提高可管理性、可用性和性能。 Oracle提供了以下幾種分區(qū)類型：

2、范圍分區(qū)range； 哈希分區(qū)hash； 列表分區(qū)list； 范圍哈希復(fù)合分區(qū)range-hash； 范圍列表復(fù)合分區(qū)range-list。 Oracle的普通表沒有方法經(jīng)過修正屬性的方式直接轉(zhuǎn)化為分區(qū)表，必需經(jīng)過重建的方式進(jìn)展轉(zhuǎn)變，下面引見三種效率比較高的方法，并闡明它們各自的特點(diǎn)。 方法一：利用原表重建分區(qū)表。 步驟： SQL CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 表已創(chuàng)建。 SQL INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS; 已創(chuàng)建6264行。 SQL COM

3、MIT; 提交完成。SQL CREATE TABLE T_NEW (ID, TIME) 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 (MAXV

4、ALUE) 6 AS SELECT ID, TIME FROM T; 表已創(chuàng)建。 SQL RENAME T TO T_OLD; 表已重命名。 SQL RENAME T_NEW TO T; 表已重命名。 SQL SELECT COUNT(*) FROM T; COUNT(*)- 6264 SQL SELECT COUNT(*) FROM T PARTITION (P1); COUNT(*)- 0 SQL SELECT COUNT(*) FROM T PARTITION (P2); COUNT(*)- 6246 SQL SELECT COUNT(*) FROM T PARTITION (P3);

5、COUNT(*)- 18 優(yōu)點(diǎn)：方法簡(jiǎn)單易用，由于采用DDL語句，不會(huì)產(chǎn)生UNDO，且只產(chǎn)生少量REDO，效率相對(duì)較高，而且建表完成后數(shù)據(jù)曾經(jīng)在分布到各個(gè)分區(qū)中了。 缺乏：對(duì)于數(shù)據(jù)的一致性方面還需求額外的思索。由于幾乎沒有方法經(jīng)過手工鎖定T表的方式保證一致性，在執(zhí)行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修正能夠會(huì)喪失，假設(shè)要保證一致性，需求在執(zhí)行完語句后對(duì)數(shù)據(jù)進(jìn)展檢查，而這個(gè)代價(jià)是比較大的。另外在執(zhí)行兩個(gè)RENAME語句之間執(zhí)行的對(duì)T的訪問會(huì)失敗。 適用于修正不頻繁的表，在閑時(shí)進(jìn)展操作，表的數(shù)據(jù)量不宜太大。 方法二：運(yùn)用交換分區(qū)的方法。 步驟： SQL CR

6、EATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 表已創(chuàng)建。 SQL INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS; 已創(chuàng)建6264行。 SQL COMMIT; 提交完成。 SQL CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE(2005-7-1, YYYY-MM-DD), 3 PARTITI

7、ON P2 VALUES LESS THAN (MAXVALUE); 表已創(chuàng)建。 SQL ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T; 表已更改。 SQL RENAME T TO T_OLD; 表已重命名。 SQL RENAME T_NEW TO T; 表已重命名。 SQL SELECT COUNT(*) FROM T; COUNT(*)- 6264 優(yōu)點(diǎn)：只是對(duì)數(shù)據(jù)字典中分區(qū)和表的定義進(jìn)展了修正，沒有數(shù)據(jù)的修正或復(fù)制，效率最高。假設(shè)對(duì)數(shù)據(jù)在分區(qū)中的分布沒有進(jìn)一步要求的話，實(shí)現(xiàn)比較簡(jiǎn)單。在執(zhí)行完RENAME操作后，可以檢查T_OLD

8、中能否存在數(shù)據(jù)，假設(shè)存在的話，直接將這些數(shù)據(jù)插入到T中，可以保證對(duì)T插入的操作不會(huì)喪失。 缺乏：依然存在一致性問題，交換分區(qū)之后RENAME T_NEW TO T之前，查詢、更新和刪除會(huì)出現(xiàn)錯(cuò)誤或訪問不到數(shù)據(jù)。假設(shè)要求數(shù)據(jù)分布到多個(gè)分區(qū)中，那么需求進(jìn)展分區(qū)的SPLIT操作，會(huì)添加操作的復(fù)雜度，效率也會(huì)降低。 適用于包含大數(shù)據(jù)量的表轉(zhuǎn)到分區(qū)表中的一個(gè)分區(qū)的操作。應(yīng)盡量在閑時(shí)進(jìn)展操作。 方法三：Oracle9i以上版本，利用在線重定義功能 步驟： SQL CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 表已創(chuàng)建。 SQL INSERT INTO

9、 T SELECT ROWNUM, CREATED FROM DBA_OBJECTS; 已創(chuàng)建6264行。 SQL COMMIT; 提交完成。 SQL EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, T, DBMS_REDEFINITION.CONS_USE_PK); PL/SQL 過程已勝利完成。 SQL CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE(20

10、04-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); 表已創(chuàng)建。 SQL EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, T, T_NEW, - ID ID, TIME TIME, DBMS_REDEFINITION.CO

11、NS_USE_PK); PL/SQL 過程已勝利完成。 SQL EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(YANGTK, T, T_NEW); PL/SQL 過程已勝利完成。 SQL SELECT COUNT(*) FROM T; COUNT(*)- 6264 SQL SELECT COUNT(*) FROM T PARTITION (P2); COUNT(*)- 6246 SQL SELECT COUNT(*) FROM T PARTITION (P3); COUNT(*)- 18 優(yōu)點(diǎn)：保證數(shù)據(jù)的一致性，在大部分時(shí)間內(nèi)，表T都可以正常進(jìn)展DML操作

12、。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強(qiáng)的靈敏性，對(duì)各種不同的需求都能滿足。而且，可以在切換前進(jìn)展相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需求任何額外的管理操作。 缺乏：實(shí)現(xiàn)上比上面兩種略顯復(fù)雜。 適用于各種情況。 這里只給出了在線重定義表的一個(gè)最簡(jiǎn)單的例子，詳細(xì)的描畫和例子可以參考下面兩篇文章。 Oracle的在線重定義表功能： Oracle的在線重定義表功能二： 索引也可以進(jìn)展分區(qū)，分區(qū)索引有兩種類型：global和local。對(duì)于local索引，每一個(gè)表分區(qū)對(duì)應(yīng)一個(gè)索引分區(qū)，當(dāng)表的分區(qū)發(fā)生變化時(shí)，索引的維護(hù)由Oracle自動(dòng)進(jìn)展。對(duì)于global索引，可以選擇能否

13、分區(qū)，而且索引的分區(qū)可以不與表分區(qū)相對(duì)應(yīng)。當(dāng)對(duì)分區(qū)進(jìn)展維護(hù)操作時(shí)，通常會(huì)導(dǎo)致全局索引的INVALDED，必需在執(zhí)行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES語句，可以使在進(jìn)展分區(qū)維護(hù)的同時(shí)重建全局索引。 全局索引可以包含多個(gè)分區(qū)的值 部分索引比全局索引容易管理,而全局索引比較快留意:不能為散列分區(qū) 或者 子分區(qū)創(chuàng)建全局索引Oracle的分區(qū)功能非常強(qiáng)大。不過用起來發(fā)現(xiàn)有兩點(diǎn)不大方便： 第一是曾經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過Oracle提供了在線重定義表的功能，可以經(jīng)過這種方式來完成普通表到分區(qū)表的轉(zhuǎn)化?？梢詤⒖歼@個(gè)例子： 第二點(diǎn)是假設(shè)

14、采用了local分區(qū)索引，那么在添加表分區(qū)的時(shí)候，索引分區(qū)的表空間是不可控制的。假設(shè)希望將表和索引的分區(qū)分開到不同的表空間且不同索引分區(qū)也分散到不同的表空間中，那么只能在添加分區(qū)后，對(duì)新增的分區(qū)索引單獨(dú)rebuild。Oracle最大允許存在多少個(gè)分區(qū)呢?我們可以從Oracle的Concepts手冊(cè)上找到這個(gè)信息，對(duì)于Oracle9iR2:Tables can be partitioned into up to 64,000 separate partitions.對(duì)于Oracle10gR2，Oracle加強(qiáng)了分區(qū)特性：Tables can be partitioned into up to 1024K-1 separate partitions. 關(guān)于何時(shí)應(yīng)該進(jìn)展分區(qū),Oracle有如下建議: Tables greater than 2GB should always be considered for par