Oracle分区表 (Partition Table) 的创建及管理.doc

Oracle分区表 (Partition Table) 的创建及管理一、创建分区表ITPUB个人空间 VBX2.Q分区表分为四类：1、范围分区表 2、列表分区表 3、哈希分区表 4、组合分区表下面分别创建四类分区表。ITPUB个人空间Q$g&Tu+Vx!z&b;QX1、范围分区表 CREATE TABLE range_example(%sb-B _ BS0 range_key_column DATE,ITPUB个人空间:sc!_5N3 DATA VARCHAR2(20),8a%Az jLnDl0 ID integerITPUB个人空间w ?&ga z0t) PARTITION BY RANGE(range_key_column)ITPUB个人空间:e6?_#t CLL f(ITPUB个人空间 gsX P6w PARTITION part01 VALUES LESS THAN (TO_DATE(2008-07-1 00:00:00,yyyy-mm-dd hh24:mi:ss) TABLESPACE tbs01,8m!d QRKh0 PARTITION part02 VALUES LESS THAN (TO_DATE(2008-08-1 00:00:00,yyyy-mm-dd hh24:mi:ss) TABLESPACE tbs02,ITPUB个人空间e(N+jJU#yU PARTITION part03 VALUES LESS THAN (TO_DATE(2008-09-1 00:00:00,yyyy-mm-dd hh24:mi:ss) TABLESPACE tbs03ITPUB个人空间nNig6rKwx:_);2、列表分区表0ngld2Np,S0 CREATE TABLE list_example(ITPUB个人空间3m_ F9h2|E1w1W6 dname VARCHAR2(10),Hg hl+u%0 DATA VARCHAR2(20)ITPUB个人空间 b2xk6Cn)PARTITION BY LIST(dname)ITPUB个人空间y#j5_DjxD(ITPUB个人空间ux5L EN PARTITION part01 VALUES(ME,PE,QC,RD),ITPUB个人空间5R2k8Mpk)K PARTITION part02 VALUES(SMT,SALE).Ruf?,?D0);3、哈希分区表ITPUB个人空间 e? S9n2A9bTCREATE TABLE hash_example(ITPUB个人空间ad ?Z2U I+b-E hash_key_column DATE,ITPUB个人空间CCG*U/p-s.B6i:o2Q DATA VARCHAR2(20).D Bz38Z0) PARTITION BY HASH(hash_key_cloumn)ITPUB个人空间 TmJ_A(R(ITPUB个人空间,Rg8?bH8gO PARTITION part01 ,ITPUB个人空间9lmO B7a PARTITION part02ITPUB个人空间2vuE2Xp4D1Qp0J);4、组合分区表ITPUB个人空间J5l/7a;Yf.tCREATE TABLE range_hash_example(ITPUB个人空间8D Pom:OVrange_column_key DATE,k R:eeD+ta0hash_column_key INT,ITPUB个人空间H1N Jmd uYDATA VARCHAR2(20)ITPUB个人空间Yhuj*G Wz*b)ITPUB个人空间XcO0UxH6vyPARTITION BY RANGE(range_column_key)ITPUB个人空间O j64?;SUBPARTITION BY HASH(hash_column_key) SUBPARTITIONS 2ITPUB个人空间k q5g|W1v7Sp(ITPUB个人空间6L0B-h PARTITION part_1 VALUES LESS THAN (TO_DATE(2008-08-01,yyyy-mm-dd)(ITPUB个人空间+$C&Ev+Fs SUBPARTITION part_1_sub_1,v;w+F#Z:l2b6KHA0 SUBPARTITION part_1_sub_2,ITPUB个人空间h%R4c95Q+c U:rL)hT SUBPARTITION part_1_sub_34Ii2u r&oB0 ),i ?2jlj9Uu&o0 PARTITION part_2 VALUES LESS THAN (TO_DATE(2008-09-01,yyyy-mm-dd)(-5$i?1flQ0 SUBPARTITION part_2_sub_1,ITPUB个人空间,BS(Gtjm#A/Az SUBPARTITION part_2_sub_2F v;Z|.P y0 )R3C7h*U(70);J#?DkA;(F0-注 subpartitions 2 并不是指定subpartition的个数一定为2，实际上每个分区的子分区个数可以不同。subpartitions 关键字的作用到底是什么？如果不指定subpartition的具体明细，则系统按照subpartitions的值指定subpartition的个数生成子分区，名称由系统定义 。二、增加分区ITPUB个人空间P%fuLeA- range partitioned tableITPUB个人空间ls,O%gALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE(2008-10-1 00:00:00,yyyy-mm-dd hh24:mi:ss);rI9W2RF0ITPUB个人空间e)fd X-list partitioned tableFNZ)ipK Y0ALTER TABLE list_example ADD PARTITION part04 VALUES (TE);CQ1I3g2X|TC,l0d YJ$Z+O0-Adding Values for a List PartitionITPUB个人空间9W9|#df#Pkss C1rALTER TABLE list_example MODIFY PARTITION part04 ADD VALUES(MIS);ITPUB个人空间&X!FV#Ud I&QYFo;Z2Bw$W7y0-Dropping Values from a List PartitionITPUB个人空间ZE.lOjf.Y/U:TALTER TABLE list_example MODIFY PARTITION part04 DROP VALUES(MIS);,?LBVp!Fv02itHjre(0-hash partitioned tableITPUB个人空间;C Q+q KxR;QALTER TABLE hash_example ADD PARTITION part03;/j+pO,DuV03bFL0-增加subpartitionITPUB个人空间VGp$b+K2D FALTER TABLE range_hash_exampleMODIFYPARTITIONITPUB个人空间+e/wbh3l|part_1 ADD SUBPARTITION part_1_sub_4;注:hash partitioned table 新增partition时，现有表的中所有data都有重新计算hash值，然后重新分配到分区中。所以被重新分配的分区的 indexes需要rebuild 。ITPUB个人空间E4x,R(+v4_CITPUB个人空间/h.v6j:l)my三、删除分区ITPUB个人空间#X+uX|P &A You can drop partitions from range, list, or composite range-list partitioned tables. ALTER TABLE . DROP PARTITION part_name;For hash-partitioned tables, or hash subpartitions of range-hash partitioned tables, you must perform. a coalesce operation instead.ITPUB个人空间,b!,sMs XW- 减少hash 分区的个数，一次减少一个。不能指定减少partition的名称。+AIK7pY0ALTER TABLE hash_example COALESCE PARTITION ;ED O tflH DQ.R0-subpartition 的语法对于如下ITPUB个人空间1P8iaoV9g;5eALTER TABLE divingMODIFYPARTITION us_locationsITPUB个人空间 pu.Qn*;a/o9oc COALESCE SUBPARTITION;四、 分区合并ITPUB个人空间CVCY W1. 合并父分区ITPUB个人空间)r1rqc2K/p,WALTER TABLE range_exampleITPUB个人空间7D/JtY;c4cMERGE PARTITIONS part01_1,part01_2 INTO PARTITION part01ITPUB个人空间kyL%a,a9n )c)Y0TUPDATE INDEXES;ITPUB个人空间#/X ?G1C?q8t m如果省略update indexes子句的话，必须重建受影响的分区的index 。 ALTER TABLE range_example MODIFY PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;!S Hq2PR/wU1v;Y00ITPUB个人空间,q;r.a w$i;b2. 合并子分区p#ZT0A6A?(l0 ALTER TABLE composite_example!I/kw3 EZ9q0MERGE SUBPARTITIONS part_1_sub_2,part_1_sub_3 INTO SUBPARTITION part_1_sub_2GTI.y9T:j6u0UPDATE INDEXES;五、分割分区 hash partitions or subpartitions不能分割。如果指定的分割分区包含任何的数据时，对应的indexes可以被标识为UNUSABLE 。如果是一般的Heap Table，新的分区indexes为UNUSABLE ,所有gloabl indexes被标识为UNUSABLE 。如果是Index-organized table 新分区的indexes 为UNUSABLE，global indexes 仍然为USABLE。?.s9hG 0O01、range类型分区的分割?G6g;b$%T0 ALTER TABLE range_exampleG6MT Nt0mMDu*wq0SPLIT PARTITION part01)u* fE0 AT (TO_DATE(2008-06-01 00:00:00,yyyy-mm-dd hh24:mi:ss)O29hWU:n+Ne0INTO ( PARTITION part01_1,PARTITION part01_2ITPUB个人空间OEa/F#id b0u5);ITPUB个人空间 ld$P一个分区一次性只能分割成两个分区，at关键字后面指定的值为第一个分区的range范围，默认为less than 。2、list类型分区的分割eU p*g9_ Y,|6H0 ALTER TABLE list_example,b.IF*D9t:0SPLIT PARTITION part01 VALUES(ME,PE)Jj1A QaR?G0INTO ( PARTITION part01_1, PARTITION part01_21V/e|(yR5aO&0);3、Range_Hash类型分区的分割 新分区会对原有分区的subpartition做rehash的动作。如果在分割是指定subpartition的个数，则按新规则rehash subpartition，如果没有指定则保留原有subpartition的个数不变。 ALTER TABLE range_hash_example SPLIT PARTITION part_1ITPUB个人空间a4q+KVq P|FEzAT (TO_DATE(2008-07-01 00:00:00,yyyy-mm-dd hh24:mi:ss) INTO (ITPUB个人空间e zy.i i7Q PARTITION part_1_1 SUBPARTITIONS 2 STORE IN (tbs01,tbs02),ITPUB个人空间Oi!d4u&C PARTITION part_1_2ITPUB个人空间w.KJ.VH rv); subpartitions 2 - 指定新分区的subpartition的个数，store in 子句指定subpartition存储的tablespace 。六、Exchanging Partitions可以将分区表转换成非分区表，或者几种不同分区表之间的转换。U&aIY0如下：0k$B*bMT#J0 CREATE TABLE hash_part02 AS SELECT * FROM hash_example WHERE 1=2;+qi1fk$aT._y0ALTER TABLE hash_example EXCHANGE PARTITION part02 WITH TABLE hash_part02;这时，分区表hash_example中的part02分区的资料将被转移到hash_part02这个非分区表中。七、索引Vce4Qw;I0 在分区表上可以建立三种类型的索引：1、和普通表一样的全局索引。2、全局分区索引。3、本地分区索引。ITPUB个人空间;k0i.F*e;o/u3E它们之间的区别如下图示。以表range_example为例。ITPUB个人空间4GrDh%*g1.建立普通的索引ITPUB个人空间gKJNBY4fcreate index com_index_range_example_id on range_exa