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

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

并行處理應用經驗Oracle的分區(qū)技術

分區(qū)技術內容什么是分區(qū)?分區(qū)的好處?如何實施分區(qū)?如何評估分區(qū)的效果?

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

高性能–>數據維護能力->實施難度–>高可用性（故障屏蔽能力）分區(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

必須是確定值每個分區(qū)可以單獨指定物理屬性123范圍分區(qū)例最早、最最經典的的分區(qū)算算法Range分區(qū)通過過對分區(qū)區(qū)字段值值的范圍圍進行分分區(qū)Range分區(qū)特別別適合于于按時間間周期進進行數據據的存儲儲。日、、周、月月、年等等。數據管理理能力強強數據遷移移數據備份份數據交換換范圍分區(qū)區(qū)的數據據可能不不均勻范圍分區(qū)區(qū)與記錄錄值相關關，實施施難度和和可維護護性相對對較差范圍分區(qū)區(qū)特點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ū)特特點基于分分區(qū)字字段的的HASH值，自自動將將記錄錄插入入到指指定分分區(qū)。。分區(qū)數數一般般是2的冪冪易于實實施總體性性能最最佳適合于于靜態(tài)態(tài)數據據HASH分區(qū)適適合于于數據據的均均勻存存儲HASH分區(qū)特特別適適合于于PDML和partition-wisejoins。。支持(hash)localindexes9i不支持持(hash)globalindexes10g支持(hash)globalindexesHASH分區(qū)數據管管理能能力弱弱HASH分區(qū)對對數據據值無無法控控制列表分分區(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ū)特特點List分區(qū)通過對對分區(qū)字段段的離散值值進行分區(qū)區(qū)。List分區(qū)是不排排序的，而而且分區(qū)之之間沒有關關聯(lián)關系List分區(qū)適合于于對數據離離散值進行行控制。List分區(qū)只支持持單個字段段。List分區(qū)具有與與范圍分區(qū)區(qū)相似的優(yōu)優(yōu)缺點數據管理能能力強List分區(qū)的數據據可能不均均勻List分區(qū)與記錄錄值相關，，實施難度度和可維護護性相對較較差復合分區(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)復合分區(qū)圖圖示復合分區(qū)特特點Oracle支持的Composite分區(qū)：Range-Hash,Range-List既適合于歷歷史數據，，又適合于于數據均勻勻分布與范圍分區(qū)區(qū)一樣提供供高可用性性和管理性性更好的PDML和partition-wisejoins性能實現粒度更更細的操作作支持復合localindexes不支持復合合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提供了更更好的可可用性數據倉庫庫系統(tǒng)中中的建議議Localindex更適合于于數據裝裝載和分分區(qū)維護護在大量數數據統(tǒng)計計時，能能充分利利用Localindex并行查詢詢能力在性能、、高可用用性和可可管理性性之間進進行平衡衡分區(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ū)表設設計原則則表的大小?。寒敱肀淼拇笮⌒〕^1.5GB－2GB，或對于OLTP系統(tǒng)，表表的記錄錄超過1000萬，都應應考慮對對表進行行分區(qū)。。數據訪問問特性：：基于表表的大部部分查詢詢應用，，只訪問問表中少少量的數數據。對對于這樣樣表進行行分區(qū)，，可充分分利用分分區(qū)排除除無關數數據查詢詢的特性性。數據維護護：按時時間段刪刪除成批批的數據據，例如如按月刪刪除歷史史數據。。對于這這樣的表表需要考考慮進行行分區(qū)，，以滿足足維護的的需要。。數據備份份和恢復復：按時間周周期進行行表空間間的備份份時，將將分區(qū)與與表空間間建立對對應關系系。只讀數據據：如果果一個表表中大部部分數據據都是只只讀數據據，通過過對表進進行分區(qū)區(qū)，可將將只讀數數據存儲儲在只讀讀表空間間中，對對于數據據庫的備備份是非非常有益益的。并行數據據操作：：對于經經常執(zhí)行行并行操操作（如如ParallelInsert,ParallelUpdate等）的表表應考慮慮進行分分區(qū)。表的可用用性：當當對表的的部分數數據可用用性要求求很高時時，應考考慮進行行表分區(qū)區(qū)。分區(qū)表的的管理功功能分區(qū)的增增加（ADD））分區(qū)的刪刪除（DROP）分區(qū)的合合并（MERGE）分區(qū)的清清空（TRUNCATE））分區(qū)的交交換（EXCHANGE））分區(qū)的壓壓縮（COALESE）分區(qū)的移移動（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新月份數數據的加加載和索索引的維維護NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002...“滾動窗口””操作-大大量數據高高速裝載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刪除或歸檔最最老月份的數數據OCT

