Oracle分区表详细解PPT课件

.,1,Oracle数据库的表分区,.,2,分区表的由来,当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。从应用程序的角度来看，分区后的表与非分区表完全相同，使用SQLDML命令访问分区后的表时，无需任何修改。,.,3,分区表概述,大数据对象（表、索引）被分成小物理段当分区表建立时，记录基于分区字段值被存储到相应分区分区字段值可以修改.(rowmovementenabled)分区可以存储在不同的表空间分区可以有不同的物理存储参数分区支持IOT表、对象表、LOB字段Varrays等,.,4,什么时候使用分区表,表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要,.,5,性能Select和DML只访问指定分区,提高检索速度并行DML操作Partitionwisejoin可管理性历史数据清除提高备份性能指定分区的数据维护操作可用性将故障局限在分区中缩短恢复时间分区目标优先级高性能-数据维护能力-实施难度-高可用性(故障屏蔽能力）,使用分区表的好处,.,6,分区表的分类,1、范围分区(Range)2、列表分区(List)3、散列分区(Hash)4、组合范围散列分区5、复合范围散列分区,.,7,范围分区,范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。如：将销售数据按照月份进行分区,.,8,范围分区时的规则,使用范围分区时，需要注意以下几个规则1、每一个分区都必须有一个VALUESLESSTHEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。3、在最高的分区中定义了MAXVALUE。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUELESSTHEN的值，同时包括空值。,.,9,范围分区例1,假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘,.,10,CREATETABLECUSTOMER(CUSTOMER_IDNUMBERNOTNULLPRIMARYKEY,FIRST_NAMEVARCHAR2(30)NOTNULL,LAST_NAMEVARCHAR2(30)NOTNULL,PHONEVARCHAR2(15)NOTNULL,EMAILVARCHAR2(80),STATUSCHAR(1)PARTITIONBYRANGE(CUSTOMER_ID)(PARTITIONCUS_PART1VALUESLESSTHAN(100000)TABLESPACECUS_TS01,PARTITIONCUS_PART2VALUESLESSTHAN(200000)TABLESPACECUS_TS02),.,11,按时间划分CREATETABLEORDER_ACTIVITIES(ORDER_IDNUMBER(7)NOTNULL,ORDER_DATEDATE,TOTAL_AMOUNTNUMBER,CUSTOTMER_IDNUMBER(7),PAIDCHAR(1)PARTITIONBYRANGE(ORDER_DATE)(PARTITIONORD_ACT_PART01VALUESLESSTHAN(TO_DATE(01-MAY-2003,DD-MON-YYYY)TABLESPACEORD_TS01,PARTITIONORD_ACT_PART02VALUESLESSTHAN(TO_DATE(01-JUN-2003,DD-MON-YYYY)TABLESPACEORD_TS02,PARTITIONORD_ACT_PART02VALUESLESSTHAN(TO_DATE(01-JUL-2003,DD-MON-YYYY)TABLESPACEORD_TS03),.,12,MAXVALUECREATETABLERangeTable(iddINTPRIMARYKEY,iNAMEVARCHAR(10),gradeINT)PARTITIONBYRANGE(grade)(PARTITIONpart1VALUESLESSTHEN(1000)TABLESPACEPart1_tb,PARTITIONpart2VALUESLESSTHEN(MAXVALUE)TABLESPACEPart2_tb);,.,13,范围分区的特点,最早、最经典的分区方法Range分区通过对分区字段值的范围进行分区Range分区特别适合于按时间周期进行数据的存储：日、周、月、年等数据管理能力强数据迁移数据备份数据交换范围分区的数据可能不均匀范围分区与记录值有关，实施难度和可维护性相对较差,.,14,列表(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);,.,15,CREATETABLEListTable(idINTPRIMARYKEY,nameVARCHAR(20),areaVARCHAR(10)PARTITIONBYLIST(area)(PARTITIONpart1VALUES(guangdong,beijing)TABLESPACEPart1_tb,PARTITIONpart2VALUES(shanghai,nanjing)TABLESPACEPart2_tb);),.,16,列表分区的特点,List分区通过对分区字段的离散值进行分区List分区是不排序的，而且分区之间也没有关联List分区适合于对数据离散值进行控制List分区只支持单个字段List分区具有与range分区相似的优缺点：数据管理能力强各分区的数据可能不均匀,.,17,散列(hash)分区,这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。,.,18,例一：CREATETABLEHASH_TABLE(COLNUMBER(8),INFVARCHAR2(100)PARTITIONBYHASH(COL)(PARTITIONPART01TABLESPACEHASH_TS01,PARTITIONPART02TABLESPACEHASH_TS02,PARTITIONPART03TABLESPACEHASH_TS03),.,19,简写：CREATETABLEemp(empnoNUMBER(4),enameVARCHAR2(30),salNUMBER)PARTITIONBYHASH(empno)PARTITIONS8STOREIN(emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);,.,20,hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。,.,21,Hash分区的特点,基于分区字段的hash值，自动将记录插入到相应分区分区一般是2的幂易于实施总体性能最佳适合于静态数据Hash分区适合于数据的均匀存储Hash分区特别适合于PDML和Partition-wisejoin支持（hash）local/globalindexes数据管理能力强Hash分区对数据值无法控制,.,22,组合(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);,.,23,组合分区的特点,Oracle支持的Composite分区：range-hashRange-list既适合于历史数据，又适合于数据均匀分布与范围分区一样提高可用性和可管理性更好的PDML和partition-wisejoins特性实现粒度更细的操作支持符合localindexes不支持符合globalindexes,.,24,组合范围散列分区,这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区，分区之中的分区被称为子分区。,.,25,createtabledinya_test(transaction_idnumberprimarykey,item_idnumber(8)notnull,item_descriptionvarchar2(300),transaction_datedate)partitionbyrange(transaction_date)subpartitionbyhash(transaction_id)subpartitions3storein(dinya_space01,dinya_space02,dinya_space03)(partitionpart_01valueslessthan(to_date(2006-01-01,yyyy-mm-dd),partitionpart_02valueslessthan(to_date(2010-01-01,yyyy-mm-dd),partitionpart_03valueslessthan(maxvalue);,.,26,组合范围列表分区,这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区,.,27,CREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATE,SALES_COSTNUMBER(10),STATUSVARCHAR2(20)PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYLIST(STATUS)(PARTITIONP1VALUESLESSTHAN(TO_DATE(2003-01-01,YYYY-MM-DD)TABLESPACErptfact2009(SUBPARTITIONP1SUB1VALUES(ACTIVE)TABLESPACErptfact2009,SUBPARTITIONP1SUB2VALUES(INACTIVE)TABLESPACErptfact2009),PARTITIONP2VALUESLESSTHAN(TO_DATE(2003-03-01,YYYY-MM-DD)TABLESPACErptfact2009(SUBPARTITIONP2SUB1VALUES(ACTIVE)TABLESPACErptfact2009,SUBPARTITIONP2SUB2VALUES(INACTIVE)TABLESPACErptfact2009),.,28,如何选定不同类型的分区表,何时选择范围分区必须可以将表的记录按照某一列值的范围进行划分。你想处理一些数据，这些数据经常是属于某一个范围内，例如月份。如果数据能够按照分区的范围均匀分布的话，那会获得最佳性能。如果数据分布很不均匀的话，你可能不得不选择其他分区方式。,.,29,何时选择HASH分区,如果数据不容易用范围分区，但你想提升性能和表的易管理性。Hash分区提供了一个在指定数量的分区内交叉均匀分布数据的方法。行根据分区键的hash值映射到相应分区中。创建和使用hash分区你可以灵活放置数据，可以通过交叉访问在不同I/O设备上的分区提升性能。CREATETABLEscubagear(idNUMBER,nameVARCHAR2(60)PARTITIONBYHASH(id)PARTITIONS4STOREIN(gear1,gear2,gear3,gear4);,.,30,何时选择列表分区,使用LIST分区你可以直接控制某些数据映射到某些分区。你可以为某个分区指定不连续的分区键值。这不同于RANGE分区（用键值的范围划分分区），也不同于HASH分区（不能控制某行映射到哪个分区）。,.,31,不同类型的分区表对性能的影响,关于范围分区Range：要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。,.,32,不同类型的分区表对性能的影响,关于散列分区Hash：如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。,.,33,关于列表分区List：当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。,.,34,不同类型的分区表对性能的影响,关于组合范围-散列分区：范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。,.,35,不同类型的分区表对性能的影响,关于组合范围-列表分区：范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。创建或更改分区表时可以指定行移动子句，即ENABLEROWMOVEMENT或者DISABLEROWMOVEMENT，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLEROWMOVEMENT。,.,36,分区索引,分区表索引的分类LocalprefixedindexLocalnon-prefixedindexGlobalprefixedindexNonpartitionindexGlobalindex的分区不同于表分区Local索引的分区与表分区相同Anindexisprefixedifitispartitionedonaleftprefixoftheindexcolumns分区表上的非分区索引等同于global索引,.,37,分区索引,Globalindex必须是范围分区9i以前Globalindex可以是hash分区10g新特性Globalindex不支持bitmapindexUniqueindex必须是prefixed，或者包含分区字段Localindex（non-prefixed,non-unique)可以不包含分区字段,.,38,分区索引示例,CREATEINDEXmonth_ixONsales(sales_month)GLOBALPARTITIONBYRANGE(sales_month)(PARTITIONpm1_ixVALUESLESSTHAN(2)PARTITIONpm2_ixVALUESLESSTHAN(3)PARTITIONpm9_ixVALUESLESSTHAN(10)PARTITIONpm10_ixVALUESLESSTHAN(11)PARTITIONpm11_ixVALUESLESSTHAN(12)PARTITIONpm12_ixVALUESLESSTHAN(MAXVALUE);,.,39,分区表索引的使用,OLTP系统中的建议Global和uniquelocalindex性能优于nonuniquelocalindexLocalindex提供了更好的可用性决策支持系统中的建议Localindex更适合于数据装载和分区维护在大量数据统计时，能充分利用localindex的并行查询能力在性能、高可用和可管理性之间进行平衡,.,40,分区索引策略选择,索引字段是表分区字段的前缀？,Localprefixed,yes,该非分区字段是唯一索引？,globalprefixed,yes,是否性能在可承受范围，而分区的可管理性、可用性更重要？,Localnon-prefixed,是数据仓库/交易系统？,yes,Localnon-prefixed,Globalprefixed,DSS,OLTP,NO,NO,NO,.,41,分区表设计原则,表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要数据备份和恢复：按时间周期进行表空间备份时，将分区与表空间建立对应关系