Oracle高級技術之大數(shù)據(jù)處理70課件

Oracle高級技術之大數(shù)據(jù)處理702022/12/26Oracle高級技術之大數(shù)據(jù)處理70Oracle高級技術之大數(shù)據(jù)處理702022/12/26Or1交流內容分區(qū)技術報表優(yōu)化技術

并行處理應用經驗Oracle高級技術之大數(shù)據(jù)處理70交流內容分區(qū)技術Oracle高級技術之大數(shù)據(jù)處理702Oracle的分區(qū)技術Oracle高級技術之大數(shù)據(jù)處理70Oracle的分區(qū)技術Oracle高級技術之大數(shù)據(jù)處理703

分區(qū)技術內容什么是分區(qū)?分區(qū)的好處?如何實施分區(qū)?如何評估分區(qū)的效果?Oracle高級技術之大數(shù)據(jù)處理70分區(qū)技術內容什么是分區(qū)?Oracle高級技術之大數(shù)據(jù)處理4

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

高性能

–>數(shù)據(jù)維護能力->實施難度

–>高可用性（故障屏蔽能力）Oracle高級技術之大數(shù)據(jù)處理70分區(qū)技術的效益和目標性能Oracle高級技術之大數(shù)據(jù)處理707分區(qū)方法分區(qū)方法:范圍---8Hash---8i列表---9i組合---8iRange

partitioningHash

partitioningComposite

partitioningList

partitioningOracle高級技術之大數(shù)據(jù)處理70分區(qū)方法分區(qū)方法:Range

