Oracle中使用分区表技术

Oracle中使用分区表技术主讲人：倪畅分区表什么是分区表？将大的数据集拆成更小，可管理的数据块的策略每个单独的块就是一个分区分区上的数据处理相互独立DBA工作拆分到分区上采用拆分－各个击破的策略来管理日益增大的数据主要用于支持VLDB(VeryLargeDataBase)。分区表为什么要对表进行分区？由于将数据分散到各个分区中，减少了数据损坏的可能性;可以将分区映射到不同的物理磁盘上，来分散IO；可以对单独的分区进行备份和恢复；提高可管理性、可用性和性能。分区表分区表特点分区表主要支持比较大规模的应用系统，它有以下的特点一个分区表有一个或多个分区，每个分区存储按照范围，散列，列表或复合进行分区后的数据分区表的每个分区是一个段并可分配在不同的表空间分区对于大型表的并发查询和操作是非常大的用处管理分区需要特殊的命令分区表不支持Long,LongRaw数据类型分区表类型分区方法有以下几种类型:范围(Range)散列（HASH）列表(List)复合(Composite)范围分区散列分区复合分区列表分区分区表类型（续）分区方法按照范围分区按照表中列值的范围将映射相应的行到不同的分区。例如按照月或年进行分区划分，该方法比较适合于数据历史表。但是,并不能总是预先知道每个范围有多少数据而导致各个分区的大小不一致，因此在某些操作上如并行的DML操作时，性能并不能达到最优。分区表类型（续）散列分区如果你的数据并不适合于进行范围分区，但你仍然想进行分区提高性能和管理，可以使用散列分区。该方法在分区列上使用散列功能把数据均匀地划分到每个分区中。通过固定的分区数控制数据的物理分布同时保证分区的大小一致。列表分区如果你的数据并不适合于进行范围分区，同时分区字段的取值范围只是一个包含少数值的集合，可以进行列表分区。指定分区列中一串离散的值列将行数据分配进相应的分区中列表分区不同于由范围值指定的范围分区,也不同于用户不能对行与分区之间的映射关系进行控制的散列分区.数据间没有关联，使用列表分区将它们分组或组织在一起。分区表类型（续)复合分区该方式使用三种分区类型的复合方式.这种类型支持在分区级上的历史数据操作及在子分区级上的并行DML操作。复合分区有以下两种类型：范围-散列复合分区:首先表通过按范围方法进行分区，每个分区再通过散列方式分为子分区范围-列表复合分区:首先表通过按范围方法进行分区，每个分区再通过列表方式分为子分区。创建范围分区表创建范围分区表创建范围分区时，你需要指定:分区方法：Range分区列指定分区范围条件说明下面例子创建一个有四个分区的表，每个分区为一季度的销售数据，其中列Sale_year,Sale_Month,Sale_Day为分区列。它们的值构成一个特殊的行的分区关键字。VALUESLESSTHEN子句描述了分区条件范围，行的关键字与分区条件相比较，小于分区条件中设置的值的行数据被存储在相应的分区中。每个分区给定一个名字（sales_q1,q2,q3,q4)。同时每个分区分别在独立的表空间中。创建范围分区表示例：CREATETABLEsales(invoice_noNUMBER,sale_yearINTNOTNULL,sale_monthINTNOTNULL,sale_dayINTNOTNULL)PARTITIONBYRANGE(sale_year,sale_month,sale_day)(PARTITIONsales_q1VALUESLESSTHAN(1999,04,01)TABLESPACEtsa,PARTITIONsales_q2VALUESLESSTHAN(1999,07,01)TABLESPACEtsb,PARTITIONsales_q3VALUESLESSTHAN(1999,10,01)TABLESPACEtsc,PARTITIONsales_q4VALUESLESSTHAN(2000,01,01)TABLESPACEtsd);创建分区表接上例。　　如果某行数据中的sale_year=1999,sale_month=8,sale_day=1，则该行会有分区关键字（1999,8,1)，并被保存在分区Sales_q3中。cust_ptn3:Sale_year<1999101Segment3cust_ptn2:sale_year<19990701Segment2cust_ptn1:sale_year<19990401Segment1创建散列分区表创建散列分区表创建散列分区时，你需要指定:分区方法：Hash分区列指定分区数量或单独的分区说明以下的例子创建散列分区表，分区列是id，并将创建由系统命名四个分区，同时被分别放入四个表空间中（gear1,gear2……）创建散列分区表示例:CREATETABLEscubagear(idNUMBER,nameVARCHAR2(60))PARTITIONBYHASH(id)PARTITIONS4STOREIN(gear1,gear2,gear3,gear4);gear2:Segment2gear1:Segment1gear3:Segment3gear4:Segment4创建列表分区创建列表分区表创建列表分区时，你需要指定:分区方法：List分区列指定分区描述：描述了每个分区条件的值的列表，每个值列是由分区列中的不连续的值组成，用于判断该行是否包含在分区中。下面的例子创建列表分区表，该表在state字段上进行区域分区。创建列表分区表示例：CREATETABLEq1_sales_by_region(deptnonumber,deptnamevarchar2(20),quarterly_salesnumber(10,2),statevarchar2(2))PARTITIONBYLIST(state)(PARTITIONq1_northwestVALUES('OR','WA'),PARTITIONq1_southwestVALUES('AZ','UT','NM'),PARTITIONq1_northeastVALUES('NY','VM','NJ'),PARTITIONq1_southeastVALUES('FL','GA'),PARTITIONq1_northcentralVALUES('SD','WI'),PARTITIONq1_southcentralVALUES('OK','TX'));创建列表分区接上例：某行分区列中的值与分区条件中的值列进行匹配，以判断放入哪个分区中。例如：(10,‘accounting’,100,‘WA’)对应于分区partitionq1_northwest(20,'R&D',150,'OR')对应于分区partitionq1_northwest(30,'sales',100,'FL')对应于分区partitionq1_southeast(40,'HR',10,'TX')对应于分区partitionq1_southwest(50,‘systemsengineering’,10,‘CA’)不对应于表中任何分区并返回错误可以指定DefaultPartition语句到上例中，以存放不符合其它分区条件的行数据。创建复合分区创建复合范围－散列分区表创建复合范围-散列分区时，你需要指定:分区方法：Range分区列指定分区范围条件说明子分区方法:Hash子分区列指定每个分区的子分区数量或子分区的描述下面的例子创建范围-散列分区表，指定三个范围分区，每个分区包含8个由系统命名的子分区，其中STOREIN子句将它们分别分布于4个表空间内。创建复合分区示例：CREATETABLEscubagear(equipnoNUMBER,equipnameVARCHAR(32),priceNUMBER)PARTITIONBYRANGE(equipno)SUBPARTITIONBYHASH(equipname)SUBPARTITIONS8STOREIN(ts1,ts2,ts3,ts4)(PARTITIONp1VALUESLESSTHAN(1000),PARTITIONp2VALUESLESSTHAN(2000),PARTITIONp3VALUESLESSTHAN(MAXVALUE));创建复合分区创建复合范围－列表分区表创建复合范围-列表分区时，你需要指定:分区方法：Range分区列指定分区范围条件说明子分区方法:List子分区列指定描述了每个子分区条件的值的列表，每个值列是由子分区列中的不连续的值组成，用于判断该行是否包含在子分区中。下面的例子显示了每个季度的产品销售数据，在每个季度中，根据区域位置进行分组创建复合分区示例：CREATETABLEquarterly_regional_sales(deptnonumber,item_novarchar2(20),txn_datedate,txn_amountnumber,statevarchar2(2))TABLESPACEts4PARTITIONBYRANGE(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('OK','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('OK','TX')),创建复合分区PARTITIONq3_1999VALUESLESSTHAN(TO_DATE('1-OCT-1999','DD-MON-YYYY'))(SUBPARTITIONq3_1999_northwestVALUES('OR','WA'),SUBPARTITIONq3_1999_southwestVALUES('AZ','UT','NM'),SUBPARTITIONq3_1999_northeastVALUES('NY','VM','NJ'),SUBPARTITIONq3_1999_southeastVALUES('FL','GA'),SUBPARTITIONq3_1999_northcentralVALUES('SD','WI'),SUBPARTITIONq3_1999_southcentralVALUES('OK','TX')),PARTITIONq4_1999VALUESLESSTHAN(TO_DATE('1-JAN-2000','DD-MON-YYYY'))(SUBPARTITIONq4_1999_northwestVALUES('OR','WA'),SUBPARTITIONq4_1999_southwestVALUES('AZ','UT','NM'),SUBPARTITIONq4_1999_northeastVALUES('NY','VM','NJ'),SUBPARTITIONq4_1999_southeastVALUES('FL','GA'),SUBPARTITIONq4_1999_northcentralVALUES('SD','WI'),SUBPARTITIONq4_1999_southcentralVALUES('OK','TX')));维护分区表在表中增加分区（以范围分区为例）使用AlterTable…AddPartition子句增加新分区到表中（该分区位置在该表最后一个分区之后），如果要将该分区加入表中开始或中间位置，则使用SPLITPARTITION子句。示例：ALTERTABLEsalesADDPARTITIONjan96VALUESLESSTHAN('01-FEB-1999')TABLESPACEtsx;维护分区表结合分区减少散列分区或范围－散列分区数量的方法就是组合分区，当一个散列分区被组合之后，其数据内容会被重新分布到现存的一个或多个散列分区中，被组合的分区由Oracle选择，再其数据被重新分布后删除掉。使用AlterTable….COALESCEPARTITION子句用来结合散列分区中的一个分区。　示例：ALTERTABLEouu1COALESCEPARTITION;维护分区表删除分区可以在范围，复合，列表或范围－列表分区表中删除分区。而对于散列分区，散列子分区或范围－散列分区表，必须使用结合分区方法。使用　AlterTable……DropPARATITION删除分区使用AlterTable……DropSUBPARATITION删除子分区示例：　ALTERTABLEsalesDROPPARTITIONdec98;维护分区表交换分区你可以将一个非分区表数据交换为一个分区表。也可以将一个散列分区交换为范围－散列分区中的一个分区或反之，类似的，将列表分区交换为范围－列表分区中的分区或反之。　使用AlterTable……EXCHANGEPARATITION语句。示例1：转换一个非分区表中的分区到分区表中。ALTERTABLEstocksEXCHANGEPARTITIONp3WITHstock_table_3;维护分区表示例2：交换一个散列分区表到一个范围－散列分区表的中一个分区中。首先，创建一个散列分区表CREATETABLEt1(iNUMBER,jNUMBER)PARTITIONBYHASH(i)(PARTITIONp1,PARTITIONp2)维护分区表创建一个范围－散列表CREATETABLEt2(iNUMBER,jNUMBER)PARTITIONBYRANGE(j)SUBPARTITIONBYHASH(i)(PARTITIONp1VALUESLESSTHAN(10)SUBPARTITIONt2_pls1SUBPARTITIONt2_pls2,PARTITIONp2VALUESLESSTHAN(20)SUBPARTITIONt2_p2s1SUBPARTITIONt2_p2s2));维护分区表T1表中的分区关键字与t2表中的子分区关键字必须相同。将数据从t2迁移到t1中，并且保证数据行有效。使用以下的语句。ALTERTABLEt1EXCHANGEPARTITIONp1WITHTABLEt2WITHVALIDATION;维护分区表合并分区表将两个分区的内容合并为一个，原来的两个分区则会被删除掉。这个功能不能应用于散列分区或散列子分区。使用ALTERTABLE...MERGEPARTITIONS执行该功能例1：合并范围分表可以合并两个相邻范围分区为一个分区，如果两个分区的范围并不相邻，则不能进行合并。首先，创建范围分区表维护分区表CREATETABLEfour_seasons(oneDATE,twoVARCHAR2(60),threeNUMBER)PARTITIONBYRANGE(one)(PARTITIONquarter_oneVALUESLESSTHAN(TO_DATE('01-apr-1998','dd-mon-yyyy'))TABLESPACEquarter_one,PARTITIONquarter_twoVALUESLESSTHAN(TO_DATE('01-jul-1998','dd-mon-yyyy'))TABLESPACEquarter_two,PARTITIONquarter_threeVALUESLESSTHAN(TO_DATE('01-oct-1998','dd-mon-yyyy'))TABLESPACEquarter_three,PARTITIONquarter_fourVALUESLESSTHAN(TO_DATE('01-jan-1999','dd-mon-yyyy'))TABLESPACEquarter_four);维护分区表然后，进行合并分区操作合并头两个分区ALTERTABLEfour_seasonsMERGEPARTITIONSquarter_one,quarter_twoINTOPARTITIONquarter_two;例2：合并列表分区合并列表分区时，被合并的两个分区可以是任意两个分区。并不需要如同合并范围分区时两分区的需要邻近的范围。如果其中合并了一个default分区，则新合并的分区则是default分区ALTERTABLEq1_sales_by_regionMERGEPARTITIONSq1_northcentral,q1_southcentralINTOPARTITIONq1_central维护分区表上面语句的两个原始分区分别是PARTITIONq1_northcentralVALUES('SD','WI')PARTITIONq1_southcentralVALUES('OK','TX')合并后的新分区则包含以下列值('SD','WI','OK','TX')维护分区表修改列表分区，增加值可以在现存分区的值列中增加值以扩充值列范围，但新增的值不能存在于其它分区条件中。使用MODIFYPARTITION...ADDVALUES子句执行该功能例如：ALTERTABLEsales_by_regionMODIFYPARTITIONregion_southADDVALUES('OK','KS');在列表分区region_south中新增值’OK’,’KS’维护分区表修改列分区：删除值从列表分区的值列中删除某些值。不能从default列表分区中删除值。使用MODIFYPARTITION...DROPVALUES执行该操作。例如：ALTERTABLEsales_by_regionMODIFYPARTITIONregion_southDROPVALUES('OK','KS');维护分区表移动分区移动分区到另一个表空间中，重组织数据及减少数据碎片。使用MOVEPARTITION子句执行该功能。例如：ALTERTABLEpartsMOVEPARTITIONdepot2TABLESPACEts094;维护分区表重命名分区表可以给某个分区或子分区重命名一个更有意义的名字以取代系统的命名，这样更有利于平时的维护工作。使用RENAMEPARTITION执行该操作。例如：ALTERTABLEscubagearRENAMEPARTITIONsys_p636TOtanks;维护分区表拆分分区表拆分分区表中分区重新分配分区数据到两个新分区。当某个分区变得过大，在执行备份，恢复与平时的维护工作时需花大量时间，则需要考虑进行拆分。使用SPLITPARTITION执行该功能例1:拆分范围分区表拆分范围分区表时，需要指定分区列中的值，该值需存在于该分区范围中。拆分后的两个分区，第一个分区的内容是小于该值的数据。第二个分区则大于或等于该值的数据。例:ALTERTABLEvet_catsSPLITPARTITIONfee_katyat(100)INTO(PARTITIONfee_katy1...,PARTITIONfee_katy2...);维护分区表例2：拆分列表分区需指定一个值列以定义拆分后的第一个分区的值列条件，原分区的内容符合该值列条件的数据放入第一个分区中，而其余的内容则放入第二个分区中。如：ALTERTABLEsales_by_regionSPLITPARTITIONregion_eastVALUES('CT','VA','MD')INTO(PARTITIONregion_east_1,PARTITIONregion_east_2);其中原分区region_east的值列为('MA','NY','CT','NH','ME','MD','VA','PA','NJ')维护分区表截取分区截取一个分区与删除一个分区有些类似，不同在于截取分区时只是清空该分区中的数据内容，但不物理地删除该分区。使用ALTERTABLE...TRUNCATEPARTITION执行该功能例如：ALTERTABLEsalesTRUNCATEPARTITIONdec98;