oracle分區(qū)技術(shù)大批量數(shù)據(jù)操作

Oracle數(shù)據(jù)庫高級(jí)技術(shù)交流---大批量數(shù)據(jù)處理技術(shù)Oracle(中國)顧問咨詢部羅敏資深技術(shù)顧問電話Mail:Michael.luo@交流內(nèi)容分區(qū)技術(shù)報(bào)表優(yōu)化技術(shù)

并行處理應(yīng)用經(jīng)驗(yàn)Oracle的分區(qū)技術(shù)

分區(qū)技術(shù)內(nèi)容什么是分區(qū)?分區(qū)的好處?如何實(shí)施分區(qū)?如何評(píng)估分區(qū)的效果?

Oracle的分區(qū)技術(shù)基本原理分而治之SB_ZSXX按年度進(jìn)行分區(qū)2003200420052006分區(qū)概述大數(shù)據(jù)對(duì)象(表,索引)被分成小物理段當(dāng)分區(qū)表建立時(shí)，記錄基于分區(qū)字段值被存儲(chǔ)到相應(yīng)分區(qū)。分區(qū)字段值可以修改。(rowmovementenabled)分區(qū)可以存儲(chǔ)在不同的表空間分區(qū)可以有不同的物理存儲(chǔ)參數(shù)分區(qū)支持IOT表，對(duì)象表，LOB字段，varrays等分區(qū)技術(shù)的效益和目標(biāo)性能Select和DML操作只訪問指定分區(qū)并行DML操作Partition-wiseJoin可管理性：數(shù)據(jù)刪除，數(shù)據(jù)備份歷史數(shù)據(jù)清除提高備份性能指定分區(qū)的數(shù)據(jù)維護(hù)操作可用性將故障局限在分區(qū)中縮短恢復(fù)時(shí)間分區(qū)目標(biāo)優(yōu)先級(jí)

高性能–>數(shù)據(jù)維護(hù)能力->實(shí)施難度–>高可用性（故障屏蔽能力）分區(qū)方法分區(qū)方法:范圍---8Hash---8i列表---9i組合---8iRange

partitioningHash

partitioningComposite

partitioningList

partitioning123CREATETABLEsales(acct_noNUMBER(5),personVARCHAR2(30),sales_amountNUMBER(8),week_noNUMBER(2))PARTITIONBYRANGE(week_no)(PARTITIONP1VALUESLESSTHAN(4)TABLESPACEdata0,PARTITIONP2VALUESLESSTHAN(8)TABLESPACEdata1,...…PARTITIONP13VALUESLESSTHAN(53)TABLESPACEdata12);分區(qū)字段：week_no.VALUESLESSTHAN