partitioningHas8123CREATETABLEsales(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ū)例Oracle高級技術之大數(shù)據(jù)處理70123CREATETABLEsales分區(qū)字段：wee9最早、最經典的分區(qū)算法Range分區(qū)通過對分區(qū)字段值的范圍進行分區(qū)Range分區(qū)特別適合于按時間周期進行數(shù)據(jù)的存儲。日、周、月、年等。數(shù)據(jù)管理能力強數(shù)據(jù)遷移數(shù)據(jù)備份數(shù)據(jù)交換范圍分區(qū)的數(shù)據(jù)可能不均勻范圍分區(qū)與記錄值相關，實施難度和可維護性相對較差范圍分區(qū)特點Oracle高級技術之大數(shù)據(jù)處理70最早、最經典的分區(qū)算法范圍分區(qū)特點Oracle高級技術之大數(shù)10Hash分區(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)Oracle高級技術之大數(shù)據(jù)處理70Hash分區(qū)例createtableCUSTOMERS11Hash分區(qū)特點基于分區(qū)字段的HASH值，自動將記錄插入到指定分區(qū)。分區(qū)數(shù)一般是2的冪易于實施總體性能最佳適合于靜態(tài)數(shù)據(jù)HASH分區(qū)適合于數(shù)據(jù)的均勻存儲HASH分區(qū)特別適合于PDML和partition-wisejoins。支持(hash)localindexes9i不支持(hash)globalindexes10g支持(hash)globalindexesHASH分區(qū)數(shù)據(jù)管理能力弱HASH分區(qū)對數(shù)據(jù)值無法控制Oracle高級技術之大數(shù)據(jù)處理70Hash分區(qū)特點基于分區(qū)字段的HASH值，自動將記錄插入到指12列表分區(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)Oracle高級技術之大數(shù)據(jù)處理70列表分區(qū)例createtableaddresses(.13列表分區(qū)特點List分區(qū)通過對分區(qū)字段的離散值進行分區(qū)。List分區(qū)是不排序的，而且分區(qū)之間沒有關聯(lián)關系List分區(qū)適合于對數(shù)據(jù)離散值進行控制。List分區(qū)只支持單個字段。List分區(qū)具有與范圍分區(qū)相似的優(yōu)缺點數(shù)據(jù)管理能力強List分區(qū)的數(shù)據(jù)可能不均勻List分區(qū)與記錄值相關，實施難度和可維護性相對較差Oracle高級技術之大數(shù)據(jù)處理70列表分區(qū)特點List分區(qū)通過對分區(qū)字段的離散值進行分區(qū)。Or14復合分區(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)Oracle高級技術之大數(shù)據(jù)處理70復合分區(qū)例createtabledaily_trans_15復合分區(qū)圖示Oracle高級技術之大數(shù)據(jù)處理70復合分區(qū)圖示Oracle高級技術之大數(shù)據(jù)處理7016復合分區(qū)特點Oracle支持的Composite分區(qū)：Range-Hash,Range-List既適合于歷史數(shù)據(jù)，又適合于數(shù)據(jù)均勻分布與范圍分區(qū)一樣提供高可用性和管理性更好的PDML和partition-wisejoins性能實現(xiàn)粒度更細的操作支持復合localindexes不支持復合composite

globalindexes？Oracle高級技術之大數(shù)據(jù)處理70復合分區(qū)特點Oracle支持的Composite分區(qū)：Ora17分區(qū)索引不分區(qū)分區(qū)不分區(qū)

分區(qū)表索引√√√√Oracle高級技術之大數(shù)據(jù)處理70分區(qū)索引表索引√√√√Oracle高級技術之大數(shù)據(jù)處理7018GlobalNonpartitionedindexLocalpartitionedindexGlobalPartitionedIndex不同的分區(qū)索引紹興杭州溫州03年04年…08年Oracle高級技術之大數(shù)據(jù)處理70GlobalLocalpartitionedindexG19分區(qū)索引分區(qū)表索引的分類：LocalPrefixedindexLocalNon-prefiexedindexGlobalPrefixedindexNonPartitionIndexGlobal索引的分區(qū)不同與表分區(qū)Local索引的分區(qū)與表分區(qū)相同Anindexisprefixedifitispartitionedonaleftprefixoftheindexcolumns.分區(qū)表上的非分區(qū)索引等同于Global索引Oracle高級技術之大數(shù)據(jù)處理70分區(qū)索引分區(qū)表索引的分類：Oracle高級技術之大數(shù)據(jù)處理720分區(qū)索引Global索引必須是范圍分區(qū)---9i之前Global索引可以是HASH分區(qū)--10g新特性Global索引不支持Bitmap索引Unique索引必須是prefixed，或者包含分區(qū)字段Local索引（non-prefixed,non-unique）可以不包含分區(qū)字段Oracle高級技術之大數(shù)據(jù)處理70分區(qū)索引Global索引必須是范圍分區(qū)---9i之前Or21createindexcust_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ū)索引舉例Oracle高級技術之大數(shù)據(jù)處理70createindexcust_idx1oncust22分區(qū)表索引的使用OLTP系統(tǒng)中的建議Global和uniquelocalindex性能優(yōu)于nonuniquelocalindexLocalindex提供了更好的可用性數(shù)據(jù)倉庫系統(tǒng)中的建議Localindex更適合于數(shù)據(jù)裝載和分區(qū)維護在大量數(shù)據(jù)統(tǒng)計時，能充分利用Localindex并行查詢能力在性能、高可用性和可管理性之間進行平衡Oracle高級技術之大數(shù)據(jù)處理70分區(qū)表索引的使用OLTP系統(tǒng)中的建議Oracle高級技術之大23分區(qū)索引選擇策略Oracle高級技術之大數(shù)據(jù)處理70分區(qū)索引選擇策略Oracle高級技術之大數(shù)據(jù)處理7024分區(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’);Oracle高級技術之大數(shù)據(jù)處理70分區(qū)裁剪功能Partitionpruning:Only25分區(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_SUMM231234

1select*fromdaily_trans_summ2*wheretrans_datetimein('25-jun-2001','28-jun-2001')PartitionPartitionOperationOptionsObjectNameStartStop-----------------------------------------------------------------------------SELECTSTATEMENTPARTITIONRANGEINLISTKEY(INLIST)KEY(INLIST)TABLEACCESSFULLDAILY_TRANS_SUMMKEY(INLIST)KEY(INLIST)

Oracle高級技術之大數(shù)據(jù)處理70分區(qū)裁剪舉例1select*fromdaily_t26Nonpartition-wisejoinFullpartition-wisejoinPartialpartition-wisejoinQueryslavePartitionPartitionedtable123Partition-WiseJoinOracle高級技術之大數(shù)據(jù)處理70Nonpartition-wisejoinFullpar27Partition-wiseJoinsTablesandindexesthatarepartitionedidenticallyareequi-partitioned.Afullpartition-wisejoinoccurswhenjoiningtwoequi-partitionedtablesthatarepartitionedonthejoinkey.Oraclesplitsthejoinintojoinsofpairsofpartitions.Apartialpartition-wisejoinoccurswhenonlyoneofthetablesispartitionedonthejoinkey.Partition-wisejoinsoccurwhenjoiningahashpartitiontabletoacompositepartitiontableifthehashpartitioningandsub-partitioningisonthejoinkey.Oracleassignsparallelqueryslavestoprocessthepartitionjoins.Oracle高級技術之大數(shù)據(jù)處理70Partition-wiseJoinsTablesand28Partition-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_DATA393424TABLEACCESSFULLCUSTOMERS18Oracle高級技術之大數(shù)據(jù)處理70Partition-wiseJoins舉例1selec29分區(qū)表設計原則表的大小：當表的大小超過1.5GB－2GB，或對于OLTP系統(tǒng)，表的記錄超過1000萬，都應考慮對表進行分區(qū)。

數(shù)據(jù)訪問特性：基于表的大部分查詢應用，只訪問表中少量的數(shù)據(jù)。對于這樣表進行分區(qū)，可充分利用分區(qū)排除無關數(shù)據(jù)查詢的特性。

數(shù)據(jù)維護：按時間段刪除成批的數(shù)據(jù)，例如按月刪除歷史數(shù)據(jù)。對于這樣的表需要考慮進行分區(qū)，以滿足維護的需要。數(shù)據(jù)備份和恢復：按時間周期進行表空間的備份時，將分區(qū)與表空間建立對應關系。只讀數(shù)據(jù)：如果一個表中大部分數(shù)據(jù)都是只讀數(shù)據(jù)，通過對表進行分區(qū)，可將只讀數(shù)據(jù)存儲在只讀表空間中，對于數(shù)據(jù)庫的備份是非常有益的。并行數(shù)據(jù)操作：對于經常執(zhí)行并行操作（如ParallelInsert,ParallelUpdate等）的表應考慮進行分區(qū)。表的可用性：當對表的部分數(shù)據(jù)可用性要求很高時，應考慮進行表分區(qū)。Oracle高級技術之大數(shù)據(jù)處理70分區(qū)表設計原則表的大小：當表的大小超過1.5GB－2GB，或30分區(qū)表的管理功能分區(qū)的增加（ADD）分區(qū)的刪除（DROP）分區(qū)的合并（MERGE）分區(qū)的清空（TRUNCATE）分區(qū)的交換（EXCHANGE）分區(qū)的壓縮（COALESE）分區(qū)的移動（MOVE）分區(qū)的分離（SPLIT）修改分區(qū)的DefaultAttribute分區(qū)的更名（RENAME）Oracle高級技術之大數(shù)據(jù)處理70分區(qū)表的管理功能分區(qū)的增加（ADD）Oracle高級技術之大31分區(qū)索引的管理功能分區(qū)索引的刪除（DROP）分區(qū)索引的修改（MODIFY）分區(qū)索引DefaultAttribute的修改分區(qū)索引的重建（REBUILD）分區(qū)索引的更名（RENAME）分區(qū)索引的分離（SPLIT）分區(qū)索引的UnusableOracle高級技術之大數(shù)據(jù)處理70分區(qū)索引的管理功能分區(qū)索引的刪除（DROP）Oracle高級32分區(qū)表和Local索引OCT

2002SEP

2002AUG

2002NOV

2001OCT

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

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002NOV

2002...“滾動窗口”操作-大量數(shù)據(jù)高速裝載Oracle高級技術之大數(shù)據(jù)處理70分區(qū)表和Local索引OCT

2002SEP

2002AUG33OCT

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ù)據(jù)OCT

