oracle相关特性篇之分区表

1、的增加、查找、删除等(DML)一个简化后的小表称为一个单个的分区，SQL 的增加、查找、删除等(DML)一个简化后的小表称为一个单个的分区，SQL DML 售数据存放到 TBS_2002,表的管理，提高查询性能及 I/O 对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上然是。pctfree, pctused, and 特殊性：含有 LONG、LONGRAW 数据类型的表不能进行分.在分区级别启用一些数据管理：对分区装载数据、创建索引和导入导出数据等。提高查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。增强可用性：如果表的某个分区出现故障，表在其他分区

2、的数据仍然可用。节约 成本和时间：如果表的某个分在分区级别启用一些数据管理：对分区装载数据、创建索引和导入导出数据等。提高查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。节约 成本和时间：如果表的某个分区出现故障，只需要修复该分区即可均衡 I/O：将不同的到不同德磁盘以平衡 I/O，提高并发性当表大小超过2GB 大小且继续增需要将历史数据和当前的数据分开单独处理，比如历史数据仅仅需要只读，而当数据则实现 数据分布在不同介质复合分区方(一)、 范围分范围分区就是对数据表中的某个值的范围进(一)、 范围分范围分区就

3、是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数在哪个分区上。如根据序号分区，根据业的创建日期进行分区等比如用日期列做分区键，2010年1月这个分区可能包含分区键的数据- 于或者等于它的值都将被放到下一个分区。除了第一个分区以外的所有分区的最小值都一个分区VALUES N 指定的值最后一个分区可以指定 MAXVALUE 关键字，所有超过其他值和 NULL 值都将个分区1.创建范围分区表CREATETABLE( , , , , ty_sold, )PARTITIONBYRANGE( PARTITION sales_q1_2006 VALUES LESSN(TO_DATE(01-A

4、PR-2006,dd-MON-yyyy) TABLESPACE tsa, PARTITION sales_q2_2006 VALUES LESSN(TO_DATE(01-JUL-2006,dd-MON-yyyy) TABLESPACE tsb, PARTITION sales_q3_2006 VALUES LESSN(TO_DATE(01-OCT-2006,dd-MON-yyyy) TABLESPACE tsc, PARTITION sales_q4_2006 VALUES LESSN(TO_DATE(01-JAN-2007,dd-MON-yyyy) TABLESPACE tsd2.创建erv

5、al 分区表之前，分区必须是手工或过程预分配新分区erval oracle2.创建erval 分区表之前，分区必须是手工或过程预分配新分区erval oracle11gDBA会根能满列和已分配的分区自动判断新数据是否可到已存在的分区中的数据已存在的分区，系统自动分配一个新分区来存放分区减对分区的操作，保证了分区的准确安全性实验参考 TXTCREATE(,ty_sold ,)PARTITION BY RANGE (time_id) ERVAL(1,MONTH)( PARTITION p0 VALUES LESSN (TO_DATE(1-1-2008, DD-MM-YYYY), PARTITION

6、 p1 VALUES LESSN (TO_DATE(1-1-2009, DD-MM-YYYY), PARTITION p2 VALUES LESSN (TO_DATE(1-7-2009, DD-MM-YYYY), PARTITION p3 VALUES LESSN(TO_DATE(1-1-2010,DD-MM-YYYY);(二)、 列表分该分区的特点是某列(二)、 列表分该分区的特点是某列的值只有几个，基于这样的特可以采用列表分区比，“地区”列作为分区键，EastSalesRegion分区可能包含NewYorkFLT分区可以避免出现列表分区之外的分区，当分区键值和所有列表值不一样的时候，存入

7、DFULT分区。 CREATE TABLE sales_by_region (item#EGER, qtystore_name VARCHAR(30), se_codeVARCHAR(2), sale_date DATE)STORAGE(INITIAL10KNEXT20K)TABLESPACEtbs5 PARTITION BY LIST (s(PARTITIONSTORAGE (INITIAL 8M)TABLESPACEPARTITIONPARTITIONVALUESPARTITIONVALUESVALUES (NULL),PARTITIONVALUES CREATETABLEq1_sales

8、_by_region (deptno number,deptname varchar2(20), quarterly_salesnumber(10,2), e varchar2(2)PARTITIONBYLIST(PARTITION q1_northwest VALUES (OR, WA), PARTITION q1_southwest VALUES (AZ, UT,NM), PARTITIONq1_northeastVALUES (NY,VM,NJ), PARTITION q1_southeast VALUES (FL, GA),PARTITION q1_northcentral VALUE

9、S (SD, WI), PARTITIONq1_southcentralVALUES(OK,(三)、 Hash分区（散列分区哈希分区又叫散列分区。Oracle (三)、 Hash分区（散列分区哈希分区又叫散列分区。Oracle 散列分区为通过指定分来均匀分布数据的一种分区类型，因为通过在I/O 设备ID 散列地存放在(四)、 复合分CREATETABLEdept(deptnoNUMBER,deptnameVARCHAR(32) PARTITION BY HASH(deptno) PARTITIONS 16;CREATETABLEdept(deptnoNUMBER,deptnameVARCHAR

10、(32) STORAGE (INITIAL 10K)PARTITIONBY(PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2, PARTITIONp3TABLESPACEts1,PARTITIONp4TABLESPACE1. 复合范围范1. 复合范围范围分使用范围分区后再使用范围做子分区CREATETABLE(NUMBERNOT,DATENOT ,delivery_dateDATENOT,NUMBERNOT,sales_amount NUMBERNOT)PARTITION BY RANGE (order_date) SUBPART

11、ITIONBYRANGE(delivery_date)(PARTITIONp_2006_julVALUES (SUBPARTITIONp06_jul_eVALUES ,SUBPARTITIONp06_jul_aVALUES ,SUBPARTITIONp06_jul_lVALUESN),PARTITIONp_2006_augVALUES (SUBPARTITIONp06_aug_eVALUES ,SUBPARTITIONp06_aug_aVALUES ,SUBPARTITIONp06_aug_lVALUESN),PARTITIONp_2006_sepVALUES (SUBPARTITIONp06

12、_sep_eVALUES ,SUBPARTITIONp06_sep_aVALUES ,SUBPARTITIONp06_sep_lVALUESN),PARTITIONp_2006_octVALUES (SUBPARTITIONp06_oct_eVALUES ,SUBPARTITIONp06_oct_aVALUES ,SUBPARTITIONp06_oct_lVALUESN),PARTITIONp_2006_novVALUES (SUBPARTITIONp06_nov_eVALUES ,SUBPARTITIONp06_nov_aVALUES ,SUBPARTITIONp06_nov_lVALUES

13、N),PARTITIONp_2006_decVALUES (SUBPARTITIONp06_dec_eVALUES ,SUBPARTITIONp06_dec_aVALUES ,SUBPARTITIONp06_dec_lVALUESN)2. 复合范围哈希分2. 复合范围哈希分据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这用复合范围哈希分区就要CREATETABLE( , , , , ty_sold, )PARTITIONBYRANGE(time_id)SUBPARTITIONBYHASH(cust_id) SUBPARTITIONS 8 STORE IN (ts1, ts2, ts

14、3, ts4)(PARTITIONsales_q1_2006VALUES ,PARTITIONsales_q2_2006VALUES ,PARTITIONsales_q3_2006VALUES ,PARTITIONsales_q4_2006VALUES 3. 复合范围列3. 复合范围列表分根据范围分区后，将每个分区内的数据根据列表分布到数个子分区中CREATE TABLE quarterly_regional_sales (deptnonumber,item_novarchar2(20),txn_date date, txn_amount number, sevarchar2(2) TABLE

15、SPACE ts4PARTITION BY RANGE (txn_date) SUBPARTITIONBYLIST(PARTITION q1_1999 VALUES LESSN(TO_DATE(1-APR-1999,DD-MON-YYYY) (SUBPARTITION q1_1999_northwest VALUES (OR, WA),SUBPARTITIONq1_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq1_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q1_1999_southeast VALUES

16、 (FL, GA), SUBPARTITION q1_1999_northcentral VALUES (SD, WI), SUBPARTITION q1_1999_southcentral VALUES (OK, TX)PARTITION q2_1999 VALUES LESSN(TO_DATE(1-JUL-1999,DD-MON-YYYY) (SUBPARTITION q2_1999_northwest VALUES (OR, WA),SUBPARTITIONq2_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq2_1999_northeastVA

17、LUES(NY,VM,NJ), SUBPARTITION q2_1999_southeast VALUES (FL, GA), SUBPARTITION q2_1999_northcentral VALUES (SD, WI), SUBPARTITION q2_1999_southcentral VALUES (OK, TX)PARTITION q3_1999 VALUES LESSN(TO_DATE(1-OCT-1999,DD-MON-YYYY) (SUBPARTITION q3_1999_northwest VALUES (OR, WA),SUBPARTITIONq3_1999_south

18、westVALUES(AZ,UT,NM), SUBPARTITIONq3_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q3_1999_southeast VALUES (FL, GA), SUBPARTITION q3_1999_northcentral VALUES (SD, WI), SUBPARTITION q3_1999_southcentral VALUES (OK, TX)PARTITION q4_1999 VALUES LESSN(TO_DATE(1-JAN-2000,DD-MON-YYYY) (SUBPARTITION q4_199

19、9_northwest VALUES (OR, WA),SUBPARTITIONq4_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq4_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q4_1999_southeast VALUES (FL, GA), SUBPARTITION q4_1999_northcentral VALUES (SD, WI), SUBPARTITION q4_1999_southcentral VALUES (OK, TX)4. 复合列表范4. 复合列表范围分键为 country_i

20、d 做列表分区，子分区使用 order_id 做范围分区。CREATE TABLE quarterly_regional_sales (deptnonumber,item_novarchar2(20),txn_date date, txn_amount number, sevarchar2(2) TABLESPACE ts4PARTITION BY RANGE (txn_date) SUBPARTITIONBYLIST(PARTITION q1_1999 VALUES LESSN(TO_DATE(1-APR-1999,DD-MON-YYYY) (SUBPARTITION q1_1999_nor

21、thwest VALUES (OR, WA),SUBPARTITIONq1_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq1_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q1_1999_southeast VALUES (FL, GA), SUBPARTITION q1_1999_northcentral VALUES (SD, WI), SUBPARTITION q1_1999_southcentral VALUES (OK, TX)PARTITION q2_1999 VALUES LESSN(TO_D

22、ATE(1-JUL-1999,DD-MON-YYYY) (SUBPARTITION q2_1999_northwest VALUES (OR, WA),SUBPARTITIONq2_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq2_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q2_1999_southeast VALUES (FL, GA), SUBPARTITION q2_1999_northcentral VALUES (SD, WI), SUBPARTITION q2_1999_southcentr

23、al VALUES (OK, TX)PARTITION q3_1999 VALUES LESSN(TO_DATE(1-OCT-1999,DD-MON-YYYY) (SUBPARTITION q3_1999_northwest VALUES (OR, WA),SUBPARTITIONq3_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq3_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q3_1999_southeast VALUES (FL, GA), SUBPARTITION q3_1999_northcen

24、tral VALUES (SD, WI), SUBPARTITION q3_1999_southcentral VALUES (OK, TX)PARTITION q4_1999 VALUES LESSN(TO_DATE(1-JAN-2000,DD-MON-YYYY) (SUBPARTITION q4_1999_northwest VALUES (OR, WA),SUBPARTITIONq4_1999_southwestVALUES(AZ,UT,NM), SUBPARTITIONq4_1999_northeastVALUES(NY,VM,NJ), SUBPARTITION q4_1999_sou

25、theast VALUES (FL, GA), SUBPARTITION q4_1999_northcentral VALUES (SD, WI), SUBPARTITION q4_1999_southcentral VALUES (OK, TX)5. 复合列表哈5. 复合列表哈希分根据列表分区后，将每个分区内的数据散列到数个子分区中CREATETABLE(,account_number,)PARTITIONBYLISTSUBPARTITIONBYHASH(customer_id)SUBPARTITIONS8 ( PARTITION p_northwest VALUES (OR, WA),PA

26、RTITIONp_southwestVALUES(AZ,UT,PARTITIONp_northeastVALUES(NY,VM,PARTITIONp_southeastVALUES(FL,PARTITIONp_northcentralVALUES(SD,PARTITIONp_southcentralVALUES(OK,6. 复合列表列表分根据列表分区后，将每个分区内的数据根据列表分布到数个子分区中CREATETABLE6. 复合列表列表分根据列表分区后，将每个分区内的数据根据列表分布到数个子分区中CREATETABLE(,)sLIST BYLIST(PARTITIONp_northwestVA

27、LUES(OR,(,)SUBPARTITIONp_nw_badVALUESSUBPARTITIONp_nw_averageVALUES(A) SUBPARTITION p_nw_good VALUES (G),PARTITIONp_southwestVALUES(AZ,UT,(,)SUBPARTITIONp_sw_badVALUESSUBPARTITIONp_sw_averageVALUES(A) SUBPARTITION p_sw_good VALUES (G),PARTITIONp_northeastVALUES(NY,VM,(,)SUBPARTITIONp_ne_badVALUESSUB

28、PARTITIONp_ne_averageVALUES(A) SUBPARTITION p_ne_good VALUES (G),PARTITIONp_southeastVALUES(FL,(,)SUBPARTITIONp_se_badVALUESSUBPARTITIONp_se_averageVALUES(A) SUBPARTITION p_se_good VALUES (G),PARTITIONp_northcentralVALUES(SD,(,)SUBPARTITIONp_nc_badVALUESSUBPARTITIONp_nc_averageVALUES(A) SUBPARTITION

29、 p_nc_good VALUES (G),PARTITIONp_southcentralVALUES(OK,(,SUBPARTITIONp_sc_badVALUESSUBPARTITIONp_sc_averageVALUES(A) SUBPARTITION p_sc_good VALUES (G)(一)、添加分区(ADD -给范围分(一)、添加分区(ADD -给范围分区表添加分以下代码给 SALES 表添加了一个 jan99分注意：以上添加的分区界限应该高于最后一个分区界限。如果想在第一个或者中间的分区上添加新的分区，应该使用 spt 分区。-给哈希分区表添加分-给列表分区表添加分ALTER

30、TABLEADD PARTITION q1_nonmainland VALUES (HI, PR) STORAGE(INITIAL20KNEXT20K)TABLESPACEtbs_3 ALTERTABLEscubagearADDALTERTABLEADDPARTITIONp_namedTABLESPACEALTERTABLEADD PARTITION jan99 VALUES LESSN(01-FEB-1999) TABLESPACE tsx;(二)、删除分区(二)、删除分区(DROP -有全局索引的时候删除一个分如果表创建了分区，如果要删除数据文件（表空间文件），则要先删除分区，然后才删除数

31、据文件-先删除数据后更新索ALTERTABLEsalesDROPPARTITIONALTERINDEXsales_area_ix-先更新索引后删除数DELETE FROM sales partition (dec98); ALTERTABLEsalesDROPPARTITIONdec98;-一边删除数据一边更新索ALTERTABLEsalesDROPPARTITIONdec98 UPDATE INDEXES;-删erval 分ALTERTABLEsalesDROPPARTITIONFOR(TO_DATE(01-SEP-2007,dd-MON-删除子分ALTERTABLESALESDROPSUB

32、PARTITION(三)、转换分区(EXCHANGE 转换分区可以讲一个分区（或者子分区）成一个分区（或子分区）(三)、转换分区(EXCHANGE 转换分区可以讲一个分区（或者子分区）成一个分区（或子分区）也可以将一个哈希分区表转换成一个复合*-hash 分区表，或者将一个复合*-hash 分区range 或list 分区表转换成复合*-range 或者*-list分区表，或者将复合*-range 或者*-list 分区表的分区转换成 range 或者 list 分区表。它通过更新数据字典来实现分区表与普通表的转换，所以速度非常快；如果需要更新 ALTER TABLE stocksEXCHAN

33、GEPARTITIONp3WITHTABLELOCK PARTITIONFOR(TO_DATE(01-JUN-2007,dd-MON-yyyy) IN SHARE MODE;ALTEREXCHANGEPARTITIONFOR(TO_DATE(01-JUN-2007,dd-MON-WITH TABLEINCLUDING INDEXES;CREATETABLEt1(iNUMBER,jNUMBER) PARTITION BY HASH(i)(PARTITIONp1,PARTITIONCREATETABLEt2(iNUMBER,jNUMBER) PARTITION BY RANGE(j) SUBPAR

34、TITION BY HASH(i)(PARTITION p1 VALUES LESSN(10) SUBPARTITION t2_pls1 SUBPARTITION t2_pls2,PARTITION p2 VALUES LESSN(20) SUBPARTITION t2_p2s1 SUBPARTITION t2_p2s2);ALTERTABLEt2EXCHANGEPARTITIONp1WITHTABLEt1 WITH VALIDATION;-转换一个子分区和*-HASH ALTERTABLEsalesEXCHANGESUBPARTITIONWITHTABLEq3_1999INCLUDING-转

35、换一个列表分区表和一个*-LIST -转换一个子分区和*-HASH ALTERTABLEsalesEXCHANGESUBPARTITIONWITHTABLEq3_1999INCLUDING-转换一个列表分区表和一个*-LIST 分(,)PARTITIONBYLIST(,poorVALUESmediocreVALUES(C) good VALUES (G)excellentVALUES(,)PARTITIONBY LISTBYLIST(,)poorVALUESmediocreVALUES(C) good VALUES (G)excellentVALUES(PARTITIONamericasVAL

36、UES,emeaapacALTEREXCHANGEPARTITIONWITHWITHTABLE(四)、截断分区(TRUNCATE(四)、截断分区(TRUNCATE 据不能截断一个索引分区。如果一个表上定义了本地索引， 使用 ALTER TABLE TRUNCATE PARTITION 子句将截断对应的分区和对应索引如果截断分区的时候需要使全局索引可用，需要添加 UPDATE INDEXES 子句-截断表分区的数据和全局索-先截断表分区后更新索ALTERTABLEsalesTRUNCATEPARTITIONALTERINDEXsales_area_ix-先更新索引后截断表分DELETEFROMs

37、alesPARTITIONALTERTABLEsalesTRUNCATEPARTITION-一边截断表分区一边更新索ALTERTABLEsalesTRUNCATEPARTITIONdec98 UPDATE INDEXES;(五)、合并分区(五)、合并分区(MERGE 合并分区不能HASH 分区表或者HASH 分区或者*-HASH 子分区CREATETABLEfour_seasons onetwothree)PARTITION BYRANGE(one) PARTITIONVALUESN(TO_DATE(01-apr-1998,dd-mon-PARTITION quarter_twoVALUESN

38、(TO_DATE(01-jul-1998,dd-mon-PARTITION quarter_threeVALUESN(TO_DATE(01-oct-1998,dd-mon-PARTITION quarter_fourVALUESN(TO_DATE(01-jan-1999,dd-mon-TABLESPACECREATEINDEXi_four_seasons_lONfour_seasons(one,two) LOCAL (PARTITION i_quarter_one TABLESPACE i_quarter_one, PARTITION i_quarter_two TABLESPACE i_qu

39、arter_two, PARTITIONi_quarter_threeTABLESPACEi_quarter_three, PARTITION i_quarter_four TABLESPACE i_quarter_four);ALTERTABLEMERGE PARTITIONS quarter_one, quarter_twoOPARTITIONquarter_two UPDATE INDEXES;-如果不实用 UPDATE INDEXES 子ALTER TABLE four_seasons MODIFY PARTITION quarter_twoREBUILDUNUSABLELOCALIN

40、DEXES;-合并间隔分TABLE(,)PARTITIONBYERVAL(p_before_2007-合并间隔分TABLE(,)PARTITIONBYERVAL(p_before_2007VALUES (1,TO_DATE(15-JAN-2007,dd-MON-(2,TO_DATE(16-JAN-2007,dd-MON-(3,TO_DATE(30-JAN-2007,dd-MON-OALTER ,FOR(TO_DATE(16-JAN-2007,dd-MON-合并列表分ALTERTABLEMERGEPARTITIONSq1_northcentral,q1_southcentral O PARTIT

41、ION q1_centralSTORAGE(MAXEXTENTSALTERTABLEMERGEPARTITIONSquarter_1,quarter_2 SUBPARTITIONS 8;OPARTITION-合并*-LIST 分ALTERTABLEMERGESUBPARTITIONSq1_1999_northwest,q1_1999_southwest O SUBPARTITION q1_1999_westTABLESPACEALTERTABLEMERGEPARTITIONSFOR(TO_DATE(01-MAR-2007,dd-MON-yyyy), OPARTITION(六)、接合分区(COA

42、LESCA (六)、接合分区(COALESCA 接合分区是将 HASH HASH (七)、重命名分区(RNAME 重命名分区可以重命名表或索引的分区或子分区-重命名一个表ALTERTABLEscubagearRENAMEPARTITIONsys_p636-重命名一个表子分ALTERTABLE.RENAMESUBPARTITION-重命名一个索引分ALTERINDEX.RENAMEPARTITION-重命名一个分ALTERINDEXscubaRENAMESUBPARTITIONsys_subp3254TO-接合 HASH 分区（减少一个分区ALTER TABLECOALESCE-接合一个*-HA

43、SH 分区表的子分ALTERTABLEdivingMODIFYPARTITIONus_locations -接合一个全局哈希索ALTERINDEXhgidxCOALESCE(八)、拆分分区(八)、拆分分区(SPLIT HASH -拆分一个范围分区表的分ALTERTABLEvet_catsSPLITfee_katy at (100)O(PARTITION fee_katy1, PARTITION fee_katy2);ALTERINDEXJAF1REBUILDPARTITIONfee_katy1; ALTERINDEXJAF1REBUILDPARTITIONALTERTABLESPLITPARTITIONregion_eastVALUES(CT,MA,MD) (PARTITIONregion_east_1 TABLESPACE tbs2,PARTITIONregion_east_2 STORAGE(INITIALPARALLELALTER TABLE tranSPLITPARTITIONFOR(TO_DATE(01-MAY-2007,dd-MON-yyy