Oracle高级技术之大数据处理70.ppt

,Oracle高级技术-大数据处理,交流内容,分区技术报表优化技术并行处理应用经验,Oracle的分区技术,分区技术内容,什么是分区?分区的好处?如何实施分区?如何评估分区的效果?,Oracle的分区技术基本原理,分而治之,2003,2004,2005,2006,分区概述,大数据对象(表,索引)被分成小物理段当分区表建立时，记录基于分区字段值被存储到相应分区。分区字段值可以修改。(rowmovementenabled)分区可以存储在不同的表空间分区可以有不同的物理存储参数分区支持IOT表，对象表，LOB字段，varrays等,分区技术的效益和目标,性能Select和DML操作只访问指定分区并行DML操作Partition-wiseJoin可管理性：数据删除，数据备份历史数据清除提高备份性能指定分区的数据维护操作可用性将故障局限在分区中缩短恢复时间分区目标优先级高性能数据维护能力-实施难度高可用性（故障屏蔽能力）,分区方法,分区方法:范围-8Hash-8i列表-9i组合-8i,Rangepartitioning,Hashpartitioning,1,2,3,CREATETABLEsales(acct_noNUMBER(5),personVARCHAR2(30),sales_amountNUMBER(8),week_noNUMBER(2)PARTITIONBYRANGE(week_no)(PARTITIONP1VALUESLESSTHAN(4)TABLESPACEdata0,PARTITIONP2VALUESLESSTHAN(8)TABLESPACEdata1,.PARTITIONP13VALUESLESSTHAN(53)TABLESPACEdata12);,分区字段：week_no.VALUESLESSTHAN必须是确定值每个分区可以单独指定物理属性,1,2,3,范围分区例,最早、最经典的分区算法Range分区通过对分区字段值的范围进行分区Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。数据管理能力强数据迁移数据备份数据交换范围分区的数据可能不均匀范围分区与记录值相关，实施难度和可维护性相对较差,范围分区特点,Hash分区例,createtableCUSTOMERS(.columndefinitions.)pctfree0nologgingstorage(initial40mnext40mpctincrease0)partitionbyhash(customer_no)partitions8storein(cust_data01,cust_data02)createtableCUSTOMERS(.columndefinitions.)pctfree0nologgingstorage(initial40mnext40mpctincrease0)partitionbyhash(customer_no)(partitioncust_p01tablespacecust_data01,partitioncust_p02tablespacecust_data02,partitioncust_p03tablespacecust_data03,partitioncust_p04tablespacecust_data04,partitioncust_p05tablespacecust_data05,partitioncust_p06tablespacecust_data06,partitioncust_p07tablespacecust_data07,partitioncust_p08tablespacecust_data08),Hash分区特点,基于分区字段的HASH值，自动将记录插入到指定分区。分区数一般是2的幂易于实施总体性能最佳适合于静态数据HASH分区适合于数据的均匀存储HASH分区特别适合于PDML和partition-wisejoins。支持(hash)localindexes9i不支持(hash)globalindexes10g支持(hash)globalindexesHASH分区数据管理能力弱HASH分区对数据值无法控制,列表分区例,createtableaddresses(.columndefinitions.)pctfree0nologgingstorage(initial40mnext40mpctincrease0)partitionbylist(city_name)(partitionaddr_p01values(WELLINGTON)tablespaceaddr_data01,partitionaddr_p02values(CHRISTCHURCH)tablespaceaddr_data02,partitionaddr_p03values(DUNEDIN,INVERCARGILL)tablespaceaddr_data03,partitionaddr_p04values(AUCKLAND)tablespaceaddr_data04,partitionaddr_p05values(HAMILTON,ROTORUA,TAURANGA)tablespaceaddr_data05),列表分区特点,List分区通过对分区字段的离散值进行分区。List分区是不排序的，而且分区之间没有关联关系List分区适合于对数据离散值进行控制。List分区只支持单个字段。List分区具有与范围分区相似的优缺点数据管理能力强List分区的数据可能不均匀List分区与记录值相关，实施难度和可维护性相对较差,复合分区例,createtabledaily_trans_data(.columndefinitions.)partitionbyrange(trans_datetime)subpartitionbyhash(customer_no)subpartitions8storein(dtd_data01,dtd_data02)(partitiondtd_20010620valueslessthan(to_date(21-jun-2001,dd-mon-yyyy)(subpartitiondtd_20010620_s01,subpartitiondtd_20010620_s02,subpartitiondtd_20010620_s03tablespacedtd_data03,subpartitiondtd_20010620_s04tablespacedtd_data04,subpartitiondtd_20010620_s05tablespacedtd_data05,subpartitiondtd_20010620_s06tablespacedtd_data06,subpartitiondtd_20010620_s07tablespacedtd_data07,subpartitiondtd_20010620_s08tablespacedtd_data08),partitiondtd_20010621valueslessthan(to_date(22-jun-2001,dd-mon-yyyy),partitiondtd_20010622valueslessthan(to_date(23-jun-2001,dd-mon-yyyy)subpartitions4),复合分区图示,复合分区特点,Oracle支持的Composite分区：Range-Hash,Range-List既适合于历史数据，又适合于数据均匀分布与范围分区一样提供高可用性和管理性更好的PDML和partition-wisejoins性能实现粒度更细的操作支持复合localindexes不支持复合compositeglobalindexes？,分区索引,表,索引,Localpartitionedindex,GlobalPartitionedIndex,不同的分区索引,绍兴,杭州,温州,分区索引,分区表索引的分类：LocalPrefixedindexLocalNon-prefiexedindexGlobalPrefixedindexNonPartitionIndexGlobal索引的分区不同与表分区Local索引的分区与表分区相同Anindexisprefixedifitispartitionedonaleftprefixoftheindexcolumns.分区表上的非分区索引等同于Global索引,分区索引,Global索引必须是范围分区-9i之前Global索引可以是HASH分区-10g新特性Global索引不支持Bitmap索引Unique索引必须是prefixed，或者包含分区字段Local索引（non-prefixed,non-unique）可以不包含分区字段,createindexcust_idx1oncustomers(customer_name)globalpartitionbyrange(customer_name)(partitioncust_p01valueslessthan(H)tablespacecust_index01,partitioncust_p02valueslessthan(N)tablespacecust_index02,partitioncust_p03valueslessthan(T)tablespacecust_index03,partitioncust_p04valueslessthan(MAXVALUE)tablespacecust_index04)createindexcust_idx2oncustomers(customer_no)local(partitioncust_idx_p01tablespacecust_index01,partitioncust_idx_p02tablespacecust_index02,partitioncust_idx_p03tablespacecust_index03,partitioncust_idx_p04tablespacecust_index04,partitioncust_idx_p05tablespacecust_index05,partitioncust_idx_p06tablespacecust_index06,partitioncust_idx_p07tablespacecust_index07,partitioncust_idx_p08tablespacecust_index08)createindexcust_idx3oncustomers(customer_type)local;,分区索引举例,分区表索引的使用,OLTP系统中的建议Global和uniquelocalindex性能优于nonuniquelocalindexLocalindex提供了更好的可用性数据仓库系统中的建议Localindex更适合于数据装载和分区维护在大量数据统计时，能充分利用Localindex并行查询能力在性能、高可用性和可管理性之间进行平衡,分区索引选择策略,分区裁剪功能,Partitionpruning:Onlytherelevantpartitionsareaccessed.,99-May,99-Apr,99-Feb,99-Jan,99-Mar,99-Jun,sales,SQLSELECTSUM(sales_amount)2FROMsales3WHEREsales_dateBETWEEN4TO_DATE(01-MAR-1999,5DD-MON-YYYY)AND6TO_DATE(31-MAY-1999,7DD-MON-YYYY);,分区裁剪举例,1select*fromdaily_trans_summ2*wheretrans_datetimebetweento_date(25-jun-200108,DD-mon-yyyyhh24)andto_date(28-jun-200118,DD-mon-yyyyhh24)PartitionPartitionOperationOptionsObjectNameStartStop-SELECTSTATEMENTPARTITIONRANGEITERATOR231234TABLEACCESSFULLDAILY_TRANS_SUMM2312341select*fromdaily_trans_summ2*wheretrans_datetimein(25-jun-2001,28-jun-2001)PartitionPartitionOperationOptionsObjectNameStartStop-SELECTSTATEMENTPARTITIONRANGEINLISTKEY(INLIST)KEY(INLIST)TABLEACCESSFULLDAILY_TRANS_SUMMKEY(INLIST)KEY(INLIST),Nonpartition-wisejoin,Fullpartition-wisejoin,Partialpartition-wisejoin,Queryslave,Partition,Partitionedtable,1,2,3,Partition-WiseJoin,Partition-wiseJoins,Tablesandindexesthatarepartitionedidenticallyareequi-partitioned.Afullpartition-wisejoinoccurswhenjoiningtwoequi-partitionedtablesthatarepartitionedonthejoinkey.Oraclesplitsthejoinintojoinsofpairsofpartitions.Apartialpartition-wisejoinoccurswhenonlyoneofthetablesispartitionedonthejoinkey.Partition-wisejoinsoccurwhenjoiningahashpartitiontabletoacompositepartitiontableifthehashpartitioningandsub-partitioningisonthejoinkey.Oracleassignsparallelqueryslavestoprocessthepartitionjoins.,Partition-wiseJoins举例,1select/*+full(c)*/c.customer_no,count(*)2fromcustomersc,daily_trans_datad3wherec.customer_no=d.customer_no4andd.trans_datetimebetweento_date(25-jun-2001,dd-mon-yyyy)5andto_date(28-jun-2001,dd-mon-yyyy)6*groupbyc.customer_noPartitionPartitionOperationOptionsObjectNameStartStop-SELECTSTATEMENTPARTITIONHASHALL18SORTGROUPBYHASHJOINPARTITIONRANGEITERATOR5053TABLEACCESSFULLDAILY_TRANS_DATA393424TABLEACCESSFULLCUSTOMERS18,分区表设计原则,表的大小：当表的大小超过1.5GB2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。数据备份和恢复：按时间周期进行表空间的备份时，将分区与表空间建立对应关系。只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。并行数据操作：对于经常执行并行操作（如ParallelInsert,ParallelUpdate等）的表应考虑进行分区。表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。,分区表的管理功能,分区的增加（ADD）分区的删除（DROP）分区的合并（MERGE）分区的清空（TRUNCATE）分区的交换（EXCHANGE）分区的压缩（COALESE）分区的移动（MOVE）分区的分离（SPLIT）修改分区的DefaultAttribute分区的更名（RENAME）,分区索引的管理功能,分区索引的删除（DROP）分区索引的修改（MODIFY）分区索引DefaultAttribute的修改分区索引的重建（REBUILD）分区索引的更名（RENAME）分区索引的分离（SPLIT）分区索引的Unusable,分区表和Local索引,AUG2002,新月份数据的加载和索引的维护,.,“滚动窗口”操作-大量数据高速装载,删除或归档最老月份的数据,新月份数据的加载和索引的维护,.,分区表和Local索引,“滚动窗口”操作-大量数据高速装载,分区交换功能,通过交换数据段，实现分区和非分区表的数据交换。以及子分区和分区表的数据交换非常快捷的数据移动方式。特别是没有validation和索引维护操作时Local索引自动维护Global索引必须重建,分区交换的应用-全文检索,12：00分区,BF_DXX_stage中间表,（1）1：00数据的加载,（2）建立context索引,（3）partition的exchange,BF_DXX表,*初始化工作,*整理工作,2：00分区,1：00分区,分区交换的应用-全文检索,第一步：1：00数据的加载insertintoBF_DXX_stage(SJ,TEXT3)values(to_date(2004.03.02,YYYY.MM.DD),大撒反对撒);第二步：建立context索引CREATEINDEXIDX_BF_DXX_STAGEONBF_DXX_stage(text3)INDEXTYPEISCTXSYS.CONTEXTPARAMETERS(LEXERMYLEXERSTORAGEMYSTOREFILTERCTXSYS.NULL_FILTERMEMORY100M)parallel4;第三步：partition的交换altertableBF_DXXexchangepartitionp2withtableBF_DXX_stageincludingindexes;,迁移表空间(TransportableTablespace)技术简介,第一步：exptransport_tablespace=yes第二步：FTP数据文件和dmp文件第三步：imptransport_tablespace=yes,迁移表空间技术的作用,业务系统数据向数据仓库系统的迁移对业务系统和数据仓库系统的数据进行定期归档数据仓库向数据集市的数据迁移数据对外发布按表空间进行时间点的数据恢复(TSPITR),迁移表空间技术的优点,性能大大高于export/import或PL/SQL编写的程序由于Dmp文件只包含表空间的结构信息，因此该技术的真正开销在于数据文件的传输。对源系统的影响非常小只需要将被迁移的表空间设置为只读方式可同时传输索引数据，避免在目的数据库中重建索引,分区交换的应用-ETL,在源系统中，将需要抽取的数据以如下语句形式，抽取到建立在单独表空间上的中间表中：CREATETABLE.ASSELECTINSERT/*+APPEND*/ASSELECT以TTS方式将中间表的表空间传输到数据仓库之中。exptransportable_tablespace=YesFTP中间表表空间的数据文件imptransportable_tablespace=Yes在数据仓库中对中间表进行各种数据归并等清洗工作，并建立需要的各种索引。通过exchange技术，将中间表数据及索引直接交换到分区表中。Altertableexchangepartitionwithtableincludingindexes;,分区交换的应用-重复记录删除,问题描述：在使用SQL*Loader进行数据加载sor_acct_dcc_saamt_c表时，由于操作失误，重复加载，导致分区ETL_LOAD_DATE_0606出现重复记录，也使得两个唯一索引：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分区不可用（UNUSABLE）。用户在试图重新创建该分区索引时，出现如下错误：SQLalterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606;alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606*ORA-01452:cannotCREATEUNIQUEINDEX;duplicatekeysfound,分区交换的应用-重复记录删除,在试图删除该分区的重复记录时，又出现如下错误：SQLdeletefromsor_acct_dcc_saamt_cpartition(ETL_LOAD_DATE_0606)whererowidnotin(selectmin(rowid)fromsor_acct_dcc_saamt_cpartition(ETL_LOAD_DATE_0606)groupbyETL_LOAD_DATE,CUST_ACCT_NO,SA_CURR_COD,SA_CURR_IDEN);*ORA-01502:indexGYFX.IDX_SAACNAMT_C_1orpartitionofsuchindexisinunusablestate,分区交换的应用-重复记录删除,简单办法是彻底删除这两个唯一索引，重新创建。数据量大，时间太长。影响系统的可用性。更完备的解决方式创建一个与sor_acct_dcc_saamt_c结构一样的临时表test。SQLcreatetabletestasselect*fromsor_acct_dcc_saamt_cwhere1=2;将sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606数据交换到临时表test。SQLaltertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;,分区交换的应用-重复记录删除,更完备的解决方式删除test中的重复记录deletefromtestwhererowidnotin(selectmin(rowid)fromtestgroupbyETL_LOAD_DATE,CUST_ACCT_NO,SA_CURR_COD,SA_CURR_IDEN);因为test表没有任何索引，可避免上述ORA-01502错误。将临时表test数据交换回sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606。altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;,分区交换的应用-重复记录删除,更完备的解决方式重新创建创建该分区索引IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2alterindexIDX_SAACNAMT_C_1rebuildpartitionETL_LOAD_DATE_0606tablespaceETL0_R_LOAD_IDX_200606;alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606tablespaceETL0_R_LOAD_IDX_200606;此时重复记录已经删除，可避免上述ORA-01452错误,现有系统实施分区的经验,分区对象的确定：存储空间最大的前20个表Select*from(Select*fromdba_segmentsorderbybytesdesc)whererownumSELECTd_name,SUM(s.quanti