2001OCT

2001新月份數(shù)據(jù)的加載和索引的維護...分區(qū)表和Local索引“滾動窗口”操作-大量數(shù)據(jù)高速裝載Oracle高級技術之大數(shù)據(jù)處理70OCT

2002SEP

2002NOV

2002NOV

234分區(qū)交換功能通過交換數(shù)據(jù)段，實現(xiàn)分區(qū)和非分區(qū)表的數(shù)據(jù)交換。以及子分區(qū)和分區(qū)表的數(shù)據(jù)交換非常快捷的數(shù)據(jù)移動方式。特別是沒有validation和索引維護操作時Local索引自動維護Global索引必須重建Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換功能通過交換數(shù)據(jù)段，實現(xiàn)分區(qū)和非分區(qū)表的數(shù)據(jù)交換。以35分區(qū)交換的應用---全文檢索12：00分區(qū)

BF_DXX_stage中間表（1）1：00數(shù)據(jù)的加載

（2）建立context索引

（3）partition的exchangeBF_DXX表*初始化工作*整理工作……2：00分區(qū)1：00分區(qū)

Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---全文檢索12：00分區(qū)BF_DXX_36分區(qū)交換的應用---全文檢索第一步：1：00數(shù)據(jù)的加載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;Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---全文檢索第一步：1：00數(shù)據(jù)的加載Or37遷移表空間(TransportableTablespace)技術簡介