2001OCT

2001新月份數據的的加載和索引引的維護...分區(qū)表和Local索引“滾動窗口””操作-大大量數據高高速裝載分區(qū)交換功能能通過交交換數數據段段，實實現分分區(qū)和和非分分區(qū)表表的數數據交交換。。以及及子分分區(qū)和和分區(qū)區(qū)表的的數據據交換換非常快快捷的的數據據移動動方式式。特特別是是沒有有validation和索引引維護護操作作時Local索引自自動維維護Global索引必必須重重建分區(qū)交交換的的應用用---全全文文檢索索12：00分區(qū)BF_DXX_stage中間表表（1）1：00數據的的加載載（2）建立context索引（3））partition的exchangeBF_DXX表*初初始化化工作作*整整理工工作………2：00分分區(qū)1：00分區(qū)分區(qū)交交換的的應用用---全全文文檢索索第一步步：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地市系統(tǒng)imp卸載文件省級系統(tǒng)FTP數據文件卸載文件數據文件exp遷移移表表空空間間技技術術的的作作用用業(yè)務系統(tǒng)統(tǒng)數據向向數據倉倉庫系統(tǒng)統(tǒng)的遷移移對業(yè)務系系統(tǒng)和數數據倉庫庫系統(tǒng)的的數據進進行定期期歸檔數據倉庫庫向數據據集市的的數據遷遷移數據對外外發(fā)布按表空間間進行時時間點的的數據恢恢復(TSPITR)遷移表空空間技術術的優(yōu)點點性能大大大高于export/import或PL/SQL編寫的程程序由于Dmp文件只包包含表空空間的結結構信息息，因此此該技術術的真正正開銷在在于數據據文件的的傳輸。。對源系統(tǒng)統(tǒng)的影響響非常小小只需要將將被遷移移的表空空間設置置為只讀讀方式可同時傳傳輸索引引數據，，避免在在目的數數據庫中中重建索索引分區(qū)區(qū)交交換換的的應應用用---ETL在源源系系統(tǒng)統(tǒng)中中，，將將需需要要抽抽取取的的數數據據以以如如下下語語句句形形式式，，抽抽取取到到建建立立在在單單獨獨表表空空間間上上的的中中間間表表中中：：CREATETABLE...ASSELECT……INSERT/*+APPEND*/ASSELECT……以TTS方式式將將中中間間表表的的表表空空間間傳傳輸輸到到數數據據倉倉庫庫之之中中。。exptransportable_tablespace=Yes……FTP中間間表表表表空空間間的的數數據據文文件件imptransportable_tablespace=Yes……在數據據倉庫庫中對對中間間表進進行各各種數數據歸歸并等等清洗洗工作作，并并建立立需要要的各各種索索引。。通過exchange技術，，將中中間表表數據據及索索引直直接交交換到到分區(qū)區(qū)表中中。Altertable<分區(qū)表表>exchangepartition<分區(qū)名名>withtable<中間表表>includingindexes;分區(qū)交交換的的應用用---重復記記錄刪刪除問題描描述：：在在使用用SQL*Loader進行數數據加加載sor_acct_dcc_saamt_c表時，，由于于操作作失誤誤，重重復加加載，，導致致分區(qū)區(qū)ETL_LOAD_DATE_0606出現重重復記記錄，，也使使得兩兩個唯唯一索索引：：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分區(qū)不不可用用（UNUSABLE）。用戶在試試圖重新新創(chuàng)建該該分區(qū)索索引時，，出現如如下錯誤誤：SQL>alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606;alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606*ORA-01452:cannotCREATEUNIQUEINDEX;duplicatekeysfound分區(qū)交換換的應用用---重復記錄錄刪除在試圖刪刪除該分分區(qū)的重重復記錄錄時，又又出現如如下錯誤誤：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ū)交換換的應用用---重復記錄錄刪除簡單辦法法是徹底底刪除這這兩個唯唯一索引引，重新新創(chuàng)建。。數據量大大，時間間太長。。影響系統(tǒng)統(tǒng)的可用用性。更完備的的解決方方式創(chuàng)建一個個與sor_acct_dcc_saamt_c結構一樣樣的臨時時表test。SQL>createtabletestasselect*fromsor_acct_dcc_saamt_cwhere1=2;將sor_acct_dcc_saamt_c表分區(qū)ETL_LOAD_DATE_0606數據交換到到臨時表test。。SQL>altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;分區(qū)交換的的應用---重復記錄刪刪除更完備的解解決方式刪除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表分區(qū)ETL_LOAD_DATE_0606。altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;分區(qū)交換的的應用---重復記錄刪刪除更完備的解解決方式重新創(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;此時重復記錄錄已經刪除，，可避免上述述ORA-01452錯誤現有系統(tǒng)實施施分區(qū)的經驗驗分區(qū)對象的確確定：存儲空空間最大的前前20個表Select*from(Select*fromdba_segmentsorderbybytesdesc)whererownum<=20;分析大表的操操作行為Select*from(Selectsql_text,executionsfromv$sqlareawhereupper(sql_text)like‘%SB_ZSXX%’orderbyexecutionsdesc)whererownum<=20;綜合其它分區(qū)區(qū)因素的考慮慮：性能，數數據遷移，備備份，高可用用性，可維護護性分區(qū)的評估性能方面相應速度資源消耗（CPU、內存、I/O）性能分析工具具的使用：OracleTrace,Autotrace,TKPROF其它方面數據遷移能力力數據備份和恢恢復數據擴展性（（Add,Drop,Exchange,Merge,…）數據高可用性性Oracle報表優(yōu)化技術術報表處理問題題報表處理是大大部分IT系統(tǒng)是最耗時、最消消耗資源的模模塊報表處理，主主要通過FormulaOne、BO等報表處理工具具實現SQL語句基本都是是各種統(tǒng)計運運算語句?！璖UM…GROUPBY各種報表的表表格單元都是是統(tǒng)計運算語語句生成。統(tǒng)統(tǒng)計運算語句句量非常大統(tǒng)計運運算語語句，，基本本都是是從一一些交交易明明細表表或基基表，，直接接進行行匯總總運算算操作作其他匯匯總數數據按月匯匯總的的逾期未未歸還還貸款款本金金的統(tǒng)統(tǒng)計查詢交易明細查詢自動重寫匯總數數據管管理-物物化化視圖圖MaterializedView遼寧省省和四四川省省逾期91－－180天天未歸歸還貸貸款本本金的的貸款款用戶戶總數數？按地區(qū)區(qū)匯總總的逾期未未歸還還貸款款本金金的統(tǒng)統(tǒng)計物化視視圖SQL查詢的的實例例化物化視視圖可可以設設置成成查詢詢重寫寫功能能刷新類類型:CompleteorFastForceorNever刷新模式式:ManualAutomated(同步或異異步)查詢重寫寫概述查詢物化化視圖，，而不是是基表，，將極大大提高查查詢統(tǒng)計計性能。。查詢重寫寫功能對對應用透透明。不不需要特特殊權限限。查詢重寫寫與大小寫寫無關不支持子子查詢物化視圖圖創(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報表優(yōu)化的基基本思路以報表為單位位，分析現有有報表的SQL語句。總結統(tǒng)統(tǒng)計運算SQL的共同規(guī)律，，作為設計物物化視圖的考考慮對象。定義相應的物物化視圖。包包括SQL查詢語句的編編寫，刷新方方式的確定等等評價原有統(tǒng)計計運算SQL語句的執(zhí)行計計劃，是否被被Oracle查詢重寫到相相應的物化視視圖評價如何在物物化視圖上創(chuàng)創(chuàng)建索引評估所有物化化視圖數據和和索引的空間間消耗情況，，從而確定物物化視圖數據據和索引表空空間的容量報表優(yōu)化示例例現有統(tǒng)計運算算語句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報表優(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報表優(yōu)化示例例TABLESPACETS_TAB_DYN：物化視圖MV_01存放的表空間間。建議為物物化視圖單獨獨設立表空間間PARALLEL(DEGREE2)：并行度設計為為2（與目前前的CPU個數相等）BUILDIMMEDIATE：：創(chuàng)建MV_01的同時，產生生匯總數據REFRESHCOMPLETEONDEMAND：完全刷新ENABLEQUERYREWRITE：：啟動查詢語句句重寫功能報表優(yōu)化示例例SELECT短語應包含的的字段原SELECT語句的所有非非統(tǒng)計運算字字段。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)計運算字字段。即去掉掉表達式的統(tǒng)統(tǒng)計運算字段段sum(ts_dyna.VIP_ACCT_ALL.T1),sum(ts_dyna.VIP_ACCT_ALL.T29),報表優(yōu)化示示例創(chuàng)建物化視視圖上的索索引createindexidx_MV_01_01onMV_01(VIP_ORGAN3)tablespaceTS_IND_DYNA;物化視圖上上的刷新定期執(zhí)行如如下命令，，可自動進進行物化視視圖的刷新新execdbms_mview.refresh('TS_DYNA.MV_01');報表優(yōu)化示示例初始化參數數的設置query_re