oracle 分區(qū)表的建立方法（Method for establishing Oracle partition table）

1、oracle 分區(qū)表的建立方法（Method for establishing Oracle partition table）Oracle provides partitioning techniques to support VLDB (Very, Large, DataBase). The partition table partitions the different columns of the partition column into different partitions by judging the partition column. The partition is com

2、pletely transparent to the application.The partition table of Oracle can include multiple partitions, each of which is a separate segment (SEGMENT) that can be stored in separate table spaces. When querying, you can access the data in each partition by querying tables, or you can query the queries d

3、irectly by specifying partitions.Partitioning offers the following advantages:The possibility of data corruption is reduced by dispersing data into individual partitions;Individual partitions can be backed up and restored;You can map partitions to different physical disks to distract IO;Improve mana

4、geability, availability, and performance.Oracle provides the following types of partitions:Range partitioning (range);Hash partitioning (hash);List partition (list);Range hash complex partitioning (range-hash);Range list composite partition (range-list).Oracle common table there is no way to modify

5、the properties directly into the partition table, must be converted by rebuilding the way, there are three ways of high efficiency, and explain their characteristics.4m6Linux AllianceMethod 1: rebuild the partition table with the original table.Step:SQL, CREATE, TABLE, T (ID, NUMBER, PRIMARY, KEY, T

6、IME, DATE);Table created.SQL, INSERT, INTO, T, SELECT, ROWNUM, CREATED, FROM, DBA_OBJECTS;6264 rows have been created.SQL COMMIT;Submit completed.SQL, CREATE, TABLE, T_NEW (ID, TIME), PARTITION, BY, RANGE (TIME), 4m6Linux, union2 (PARTITION, P1, VALUES, LESS, THAN (TO_DATE (2004-7-1,YYYY-MM-DD), 4m6

7、Linux League3, PARTITION, P2, VALUES, LESS, THAN (TO_DATE (2005-1-1,YYYY-MM-DD), 4m6Linux League4, PARTITION, P3, VALUES, LESS, THAN (TO_DATE (2005-7-1,YYYY-MM-DD), 4m6Linux League5, PARTITION, P4, VALUES, LESS, THAN (MAXVALUE) 4m6Linux League6, AS, SELECT, ID, TIME, FROM, T;Table created.SQL, RENAM

8、E, T, TO, T_OLD;The table was renamed.SQL, RENAME, T_NEW, TO, T;The table was renamed.SQL, SELECT, COUNT (*) FROM, T;COUNT (*) 4m6Linux Alliance-4m6Linux AllianceSix thousand two hundred and sixty-fourSQL, SELECT, COUNT (*), FROM, T, PARTITION (P1);COUNT (*) 4m6Linux Alliance-4m6Linux AllianceZeroSQ

9、L, SELECT, COUNT (*), FROM, T, PARTITION (P2);COUNT (*) 4m6Linux Alliance-4m6Linux AllianceSix thousand two hundred and forty-sixSQL, SELECT, COUNT (*), FROM, T, PARTITION (P3);COUNT (*) 4m6Linux Alliance-4m6Linux AllianceEighteenAdvantages: the method is simple and easy to use, because the use of D

10、DL statements will not produce UNDO, and only a small amount of REDO, the efficiency is relatively high, and after the completion of the table, the data has been distributed to each partition.Deficiency: additional considerations are needed for data consistency. Because almost no way to ensure consi

11、stency through the manual locking T table, in the implementation of the CREATE TABLE statement and RENAME T_NEW TO T statement directly changes may be lost, if you want to ensure the consistency, need to check the data in executing the statement, and this price is relatively large. Also, access to T

12、 between two RENAME statements will fail.It is suitable for modifying tables that are not frequent, and the data in tables should not be too large when they are idle.Method two: using swap partitioning.Step:SQL, CREATE, TABLE, T (ID, NUMBER, PRIMARY, KEY, TIME, DATE);Table created.SQL, INSERT, INTO,

13、 T, SELECT, ROWNUM, CREATED, FROM, DBA_OBJECTS;6264 rows have been created.SQL COMMIT;Submit completed.SQL, CREATE, TABLE, T_NEW (ID, NUMBER, PRIMARY, KEY, TIME, DATE), PARTITION, BY, RANGE (TIME), 4m6Linux, union2 (PARTITION, P1, VALUES, LESS, THAN (TO_DATE (2005-7-1,YYYY-MM-DD), 4m6Linux League3,

14、PARTITION, P2, VALUES, LESS, THAN (MAXVALUE);表已創(chuàng)建。SQL ALTER TABLE t_new交換分區(qū)P1表T；表已更改。SQL 重命名為t_old；表已重命名。SQL 重命名t_new T；表已重命名。從t選擇select（*）；計(jì)數(shù)（*）4m6linux聯(lián)盟- 4m6linux聯(lián)盟六千二百六十四優(yōu)點(diǎn)：只是對(duì)數(shù)據(jù)字典中分區(qū)和表的定義進(jìn)行了修改，沒有數(shù)據(jù)的修改或復(fù)制，效率最高。如果對(duì)數(shù)據(jù)在分區(qū)中的分布沒有進(jìn)一步要求的話，實(shí)現(xiàn)比較簡(jiǎn)單。在執(zhí)行完重命名操作后，可以檢查t_old中是否存在數(shù)據(jù)，如果存在的話，直接將這些數(shù)據(jù)插入到T中，可以保證對(duì)T插入的

15、操作不會(huì)丟失。不足：仍然存在一致性問題，交換分區(qū)之后重命名t_new T之前，查詢、更新和刪除會(huì)出現(xiàn)錯(cuò)誤或訪問不到數(shù)據(jù)。如果要求數(shù)據(jù)分布到多個(gè)分區(qū)中，則需要進(jìn)行分區(qū)的分裂操作，會(huì)增加操作的復(fù)雜度，效率也會(huì)降低。適用于包含大數(shù)據(jù)量的表轉(zhuǎn)到分區(qū)表中的一個(gè)分區(qū)的操作。應(yīng)盡量在閑時(shí)進(jìn)行操作。方法三：Oracle9i以上版本，利用在線重定義功能步驟：創(chuàng)建表的SQL （編號(hào)主鍵，時(shí)間日期）；表已創(chuàng)建。SQL 插入T行號(hào)，創(chuàng)造了dba_objects；6264行已創(chuàng)建。提交；提交完成。SQL exec dbms_redefinition。can_redef_table（用戶，“T”，dbms_redefin

16、ition。cons_use_pk）；PL / SQL過程已成功完成。創(chuàng)建表的SQL t_new（編號(hào)主鍵，時(shí)間日期）按范圍分區(qū)（時(shí)間）4m6linux聯(lián)盟2（分區(qū)P1值小于（to_date（2004-7-1“YYYY-MM-DD”），4m6linux聯(lián)盟3分區(qū)P2的值小于（to_date（2005-1-1“YYYY-MM-DD”），4m6linux聯(lián)盟4分區(qū)P3值小于（to_date（2005-7-1“YYYY-MM-DD”），4m6linux聯(lián)盟5分區(qū)P4值小于（次）；表已創(chuàng)建。SQL exec dbms_redefinition。start_redef_table（用戶、“T”、“t_n

17、ew ，- 4m6linux聯(lián)盟的ID，時(shí)間，dbms_redefinition cons_use_pk）；PL / SQL過程已成功完成。SQL exec dbms_redefinition。finish_redef_table（yangtk”、“T”、“t_new”）；PL / SQL過程已成功完成。從t選擇select（*）；計(jì)數(shù)（*）4m6linux聯(lián)盟- 4m6linux聯(lián)盟六千二百六十四從T分區(qū)（P2）中選擇計(jì)數(shù)（*）；計(jì)數(shù)（*）4m6linux聯(lián)盟- 4m6linux聯(lián)盟六千二百四十六從t分區(qū)（P3）選擇select（*）；計(jì)數(shù)（*）4m6linux聯(lián)盟- 4m6linux聯(lián)盟十

18、八優(yōu)點(diǎn)：保證數(shù)據(jù)的一致性，在大部分時(shí)間內(nèi)，表T都可以正常進(jìn)行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強(qiáng)的靈活性，對(duì)各種不同的需要都能滿足。而且，可以在切換前進(jìn)行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。不足：實(shí)現(xiàn)上比上面兩種略顯復(fù)雜。適用于各種情況。這里只給出了在線重定義表的一個(gè)最簡(jiǎn)單的例子，詳細(xì)的描述和例子可以參考下面兩篇文章。Oracle的在線重定義表功能：/post/468/12855Oracle的在線重定義表功能（二）：/post/468/12962索引

19、也可以進(jìn)行分區(qū)，分區(qū)索引有兩種類型：全球和地方。對(duì)于當(dāng)?shù)厮饕?，每一個(gè)表分區(qū)對(duì)應(yīng)一個(gè)索引分區(qū)，當(dāng)表的分區(qū)發(fā)生變化時(shí)，索引的維護(hù)由Oracle自動(dòng)進(jìn)行。對(duì)于全球索引，可以選擇是否分區(qū)，而且索引的分區(qū)可以不與表分區(qū)相對(duì)應(yīng)。當(dāng)對(duì)分區(qū)進(jìn)行維護(hù)操作時(shí)，通常會(huì)導(dǎo)致全局索引的invalded，REBUILD must be executed after the operation has been completed. Oracle9i provides the UPDATE GLOBAL INDEXES statement that enables you to rebuild global indexes

20、 at the same time as partition maintenance is performed.Global indexes can contain values from multiple partitions, local indexes are easier to manage than global indexes, and global indexes are faster than 4m6Linux indexesNote: you cannot create global indexes for hash partitions or sub partitionsThe partitioning function of Oracle is powerful. But using it, finding two is not convenient:The first is the existing table, and there is no method