必須是確定值每個(gè)分區(qū)可以單獨(dú)指定物理屬性123范圍分區(qū)例最早、最最經(jīng)典的的分區(qū)算算法Range分區(qū)通過過對(duì)分區(qū)區(qū)字段值值的范圍圍進(jìn)行分分區(qū)Range分區(qū)特別別適合于于按時(shí)間間周期進(jìn)進(jìn)行數(shù)據(jù)據(jù)的存儲(chǔ)儲(chǔ)。日、、周、月月、年等等。數(shù)據(jù)管理理能力強(qiáng)強(qiáng)數(shù)據(jù)遷移移數(shù)據(jù)備份份數(shù)據(jù)交換換范圍分區(qū)區(qū)的數(shù)據(jù)據(jù)可能不不均勻范圍分區(qū)區(qū)與記錄錄值相關(guān)關(guān)，實(shí)施施難度和和可維護(hù)護(hù)性相對(duì)對(duì)較差范圍分區(qū)區(qū)特點(diǎn)Hash分區(qū)例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分區(qū)特特點(diǎn)基于分分區(qū)字字段的的HASH值，自自動(dòng)將將記錄錄插入入到指指定分分區(qū)。。分區(qū)數(shù)數(shù)一般般是2的冪冪易于實(shí)實(shí)施總體性性能最最佳適合于于靜態(tài)態(tài)數(shù)據(jù)據(jù)HASH分區(qū)適適合于于數(shù)據(jù)據(jù)的均均勻存存儲(chǔ)HASH分區(qū)特特別適適合于于PDML和partition-wisejoins。。支持(hash)localindexes9i不支持持(hash)globalindexes10g支持(hash)globalindexesHASH分區(qū)數(shù)據(jù)管管理能能力弱弱HASH分區(qū)對(duì)對(duì)數(shù)據(jù)據(jù)值無無法控控制列表分分區(qū)例例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)列表分區(qū)特特點(diǎn)List分區(qū)通過對(duì)對(duì)分區(qū)字段段的離散值值進(jìn)行分區(qū)區(qū)。List分區(qū)是不排排序的，而而且分區(qū)之之間沒有關(guān)關(guān)聯(lián)關(guān)系List分區(qū)適合于于對(duì)數(shù)據(jù)離離散值進(jìn)行行控制。List分區(qū)只支持持單個(gè)字段段。List分區(qū)具有與與范圍分區(qū)區(qū)相似的優(yōu)優(yōu)缺點(diǎn)數(shù)據(jù)管理能能力強(qiáng)List分區(qū)的數(shù)據(jù)據(jù)可能不均均勻List分區(qū)與記錄錄值相關(guān)，，實(shí)施難度度和可維護(hù)護(hù)性相對(duì)較較差復(fù)合分區(qū)例例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)復(fù)合分區(qū)圖圖示復(fù)合分區(qū)特特點(diǎn)Oracle支持的Composite分區(qū)：Range-Hash,Range-List既適合于歷歷史數(shù)據(jù)，，又適合于于數(shù)據(jù)均勻勻分布與范圍分區(qū)區(qū)一樣提供供高可用性性和管理性性更好的PDML和partition-wisejoins性能實(shí)現(xiàn)粒度更更細(xì)的操作作支持復(fù)合localindexes不支持復(fù)合合composite

globalindexes？？分區(qū)索引不分區(qū)分區(qū)不分區(qū)