第一步：exptransport_tablespace=yes第二步：FTP數(shù)據(jù)文件和dmp文件第三步：imptransport_tablespace=yes地市系統(tǒng)imp卸載文件省級系統(tǒng)FTP數(shù)據(jù)文件卸載文件數(shù)據(jù)文件expOracle高級技術之大數(shù)據(jù)處理70遷移表空間(TransportableTablespace38遷移表空間技術的作用業(yè)務系統(tǒng)數(shù)據(jù)向數(shù)據(jù)倉庫系統(tǒng)的遷移

對業(yè)務系統(tǒng)和數(shù)據(jù)倉庫系統(tǒng)的數(shù)據(jù)進行定期歸檔

數(shù)據(jù)倉庫向數(shù)據(jù)集市的數(shù)據(jù)遷移

數(shù)據(jù)對外發(fā)布

按表空間進行時間點的數(shù)據(jù)恢復(TSPITR)

Oracle高級技術之大數(shù)據(jù)處理70遷移表空間技術的作用業(yè)務系統(tǒng)數(shù)據(jù)向數(shù)據(jù)倉庫系統(tǒng)的遷移Ora39遷移表空間技術的優(yōu)點性能大大高于export/import或PL/SQL編寫的程序

由于Dmp文件只包含表空間的結構信息，因此該技術的真正開銷在于數(shù)據(jù)文件的傳輸。

對源系統(tǒng)的影響非常小

只需要將被遷移的表空間設置為只讀方式

可同時傳輸索引數(shù)據(jù)，避免在目的數(shù)據(jù)庫中重建索引

Oracle高級技術之大數(shù)據(jù)處理70遷移表空間技術的優(yōu)點性能大大高于export/import或40分區(qū)交換的應用---ETL在源系統(tǒng)中，將需要抽取的數(shù)據(jù)以如下語句形式，抽取到建立在單獨表空間上的中間表中：CREATETABLE...ASSELECT…INSERT/*+APPEND*/ASSELECT…以TTS方式將中間表的表空間傳輸?shù)綌?shù)據(jù)倉庫之中。exptransportable_tablespace=Yes…FTP中間表表空間的數(shù)據(jù)文件imptransportable_tablespace=Yes…在數(shù)據(jù)倉庫中對中間表進行各種數(shù)據(jù)歸并等清洗工作，并建立需要的各種索引。通過exchange技術，將中間表數(shù)據(jù)及索引直接交換到分區(qū)表中。Altertable<分區(qū)表>exchangepartition<分區(qū)名>withtable<中間表>includingindexes;

Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---ETL在源系統(tǒng)中，將需要抽取的數(shù)據(jù)以如41分區(qū)交換的應用---重復記錄刪除問題描述：在使用SQL*Loader進行數(shù)據(jù)加載sor_acct_dcc_saamt_c表時，由于操作失誤，重復加載，導致分區(qū)ETL_LOAD_DATE_0606出現(xiàn)重復記錄，也使得兩個唯一索引：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分區(qū)不可用（UNUSABLE）。用戶在試圖重新創(chuàng)建該分區(qū)索引時，出現(xiàn)如下錯誤：SQL>alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606;alterindexIDX_SAACNAMT_C_2rebuildpartitionETL_LOAD_DATE_0606*ORA-01452:cannotCREATEUNIQUEINDEX;duplicatekeysfoundOracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---重復記錄刪除問題描述：在使用SQL*L42分區(qū)交換的應用---重復記錄刪除在試圖刪除該分區(qū)的重復記錄時，又出現(xiàn)如下錯誤：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'orpartitionofsuchindexisinunusablestateOracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---重復記錄刪除在試圖刪除該分區(qū)的重復記錄時43分區(qū)交換的應用---重復記錄刪除簡單辦法是徹底刪除這兩個唯一索引，重新創(chuàng)建。

