Oracle_的分区技术

Oracle性能调整Oracle的分区技术分区技术内容什么是分区?分区的好处？如何实施分区？如何评估分区的效果？分区概述大数据对象（表、索引）被分成小物理段当分区表建立时，记录基于分区字段值被存储到相应分区分区字段值可以修改.(rowmovementenabled)分区可以存储在不同的表空间分区可以有不同的物理存储参数分区支持IOT表、对象表、LOB字段Varrays等性能Select和DML只访问指定分区并行DML操作Partition－wisejoin可管理性历史数据清除提高备份性能指定分区的数据维护操作可用性将故障局限在分区中缩短恢复时间分区目标优先级高性能-数据维护能力-实施难度-高可用性(故障屏蔽能力）分区技术的效益和目标分区方法分区方法Rangepartition---8Hashpartition---8iListpartition---9iCompositepartition-8iRange分区示例CREATETABLEsales_range(salesman_idNUMBER(5),salesman_nameVARCHAR2(30),sales_amountNUMBER(10),sales_dateDATE)COMPRESSPARTITIONBYRANGE(sales_date)(PARTITIONsales_jan2000VALUESLESSTHAN(TO_DATE('02/01/2000','DD/MM/YYYY')),PARTITIONsales_feb2000VALUESLESSTHAN(TO_DATE('03/01/2000','DD/MM/YYYY')),PARTITIONsales_mar2000VALUESLESSTHAN(TO_DATE('04/01/2000','DD/MM/YYYY')),PARTITIONsales_apr2000VALUESLESSTHAN(TO_DATE('05/01/2000','DD/MM/YYYY')));分区字段Valueslessthan必须是确定值，最后一个可以是maxvalue每个分区可以单独指定物理属性Range分区的特点最早、最经典的分区方法Range分区通过对分区字段值的范围进行分区Range分区特别适合于按时间周期进行数据的存储：日、周、月、年等数据管理能力强数据迁移数据备份数据交换范围分区的数据可能不均匀范围分区与记录值有关，实施难度和可维护性相对较差Hash分区CREATETABLEsales_hash(salesman_idNUMBER(5),salesman_nameVARCHAR2(30),sales_amountNUMBER(10),week_noNUMBER(2))PARTITIONBYHASH(salesman_id)PARTITIONS4;Hash分区的特点基于分区字段的hash值，自动将记录插入到相应分区分区一般是2的幂易于实施总体性能最佳适合于静态数据Hash分区适合于数据的均匀存储Hash分区特别适合于PDML和Partition-wisejoin支持（hash）localindexes9i不支持hashglobalindexes，10g开始支持数据管理能力强Hash分区对数据值无法控制List分区CREATETABLEsales_list(salesman_idNUMBER(5),salesman_nameVARCHAR2(30),sales_stateVARCHAR2(20),sales_amountNUMBER(10),sales_dateDATE)PARTITIONBYLIST(sales_state)(PARTITIONsales_westVALUES('California','Hawaii'),PARTITIONsales_eastVALUES('NewYork','Virginia','Florida'),PARTITIONsales_centralVALUES('Texas','Illinois'),PARTITIONsales_otherVALUES(DEFAULT));List分区的特点List分区通过对分区字段的离散值进行分区List分区是不排序的，而且分区之间也没有关联List分区适合于对数据离散值进行控制List分区只支持单个字段List分区具有与range分区相似的优缺点：数据管理能力强各分区的数据可能不均匀Composite分区CREATETABLEquarterly_regional_sales(deptnoNUMBER,item_noVARCHAR2(20),txn_dateDATE,txn_amountNUMBER,stateVARCHAR2(2))PARTITIONBYRANGE(txn_date)SUBPARTITIONBYLIST(state)(PARTITIONq1_1999VALUESLESSTHAN(TO_DATE('1-APR-1999','DD-MON-YYYY'))(SUBPARTITIONq1_1999_northwestVALUES('OR','WA'),SUBPARTITIONq1_1999_southwestVALUES('AZ','UT','NM'),SUBPARTITIONq1_1999_northeastVALUES('NY','VM','NJ'),SUBPARTITIONq1_1999_southeastVALUES('FL','GA'),SUBPARTITIONq1_1999_northcentralVALUES('SD','WI'),SUBPARTITIONq1_1999_southcentralVALUES('NM','TX')),PARTITIONq2_1999VALUESLESSTHAN(TO_DATE('1-JUL-1999','DD-MON-YYYY'))(SUBPARTITIONq2_1999_northwestVALUES('OR','WA'),SUBPARTITIONq2_1999_southwestVALUES('AZ','UT','NM'),SUBPARTITIONq2_1999_northeastVALUES('NY','VM','NJ'),SUBPARTITIONq2_1999_southeastVALUES('FL','GA'),SUBPARTITIONq2_1999_northcentralVALUES('SD','WI'),SUBPARTITIONq2_1999_southcentralVALUES('NM','TX')),PARTITIONq3_1999VALUESLESSTHAN(TO_DATE('1-OCT-1999','DD-MON-YYYY'))(SUBPARTITIONq3_1999_northwestVALUES('OR','WA'),…….SUBPARTITIONq4_1999_northcentralVALUES('SD','WI'),SUBPARTITIONq4_1999_southcentralVALUES('NM','TX')));Composite分区的特点Oracle支持的Composite分区：range-hashRange-list既适合于历史数据，又适合于数据均匀分布与范围分区一样提高可用性和可管理性更好的PDML和partition-wisejoins特性实现粒度更细的操作支持符合localindexes不支持符合globalindexes分区索引分区表索引的分类LocalprefixedindexLocalnon-prefixedindexGlobalprefixedindexNonpartitionindexGlobalindex的分区不同于表分区Local索引的分区与表分区相同Anindexisprefixedifitispartitionedonaleftprefixoftheindexcolumns分区表上的非分区索引等同于global索引分区索引Globalindex必须是范围分区－－－9i以前Globalindex可以是hash分区－－－10g新特性Globalindex不支持bitmapindexUniqueindex必须是prefixed，或者包含分区字段Localindex（non-prefixed,non-unique)可以不包含分区字段分区索引示例CREATEINDEXmonth_ixONsales(sales_month)GLOBALPARTITIONBYRANGE(sales_month)(PARTITIONpm1_ixVALUESLESSTHAN(2)PARTITIONpm2_ixVALUESLESSTHAN(3)PARTITIONpm3_ixVALUESLESSTHAN(4)PARTITIONpm4_ixVALUESLESSTHAN(5)PARTITIONpm5_ixVALUESLESSTHAN(6)PARTITIONpm6_ixVALUESLESSTHAN(7)PARTITIONpm7_ixVALUESLESSTHAN(8)PARTITIONpm8_ixVALUESLESSTHAN(9)PARTITIONpm9_ixVALUESLESSTHAN(10)PARTITIONpm10_ixVALUESLESSTHAN(11)PARTITIONpm11_ixVALUESLESSTHAN(12)PARTITIONpm12_ixVALUESLESSTHAN(MAXVALUE));分区表索引的使用OLTP系统中的建议Global和uniquelocalindex性能优于nonuniquelocalindexLocalindex提供了更好的可用性决策支持系统中的建议Localindex更适合于数据装载和分区维护在大量数据统计时，能充分利用localindex的并行查询能力在性能、高可用和可管理性之间进行平衡分区索引策略选择索引字段是表分区字段的前缀？Localprefixedyes该非分区字段是唯一索引？globalprefixedyes是否性能在可承受范围，而分区的可管理性、可用性更重要？Localnon-prefixed是数据仓库/交易系统？yesLocalnon-prefixedGlobalprefixedDSSOLTPNONONO分区表设计原则表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要数据备份和恢复：按时间周期进行表空间备份时，将分区与表空间建立对应关系只读数据：如果一个表中大部分数据都是只读，通过对表进行分区，可将只读数据存储在只读表空间，对于数据的备份有利OLAP并行数据操作分区表的管理功能分区的增加分区的删除分区的合并merge分区的清空truncate分区的交换exchange分区的压缩coalese分区的移动move分区的分离split修改分区的物理属性分区的更名rename分区交换功能通过交换数据段，实现分区和非分区表的数据交换，以及子分区和分区表的数据交换非常快捷的数据移动方式。特别是没有validation和索引维护操作时Local索引自动维护Global索引必须重建分区交换的应用ETL在源系统中，将需要抽取的数据以如下语句形式抽取到建立在单独表空间的中间表上：Createtable…asselect…insert/*+append*/asselect