分區(qū)表索引√√√√GlobalNonpartitionedindexLocalpartitionedindexGlobalPartitionedIndex不同的分區(qū)區(qū)索引紹興杭州溫州03年04年…08年分區(qū)索引分區(qū)表索引引的分類：：LocalPrefixedindexLocalNon-prefiexedindexGlobalPrefixedindexNonPartitionIndexGlobal索引的分區(qū)區(qū)不同與表表分區(qū)Local索引的分區(qū)區(qū)與表分區(qū)區(qū)相同Anindexisprefixedifitispartitionedonaleftprefixoftheindexcolumns.分區(qū)表上的的非分區(qū)索索引等同于于Global索引分區(qū)索引Global索引必須是是范圍分區(qū)區(qū)---9i之前Global索引可以是是HASH分區(qū)--10g新特性Global索引不支持持Bitmap索引Unique索引必須是是prefixed，，或者包含分分區(qū)字段Local索引（non-prefixed,non-unique））可以不包含含分區(qū)字段段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;分區(qū)區(qū)索索引引舉舉例例分區(qū)區(qū)表表索索引引的的使使用用OLTP系統(tǒng)統(tǒng)中中的的建建議議Global和uniquelocalindex性能能優(yōu)優(yōu)于于nonuniquelocalindexLocalindex提供了更更好的可可用性數(shù)據(jù)倉庫庫系統(tǒng)中中的建議議Localindex更適合于于數(shù)據(jù)裝裝載和分分區(qū)維護(hù)護(hù)在大量數(shù)數(shù)據(jù)統(tǒng)計(jì)計(jì)時(shí)，能能充分利利用Localindex并行查詢?cè)兡芰υ谛阅堋?、高可用用性和可可管理性性之間進(jìn)進(jìn)行平衡衡分區(qū)索引引選擇策策略分區(qū)裁剪剪功能Partitionpruning:Onlytherelevantpartitionsareaccessed.99-May99-Apr99-Feb99-Jan99-Mar99-JunsalesSQL>SELECTSUM(sales_amount)2FROMsales3WHEREsales_dateBETWEEN4TO_DATE(‘01-MAR-1999’,5‘‘DD-MON-YYYY’)AND6TO_DATE(‘31-MAY-1999’,7‘‘DD-MON-YYYY’);分區(qū)裁剪剪舉例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-wisejoinFullpartition-wisejoinPartialpartition-wisejoinQueryslavePartitionPartitionedtable123Partition-WiseJoinPartition-wiseJoinsTablesandindexesthatarepartitionedidenticallyareequi-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分區(qū)表設(shè)設(shè)計(jì)原則則表的大小?。寒?dāng)表表的大小小超過1.5GB－2GB，或?qū)τ贠LTP系統(tǒng)，表表的記錄錄超過1000萬，都應(yīng)應(yīng)考慮對(duì)對(duì)表進(jìn)行行分區(qū)。。數(shù)據(jù)訪問問特性：：基于表表的大部部分查詢?cè)儜?yīng)用，，只訪問問表中少少量的數(shù)數(shù)據(jù)。對(duì)對(duì)于這樣樣表進(jìn)行行分區(qū)，，可充分分利用分分區(qū)排除除無關(guān)數(shù)數(shù)據(jù)查詢?cè)兊奶匦孕浴?shù)據(jù)維護(hù)護(hù)：按時(shí)時(shí)間段刪刪除成批批的數(shù)據(jù)據(jù)，例如如按月刪刪除歷史史數(shù)據(jù)。。對(duì)于這這樣的表表需要考考慮進(jìn)行行分區(qū)，，以滿足足維護(hù)的的需要。。數(shù)據(jù)備份份和恢復(fù)復(fù)：按時(shí)間周周期進(jìn)行行表空間間的備份份時(shí)，將將分區(qū)與與表空間間建立對(duì)對(duì)應(yīng)關(guān)系系。只讀數(shù)據(jù)據(jù)：如果果一個(gè)表表中大部部分?jǐn)?shù)據(jù)據(jù)都是只只讀數(shù)據(jù)據(jù)，通過過對(duì)表進(jìn)進(jìn)行分區(qū)區(qū)，可將將只讀數(shù)數(shù)據(jù)存儲(chǔ)儲(chǔ)在只讀讀表空間間中，對(duì)對(duì)于數(shù)據(jù)據(jù)庫的備備份是非非常有益益的。并行數(shù)據(jù)據(jù)操作：：對(duì)于經(jīng)經(jīng)常執(zhí)行行并行操操作（如如ParallelInsert,ParallelUpdate等）的表表應(yīng)考慮慮進(jìn)行分分區(qū)。表的可用用性：當(dāng)當(dāng)對(duì)表的的部分?jǐn)?shù)數(shù)據(jù)可用用性要求求很高時(shí)時(shí)，應(yīng)考考慮進(jìn)行行表分區(qū)區(qū)。分區(qū)表的的管理功功能分區(qū)的增增加（ADD））分區(qū)的刪刪除（DROP）分區(qū)的合合并（MERGE）分區(qū)的清清空（TRUNCATE））分區(qū)的交交換（EXCHANGE））分區(qū)的壓壓縮（COALESE）分區(qū)的移移動(dòng)（MOVE）分區(qū)的分分離（SPLIT））修改分區(qū)區(qū)的DefaultAttribute分區(qū)的更更名（RENAME））分區(qū)索引引的管理理功能分區(qū)索引引的刪除除（DROP）分區(qū)索引引的修改改（MODIFY））分區(qū)索引引DefaultAttribute的修改分區(qū)索引引的重建建（REBUILD）分區(qū)索引引的更名名（RENAME））分區(qū)索引引的分離離（SPLIT））分區(qū)索引引的Unusable分區(qū)表和和Local索引OCT

2002SEP

2002AUG2002NOV

2001OCT

2001新月份數(shù)數(shù)據(jù)的加加載和索索引的維維護(hù)NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002...“滾動(dòng)窗口””操作-大大量數(shù)據(jù)高高速裝載OCT

2002SEP

2002NOV

2002NOV

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001OCT

2002SEP

2002NOV

2002DEC

2001NOV

2001OCT

2001NOV

2002刪除或歸檔最最老月份的數(shù)數(shù)據(jù)OCT

2001OCT