數(shù)據(jù)量大，時間太長。影響系統(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數(shù)據(jù)交換到臨時表test。SQL>altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---重復記錄刪除簡單辦法是徹底刪除這兩個唯一44分區(qū)交換的應用---重復記錄刪除更完備的解決方式刪除test中的重復記錄deletefromtestwhererowidnotin(selectmin(rowid)fromtestgroupbyETL_LOAD_DATE,CUST_ACCT_NO,SA_CURR_COD,SA_CURR_IDEN);因為test表沒有任何索引，可避免上述ORA-01502錯誤。將臨時表test數(shù)據(jù)交換回sor_acct_dcc_saamt_c表分區(qū)ETL_LOAD_DATE_0606。altertablesor_acct_dcc_saamt_cexchangepartitionETL_LOAD_DATE_0606withtabletest;Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---重復記錄刪除更完備的解決方式Oracle45分區(qū)交換的應用---重復記錄刪除更完備的解決方式重新創(chuàng)建創(chuàng)建該分區(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錯誤Oracle高級技術之大數(shù)據(jù)處理70分區(qū)交換的應用---重復記錄刪除更完備的解決方式Oracle46現(xiàn)有系統(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ū)因素的考慮：性能，數(shù)據(jù)遷移，備份，高可用性，可維護性Oracle高級技術之大數(shù)據(jù)處理70現(xiàn)有系統(tǒng)實施分區(qū)的經驗分區(qū)對象的確定：存儲空間最大的前20個47分區(qū)的評估性能方面相應速度資源消耗（CPU、內存、I/O）性能分析工具的使用：OracleTrace,Autotrace,TKPROF其它方面數(shù)據(jù)遷移能力數(shù)據(jù)備份和恢復數(shù)據(jù)擴展性（Add,Drop,Exchange,Merge,…）數(shù)據(jù)高可用性Oracle高級技術之大數(shù)據(jù)處理70分區(qū)的評估性能方面Oracle高級技術之大數(shù)據(jù)處理7048Oracle報表優(yōu)化技術Oracle高級技術之大數(shù)據(jù)處理70Oracle報表優(yōu)化技術Oracle高級技術之大數(shù)據(jù)處理7049報表處理問題報表處理是大部分IT系統(tǒng)是最耗時、最消耗資源的模塊

報表處理，主要通過FormulaOne、BO等報表處理工具實現(xiàn)SQL語句基本都是各種統(tǒng)計運算語句。…SUM…GROUPBY各種報表的表格單元都是統(tǒng)計運算語句生成。統(tǒng)計運算語句量非常大統(tǒng)計運算語句，基本都是從一些交易明細表或基表，直接進行匯總運算操作Oracle高級技術之大數(shù)據(jù)處理70報表處理問題報表處理是大部分IT系統(tǒng)是最耗時、最消耗資源的模50其他匯總數(shù)據(jù)按月匯總的逾期未歸還貸款本金的統(tǒng)計查詢交易明細查詢自動重寫匯總數(shù)據(jù)管理-物化視圖

MaterializedView遼寧省和四川省逾期91－180天未歸還貸款本金的貸款用戶總數(shù)？按地區(qū)匯總的逾期未歸還貸款本金的統(tǒng)計Oracle高級技術之大數(shù)據(jù)處理70其他匯總數(shù)據(jù)按月匯總的查詢交易查詢匯總數(shù)據(jù)管理-物化視圖51物化視圖SQL查詢的實例化物化視圖可以設置成查詢重寫功能刷新類型:CompleteorFastForceorNever刷新模式:ManualAutomated(同步或異步)Oracle高級技術之大數(shù)據(jù)處理70物化視圖SQL查詢的實例化Oracle高級技術之大數(shù)據(jù)處理752查詢重寫概述查詢物化視圖，而不是基表，將極大提高查詢統(tǒng)計性能。查詢重寫功能對應用透明。不需要特殊權限。查詢重寫與大小寫無關不支持子查詢Oracle高級技術之大數(shù)據(jù)處理70查詢重寫概述查詢物化視圖，而不是基表，將極大提高查詢統(tǒng)計性能53物化視圖創(chuàng)建例SQL>CREATEMATERIALIZEDVIEWsales_summary2TABLESPACEusers3PARALLEL(DEGREE4)4BUILDIMMEDIATE5ENABLEQUERYREWRITE6AS7SELECTd_name,8SUM(s.quantity_sold),8SUM(s.amount_sold)9FROMsaless,productsp10WHEREd_id=d_id11GROUPBYd_name;Oracle高級技術之大數(shù)據(jù)處理70物化視圖創(chuàng)建例SQL>CREATEMATERIALIZE54物化視圖創(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_SUMMARYOracle高級技術之大數(shù)據(jù)處理70物化視圖創(chuàng)建和查詢重寫例SQL>SELECTd55報表優(yōu)化的基本思路

以報表為單位，分析現(xiàn)有報表的SQL語句。總結統(tǒng)計運算SQL的共同規(guī)律，作為設計物化視圖的考慮對象。

定義相應的物化視圖。包括SQL查詢語句的編寫，刷新方式的確定等

評價原有統(tǒng)計運算SQL語句的執(zhí)行計劃，是否被Oracle查詢重寫到相應的物化視圖

評價如何在物化視圖上創(chuàng)建索引

評估所有物化視圖數(shù)據(jù)和索引的空間消耗情況，從而確定物化視圖數(shù)據(jù)和索引表空間的容量

Oracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化的基本思路以報表為單位，分析現(xiàn)有報表的SQL語句。56報表優(yōu)化示例

現(xiàn)有統(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_NAMEOracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化示例現(xiàn)有統(tǒng)計運算語句Oracle高級技術之大數(shù)57報表優(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_DATEOracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化示例物化視圖定義Oracle高級技術之大數(shù)據(jù)58報表優(yōu)化示例

TABLESPACETS_TAB_DYN：物化視圖MV_01存放的表空間。建議為物化視圖單獨設立表空間PARALLEL(DEGREE2)：并行度設計為2（與目前的CPU個數(shù)相等）BUILDIMMEDIATE：創(chuàng)建MV_01的同時，產生匯總數(shù)據(jù)REFRESHCOMPLETEONDEMAND：完全刷新ENABLEQUERYREWRITE：啟動查詢語句重寫功能Oracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化示例TABLESPACETS_TAB_DYN59報表優(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)計運算字段sum(ts_dyna.VIP_ACCT_ALL.T1),sum(ts_dyna.VIP_ACCT_ALL.T29),Oracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化示例SELECT短語應包含的字段Oracle高60報表優(yōu)化示例

創(chuàng)建物化視圖上的索引createindexidx_MV_01_01onMV_01(VIP_ORGAN3)tablespaceTS_IND_DYNA;物化視圖上的刷新定期執(zhí)行如下命令，可自動進行物化視圖的刷新execdbms_mview.refresh('TS_DYNA.MV_01');

Oracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化示例創(chuàng)建物化視圖上的索引Oracle高級技術61報表優(yōu)化示例

初始化參數(shù)的設置query_rewrite_enabled：TRUEquery_rewrite_integrity：STALE_TOLERATED權限方面的設置grantCREATEANYMATERIALIZEDVIEWtoTS_DYNA;grantGLOBALQUERYREWRITEtoTS_DYNA;

Oracle高級技術之大數(shù)據(jù)處理70報表優(yōu)化示例初始化參數(shù)的設置Oracle高級技術之大62并行處理經驗介紹Oracle高級技術之大數(shù)據(jù)處理70并行處理經驗介紹Oracle高級技術之大數(shù)據(jù)處理7063并行處理概述

Oracle并行處理技術無處不在！只要是大批量數(shù)據(jù)處理，就要考慮并行處理并行處理的先決條件大批量數(shù)據(jù)處理多CPU（SMP），CPU利用率不高足夠的內存I/O負載不高并發(fā)用戶少Oracle高級技術之大數(shù)據(jù)處理70并行處理概述Oracle并行處理技術無處不在！Orac64并行SQL語句舉例

insert

intoSUM_ACCT_DCC_SAACN_P

SELECT

'20050930',

a.sa_acct_no,

nvl(b.sa_curr_cod,'01'),

nvl(b.sa_curr_iden,'0'),

….…

FROMsor_acct_dcc_saacn_p_20050930aleft

joinsor_acct_dcc_saamt_p_20050930bona.sa_acct_no=b.sa_acct_noanda.etl_load_date=b.etl_load_dateleft

joinsor_acct_dcc_crcrdcrdcona.sa_card_no=c.cr_crd_no

wherea.etl_load_date='20050930'

1小時30分鐘

A:10,657,379B:10,548,397C:9,142,893Oracle高級技術之大數(shù)據(jù)處理70并行SQL語句舉例insert

intoSUM_A65并行SQL語句舉例

altersessionenableparalleldml;insert

/*+appendparallel(d,12)*/

intoSUM_ACCT_DCC_SAACN_Pdnologging

SELECT

/*+parallel(a,12)parallel(b,12)parallel(c,12)*/

'20050930',

a.sa_acct_no,

nvl(b.sa_curr_cod,'01'),

nvl(b.sa_curr_iden,'0'),

…

…

FROMsor_acct_dcc_saacn_p_20050930aleft

joinsor_acct_dcc_saamt_p_20050930bona.sa_acct_no=b.sa_acct_noanda.etl_load_date=b.etl_load_dateleft

joinsor_acct_dcc_crcrdcrdcona.sa_card_no=c.cr_crd_no

wherea.etl_load_date='20050930'16分鐘!

Oracle高級技術之大數(shù)據(jù)處理70并行SQL語句舉例altersessionenab66并行SQL語句舉例

directpathinsert、nologging并行處理技術

Oracle高級技術之大數(shù)據(jù)處理70并行SQL語句舉例directpathinsert67數(shù)據(jù)加載中的并行處理

在控制文件（.ctl）中進行參數(shù)設置directpathloadDIRECT=TRUE并行處理技術PARALLEL=TRUEOracle高級技術之大數(shù)據(jù)處理70數(shù)據(jù)加載中的并行處理在控制文件（.ctl）中進行參數(shù)設置68統(tǒng)計信息采集效率的提高

dbms_stats.gather_table_stats(ownname=>’OSS_SA_91’,tabname=>’TF_B_WORKFLOW’,estimate_percent=>50,degree=>4,cascade=>TRUE);/*單表統(tǒng)計信息采集*/序號采樣率并行度統(tǒng)計的記錄數(shù)統(tǒng)計時間11001283229500:04:04.9721008283229500:02:17.35310016283229500:02:28.494508284230600:00:47.575108282253800:00:27.31618286030000:00:24.68Oracle高級技術之大數(shù)據(jù)處理70統(tǒng)計信息采集效率的提高dbms_stats.gather69演講完畢，謝謝聽講!再見，seeyouagain2022/12/26Oracle高級技術之大數(shù)據(jù)處理70演講完畢，謝謝聽講!再見，seeyouagain202270Oracle高級技術之大數(shù)據(jù)處理702022/12/26Oracle高級技術之大數(shù)據(jù)處理70Oracle高級技術之大數(shù)據(jù)處理702022/12/26Or71交流內容分區(qū)技術報表優(yōu)化技術

并行處理應用經驗Oracle高級技術之大數(shù)據(jù)處理70交流內容分區(qū)技術Oracle高級技術之大數(shù)據(jù)處理7072Oracle的分區(qū)技術Oracle高級技術之大數(shù)據(jù)處理70Oracle的分區(qū)技術Oracle高級技術之大數(shù)據(jù)處理7073

分區(qū)技術內容什么是分區(qū)?分區(qū)的好處?如何實施分區(qū)?如何評估分區(qū)的效果?Oracle高級技術之大數(shù)據(jù)處理70分區(qū)技術內容什么是分區(qū)?Oracle高級技術之大數(shù)據(jù)處理74

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

高性能

–>數(shù)據(jù)維護能力->實施難度

–>高可用性（故障屏蔽能力）Oracle高級技術之大數(shù)據(jù)處理70分區(qū)技術的效益和目標性能Oracle高級技術之大數(shù)據(jù)處理7077分區(qū)方法分區(qū)方法:范圍---8Hash---8i列表---9i組合---8iRange

partitioningHash

partitioningComposite

partitioningList

partitioningOracle高級技術之大數(shù)據(jù)處理70分區(qū)方法分區(qū)方法:Range

partitioningHas78123CREATETABLEsales(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ū)例Oracle高級技術之大數(shù)據(jù)處理70123CREATETABLEsales分區(qū)字段：wee79最早、最經典的分區(qū)算法Range分區(qū)通過對分區(qū)字段值的范圍進行分區(qū)Range分區(qū)特別適合于按時間周期進行數(shù)據(jù)的存儲。日、周、月、年等。數(shù)據(jù)管理能力強數(shù)據(jù)遷移數(shù)據(jù)備份數(shù)據(jù)交換范圍分區(qū)的數(shù)據(jù)可能不均勻范圍分區(qū)與記錄值相關，實施難度和可維護性相對較差范圍分區(qū)特點Oracle高級技術之大數(shù)據(jù)處理70最早、最經典的分區(qū)算法范圍分區(qū)特點Oracle高級技術之大數(shù)80Hash分區(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)Oracle高級技術之大數(shù)據(jù)處理70Hash分區(qū)例createtableCUSTOMERS81Hash分區(qū)特點基于分區(qū)字段的HASH值，自動將記錄插入到指定分區(qū)。分區(qū)數(shù)一般是2的冪易于實施總體性能最佳適合于靜態(tài)數(shù)據(jù)HASH分區(qū)適合于數(shù)據(jù)的均勻存儲HASH分區(qū)特別適合于PDML和partition-wisejoins。支持(hash)localindexes9i不支持(hash)globalindexes10g支持(hash)globalindexesHASH分區(qū)數(shù)據(jù)管理能力弱HASH分區(qū)對數(shù)據(jù)值無法控制Oracle高級技術之大數(shù)據(jù)處理70Hash分區(qū)特點基于分區(qū)字段的HASH值，自動將記錄插入到指82列表分區(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