2001新月份數(shù)據(jù)的的加載和索引引的維護(hù)...分區(qū)表和Local索引“滾動(dòng)窗口””操作-大大量數(shù)據(jù)高高速裝載分區(qū)交換功能能通過交交換數(shù)數(shù)據(jù)段段，實(shí)實(shí)現(xiàn)分分區(qū)和和非分分區(qū)表表的數(shù)數(shù)據(jù)交交換。。以及及子分分區(qū)和和分區(qū)區(qū)表的的數(shù)據(jù)據(jù)交換換非?？炜旖莸牡臄?shù)據(jù)據(jù)移動(dòng)動(dòng)方式式。特特別是是沒有有validation和索引引維護(hù)護(hù)操作作時(shí)Local索引自自動(dòng)維維護(hù)Global索引必必須重重建分區(qū)交交換的的應(yīng)用用---全全文文檢索索12：00分區(qū)BF_DXX_stage中間表表（1）1：00數(shù)據(jù)的的加載載（2）建立context索引（3））partition的exchangeBF_DXX表*初初始化化工作作*整整理工工作………2：00分分區(qū)1：00分區(qū)分區(qū)交交換的的應(yīng)用用---全全文文檢索索第一步步：1：00數(shù)數(shù)據(jù)的的加載載insertintoBF_DXX_stage(SJ,TEXT3)values(to_date('2004.03.02','YYYY.MM.DD'),'大撒反反對(duì)撒撒');第二步步：建建立context索引引CREATEINDEXIDX_BF_DXX_STAGEONBF_DXX_stage(text3)INDEXTYPEISCTXSYS.CONTEXTPARAMETERS('LEXERMYLEXERSTORAGEMYSTOREFILTERCTXSYS.NULL_FILTERMEMORY100M')parallel4;第三三步步：：partition的交交換換altertableBF_DXXexchangepartitionp2withtableBF_DXX_stageincludingindexes;遷移移表表空空間間(TransportableTablespace)技術(shù)術(shù)簡簡介介第一一步步：：exptransport_tablespace=yes第二二步步：：FTP數(shù)據(jù)據(jù)文文件件和和dmp文件件第三三步步：：imptransport_tablespace=yes地市系統(tǒng)imp卸載文件省級(jí)系統(tǒng)FTP數(shù)據(jù)文件卸載文件數(shù)據(jù)文件exp遷移移表表空空間間技技術(shù)術(shù)的的作作用用業(yè)務(wù)系統(tǒng)統(tǒng)數(shù)據(jù)向向數(shù)據(jù)倉倉庫系統(tǒng)統(tǒng)的遷移移對(duì)業(yè)務(wù)系系統(tǒng)和數(shù)數(shù)據(jù)倉庫庫系統(tǒng)的的數(shù)據(jù)進(jìn)進(jìn)行定期期歸檔數(shù)據(jù)倉庫庫向數(shù)據(jù)據(jù)集市的的數(shù)據(jù)遷遷移數(shù)據(jù)對(duì)外外發(fā)布按表空間間進(jìn)行時(shí)時(shí)間點(diǎn)的的數(shù)據(jù)恢恢復(fù)(TSPITR)遷移表空空間技術(shù)術(shù)的優(yōu)點(diǎn)點(diǎn)性能大大大高于export/import或PL/SQL編寫的程程序由于Dmp文件只包包含表空空間的結(jié)結(jié)構(gòu)信息息，因此此該技術(shù)術(shù)的真正正開銷在在于數(shù)據(jù)據(jù)文件的的傳輸。。對(duì)源系統(tǒng)統(tǒng)的影響響非常小小只需要將將被遷移移的表空空間設(shè)置置為只讀讀方式可同時(shí)傳傳輸索引引數(shù)據(jù)，，避免在在目的數(shù)數(shù)據(jù)庫中中重建索索引分區(qū)區(qū)交交換換的的應(yīng)應(yīng)用用---ETL在源源系系統(tǒng)統(tǒng)中中，，將將需需要要抽抽取取的的數(shù)數(shù)據(jù)據(jù)以以如如下下語語句句形形式式，，抽抽取取到到建建立立在在單單獨(dú)獨(dú)表表空空間間上上的的中中間間表表中中：：CREATETABLE...ASSELECT……INSERT/*+APPEND*/ASSELECT……以TTS方式式將將中中間間表表的的表表空空間間傳傳輸輸?shù)降綌?shù)數(shù)據(jù)據(jù)倉倉庫庫之之中中。。exptransportable_tablespace=Yes……FTP中間間表表表表空空間間的的數(shù)數(shù)據(jù)據(jù)文文件件imptransportable_tablespace=Yes……在數(shù)據(jù)據(jù)倉庫庫中對(duì)對(duì)中間間表進(jìn)進(jìn)行各各種數(shù)數(shù)據(jù)歸歸并等等清洗洗工作作，并并建立立需要要的各各種索索引。。通過exchange技術(shù)，，將中中間表表數(shù)據(jù)據(jù)及索索引直直接交交換到到分區(qū)區(qū)表中中。Altertable<分區(qū)表表>exchangepartition<分區(qū)名名>withtable<中間表表>includingindexes;分區(qū)交交換的的應(yīng)用用---重復(fù)記記錄刪刪除問題描描述：：在在使用用SQL*Loader進(jìn)行數(shù)數(shù)據(jù)加加載sor_acct_dcc_saamt_c表時(shí)，，由于于操作作失誤誤，重重復(fù)加加載，，導(dǎo)致致分區(qū)區(qū)ETL_LOAD_DATE_0606出現(xiàn)重重復(fù)記記錄，，也使使得兩兩個(gè)唯唯一索索引：：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分區(qū)不不可用用（UNUSABLE）。用戶在試試圖重新新創(chuàng)建該該分區(qū)索索引時(shí)，，出現(xiàn)如如下錯(cuò)誤誤：SQL>alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606;alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606*ORA-01452:cannotCREATEUNIQUEINDEX;duplicatekeysfound分區(qū)交換換的應(yīng)用用---重復(fù)記錄錄刪除在試圖刪刪除該分分區(qū)的重重復(fù)記錄錄時(shí)，又又出現(xiàn)如如下錯(cuò)誤誤：SQL>deletefromsor_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:index'GYFX.IDX_SAACNAMT_C_1'orpartitionofsuchindexisinunusablestate分區(qū)交換換的應(yīng)用用---重復(fù)記錄錄刪除簡單辦法法是徹底底刪除這這兩個(gè)唯唯一索引引，重新新創(chuàng)建。。數(shù)據(jù)量大大，時(shí)間間太長。。影響系統(tǒng)統(tǒng)的可用用性。更完備的的解決方方式創(chuàng)建一個(gè)個(gè)與sor_acct_dcc_saamt_c結(jié)構(gòu)一樣樣的臨時(shí)時(shí)表test。SQL>createtabletestasselect*fromsor_acct_dcc_saamt_cwhere1=2;將sor_acct_dcc_saamt_c表分區(qū)ETL_LOAD_DATE_0606數(shù)據(jù)交換到到臨時(shí)表test。。SQL>altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;分區(qū)交換的的應(yīng)用---重復(fù)記錄刪刪除更完備的解解決方式刪除test中的重復(fù)記記錄deletefromtestwhererowidnotin(selectmin(rowid)fromtestgroupbyETL_LOAD_DATE,CUST_ACCT_NO,SA_CURR_COD,SA_CURR_IDEN);因?yàn)閠est表沒有任何何索引，可可避免上述述ORA-01502錯(cuò)誤。將臨時(shí)表test數(shù)據(jù)交換回回sor_acct_dcc_saamt_c表分區(qū)ETL_LOAD_DATE_0606。altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;分區(qū)交換的的應(yīng)用---重復(fù)記錄刪刪除更完備的解解決方式重新創(chuàng)建創(chuàng)創(chuàng)建該分區(qū)區(qū)索引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;此時(shí)重復(fù)記錄錄已經(jīng)刪除，，可避免上述述ORA-01452錯(cuò)誤現(xiàn)有系統(tǒng)實(shí)施施分區(qū)的經(jīng)驗(yàn)驗(yàn)分區(qū)對(duì)象的確確定：存儲(chǔ)空空間最大的前前20個(gè)表Select*from(Select*fromdba_segmentsorderbybytesdesc)whererownum<=20;分析大表的操操作行為Select*from(Selectsql_text,executionsfromv$sqlareawhereupper(sql_text)like‘%SB_ZSXX%’orderbyexecutionsdesc)whererownum<=20;綜合其它分區(qū)區(qū)因素的考慮慮：性能，數(shù)數(shù)據(jù)遷移，備備份，高可用用性，可維護(hù)護(hù)性分區(qū)的評(píng)估性能方面相應(yīng)速度資源消耗（CPU、內(nèi)存、I/O）性能分析工具具的使用：OracleTrace,Autotrace,TKPROF其它方面數(shù)據(jù)遷移能力力數(shù)據(jù)備份和恢恢復(fù)數(shù)據(jù)擴(kuò)展性（（Add,Drop,Exchange,Merge,…）數(shù)據(jù)高可用性性O(shè)racle報(bào)表優(yōu)化技術(shù)術(shù)報(bào)表處理問題題報(bào)表處理是大大部分IT系統(tǒng)是最耗時(shí)、最消消耗資源的模模塊報(bào)表處理，主主要通過FormulaOne、BO等報(bào)表處理工具具實(shí)現(xiàn)SQL語句基本都是是各種統(tǒng)計(jì)運(yùn)運(yùn)算語句。…SUM…GROUPBY各種報(bào)表的表表格單元都是是統(tǒng)計(jì)運(yùn)算語語句生成。統(tǒng)統(tǒng)計(jì)運(yùn)算語句句量非常大統(tǒng)計(jì)運(yùn)運(yùn)算語語句，，基本本都是是從一一些交交易明明細(xì)表表或基基表，，直接接進(jìn)行行匯總總運(yùn)算算操作作其他匯匯總數(shù)數(shù)據(jù)按月匯匯總的的逾期未未歸還還貸款款本金金的統(tǒng)統(tǒng)計(jì)查詢交易明細(xì)查詢自動(dòng)重寫匯總數(shù)數(shù)據(jù)管管理-物物化化視圖圖MaterializedView遼寧省省和四四川省省逾期91－－180天天未歸歸還貸貸款本本金的的貸款款用戶戶總數(shù)數(shù)？按地區(qū)區(qū)匯總總的逾期未未歸還還貸款款本金金的統(tǒng)統(tǒng)計(jì)物化視視圖SQL查詢的的實(shí)例例化物化視視圖可可以設(shè)設(shè)置成成查詢?cè)冎貙憣懝δ苣芩⑿骂愵愋?CompleteorFastForceorNever刷新模式式:ManualAutomated(同步或異異步)查詢重寫寫概述查詢物化化視圖，，而不是是基表，，將極大大提高查查詢統(tǒng)計(jì)計(jì)性能。。查詢重寫寫功能對(duì)對(duì)應(yīng)用透透明。不不需要特特殊權(quán)限限。查詢重寫寫與大小寫寫無關(guān)不支持子子查詢物化視圖圖創(chuàng)建例例SQL>CREATEMATERIALIZEDVIEWsales_summary2TABLESPACEusers3PARALLEL(DEGREE4)4BUILDIMMEDIATE5ENABLEQUERYREWRITE6AS7SELECTd_name,8SUM(s.quantity_sold),8SUM(s.amount_sold)9FROMsaless,productsp10WHEREd_id=d_id11GROUPBYd_name;物化視圖創(chuàng)建建和查詢重寫寫例SQL>SELECTd_name,SUM(s.quantity_sold),2SUM(s.amount_sold)3FROMsaless,productsp4WHEREd_id=d_id5GROUPBYd_name;SQL>selectoperation,object_name2fromv$sql_plan3whereobject_namelike'SALES%';OPERATIONNAME---------------------------------------SELECTSTATEMENTTABLEACCESSSALES_SUMMARY報(bào)表優(yōu)化的基基本思路以報(bào)表為單位位，分析現(xiàn)有有報(bào)表的SQL語句?？偨Y(jié)統(tǒng)統(tǒng)計(jì)運(yùn)算SQL的共同規(guī)律，，作為設(shè)計(jì)物物化視圖的考考慮對(duì)象。定義相應(yīng)的物物化視圖。包包括SQL查詢語句的編編寫，刷新方方式的確定等等評(píng)價(jià)原有統(tǒng)計(jì)計(jì)運(yùn)算SQL語句的執(zhí)行計(jì)計(jì)劃，是否被被Oracle查詢重寫到相相應(yīng)的物化視視圖評(píng)價(jià)如何在物物化視圖上創(chuàng)創(chuàng)建索引評(píng)估所有物化化視圖數(shù)據(jù)和和索引的空間間消耗情況，，從而確定物物化視圖數(shù)據(jù)據(jù)和索引表空空間的容量報(bào)表優(yōu)化示例例現(xiàn)有統(tǒng)計(jì)運(yùn)算算語句SELECTts_stab.VIP_ORG_STAFF.VIP_ORGAN3_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2_NAME,sum(ts_dyna.VIP_ACCT_ALL.T1)/100,sum(ts_dyna.VIP_ACCT_ALL.T29)/100))/100FROMts_stab.VIP_ORG_STAFF,ts_dyna.VIP_ACCT_ALLWHERE(ts_dyna.VIP_ACCT_ALL.STAFF_ID=ts_stab.VIP_ORG_STAFF.STAFF_ID)AND(((ts_stab.VIP_ORG_STAFF.VIP_ORGAN3)='org2'OR'ALL'='org2')AND((ts_stab.VIP_ORG_STAFF.VIP_ORGAN2)='ALL'OR'ALL'='ALL')AND((ts_dyna.VIP_ACCT_ALL.ACCOUNT_DATE)>='200401'AND(ts_dyna.VIP_ACCT_ALL.ACCOUNT_DATE)<='200401'))GROUPBYts_stab.VIP_ORG_STAFF.VIP_ORGAN3_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2_NAME報(bào)表優(yōu)化示例例物化視圖定義義CREATEMATERIALIZEDVIEWMV_01TABLESPACETS_TAB_DYNPARALLEL(DEGREE2)BUILDIMMEDIATEREFRESHCOMPLETEONDEMANDENABLEQUERYREWRITEASSELECTts_stab.VIP_ORG_STAFF.VIP_ORGAN3_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN3,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2,ts_dyna.VIP_ACCT_ALL.ACCOUNT_DATE,sum(ts_dyna.VIP_ACCT_ALL.T1),sum(ts_dyna.VIP_ACCT_ALL.T29)FROMts_stab.VIP_ORG_STAFF,ts_dyna.VIP_ACCT_ALLWHERE(ts_dyna.VIP_ACCT_ALL.STAFF_ID=ts_stab.VIP_ORG_STAFF.STAFF_ID)GROUPBYts_stab.VIP_ORG_STAFF.VIP_ORGAN3_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN3,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2,ts_dyna.VIP_ACCT_ALL.ACCOUNT_DATE報(bào)表優(yōu)化示例例TABLESPACETS_TAB_DYN：物化視圖MV_01存放的表空間間。建議為物物化視圖單獨(dú)獨(dú)設(shè)立表空間間PARALLEL(DEGREE2)：并行度設(shè)計(jì)為為2（與目前前的CPU個(gè)數(shù)相等）BUILDIMMEDIATE：：創(chuàng)建MV_01的同時(shí)，產(chǎn)生生匯總數(shù)據(jù)REFRESHCOMPLETEONDEMAND：完全刷新ENABLEQUERYREWRITE：：啟動(dòng)查詢語句句重寫功能報(bào)表優(yōu)化示例例SELECT短語應(yīng)包含的的字段原SELECT語句的所有非非統(tǒng)計(jì)運(yùn)算字字段。ts_stab.VIP_ORG_STAFF.VIP_ORGAN3_NAME,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2_NAME,原SELECT語句中所有查查詢條件涉及及到的字段。。如：ts_stab.VIP_ORG_STAFF.VIP_ORGAN3,ts_stab.VIP_ORG_STAFF.VIP_ORGAN2,ts_dyna.VIP_ACCT_ALL.ACCOUNT_DATE,原子化（Primitive）的統(tǒng)計(jì)運(yùn)算字字段。即去掉掉表達(dá)式的統(tǒng)統(tǒng)計(jì)運(yùn)算字段段sum(ts_dyna.VIP_ACCT_ALL.T1),sum(ts_dyna.VIP_ACCT_ALL.T29),報(bào)表優(yōu)化示示例創(chuàng)建物化視視圖上的索索引createindexidx_MV_01_01onMV_01(VIP_ORGAN3)tablespaceTS_IND_DYNA;物化視圖上上的刷新定期執(zhí)行如如下命令，，可自動(dòng)進(jìn)進(jìn)行物化視視圖的刷新新execdbms_mview.refresh('TS_DYNA.MV_01');報(bào)表優(yōu)化示示例初始化參數(shù)數(shù)的設(shè)置query_re