ETL性能問(wèn)題分析與調(diào)整方法

BOCN數(shù)據(jù)倉(cāng)庫(kù)

－ETL性能分析與調(diào)整方法1.前言

隨著數(shù)據(jù)倉(cāng)庫(kù)基礎(chǔ)數(shù)據(jù)和應(yīng)用的不斷增加，數(shù)據(jù)倉(cāng)庫(kù)的性能問(wèn)題日益突出。目前超過(guò)1小時(shí)的作業(yè)有2個(gè)30分鐘到1小時(shí)的作業(yè)有10個(gè)10分鐘到30分鐘的作業(yè)有48個(gè)因此對(duì)數(shù)據(jù)倉(cāng)庫(kù)進(jìn)行性能優(yōu)化的需求越來(lái)越迫切2.作業(yè)時(shí)長(zhǎng)分析共有12個(gè)作業(yè)運(yùn)行時(shí)間在30分鐘以上系統(tǒng)作業(yè)運(yùn)行時(shí)間SDDT05_EVENT_FUND_TXN_EXP1:47:03ALSALM_DEPOSIT_FIX_DATA1:05:00BRSBRS_EXP_DATA0:51:52MAST_AGMT_INFO_A0:47:01SDDSRDFTPONE0:46:08MAST_AGMT_INFO_B0:46:02PCRPCR_EXPORT0:43:20SDDSRDFTPTHREE0:43:07MASOTHER0:39:33SDDSRDFTPTWO0:38:43OCM99_OCRM_JOB_END0:37:20ALSALM_GROUP_INT_H0:33:193.共有48個(gè)作業(yè)運(yùn)行時(shí)間為10分鐘到30分鐘系統(tǒng)作業(yè)運(yùn)行時(shí)間系統(tǒng)作業(yè)運(yùn)行時(shí)間BAKBAK_ESSAPS_SRC0:29:14DQCT03_AGMT_AMT_H0:15:18T03T03_AGREEMENT_RT0:28:20CCRGRDJKFEXPDATA0:15:03PMMJXKH_C_LDM0:28:19DQCDQC_CHECK_KPI0:14:50BAKBAK_ETLENV_710:27:34PMMJXKH_P_LDM_NET0:14:08T03T03_CARD_ACCT_BAL_COMPO_H0:24:39ALSALM_LOAN_DATA0:13:57T03T03_AGREEMENT_S020:24:19MAST_MA_BALANCE0:13:53T03T03_AGMT_ACCU_H0:23:59CBST_CBS_BAL_DL0:13:18MAST_AGMT_INFO_C0:22:49BAKBAK_ETLENV_400:12:58T03T03_AGMT_BAL_H0:22:17MASFTPFILE0:12:32PMMJXKH_P_LDM_SAV0:21:33ACMCUST_FIX_IND_POST0:12:31CFST_CFS_AC_TREE0:21:31T05T05_CRDCARD_TXN_DTL0:12:05SDDBCNTDEPO0:20:57ECLNB_CUST_TXN_SUMM0:12:03DQCT03_CARD_ACCT_BAL_COMPO_H0:20:32ECLNB_NEW_CUST_SUMM0:11:58MDLM03_FIN_ACCT0:19:37BDET03_ACCT_CRDCARD0:11:53BDET03_AGREEMENT0:19:26IFST_IFS_BAL_BASIC0:11:20IRPT_DEP_POT_INT_RATE0:17:24MDLM01_CUST_SIGN_INFO0:11:16PCRCC_CR_ID_I0:17:08IRPT_LOAN_POT_INT_RATE0:11:07SDDREPAYDET0:17:06RIRDEPOS_BAL_DATA0:11:07T03T03_AGMT_BAL_H_S010:17:01T05T05_EVT_MESSAGE_SEND0:10:47ACMCUST_BASIC_INFO_PRE0:16:51CRST_REP_GJYW_ZB0:10:46CABCAB_EXP_DATA0:16:40MDLM03_CARD_TXN_STAT0:10:44PMMJXKH_P_LDM_CARD0:16:37IRPT_IRP_FLUIDITY_BASIC0:10:37MDLM03_FIN_ACCT_STAT0:16:20PCRCC_CR_BASE_I0:10:28PFXT05_VCH_FINANCE_TMP0:15:31ACMTHIRD_PTY_MON_SUMM0:10:204.月末超長(zhǎng)作業(yè)列表月末跑批：

系統(tǒng)名作業(yè)名跑批時(shí)間(分鐘/min)IRPT_IRP_SAME_BIZ_AVGBALSQL語(yǔ)句無(wú)法跑批IRPT_IRP_DEPOSIT_ACTIVITYSQL語(yǔ)句無(wú)法跑批IFST_IFS_AC_DLSQL語(yǔ)句無(wú)法跑批OCMOCRM_HIGH_CUST_INFOSQL語(yǔ)句無(wú)法跑批5.ETL腳本性能問(wèn)題與原因分析業(yè)務(wù)規(guī)則理解數(shù)據(jù)訪問(wèn)路徑選取的合理性SQL策略與算法Multi-Statement實(shí)現(xiàn)并行Insert…Select…比對(duì)算法不標(biāo)準(zhǔn)加載策略不優(yōu)-大數(shù)據(jù)量Update、向非空表進(jìn)行Insert

一個(gè)任務(wù)內(nèi)多腳本，過(guò)多的數(shù)據(jù)庫(kù)錄入/錄出ACRM中居多模型優(yōu)化PPI和PI的選擇對(duì)小表或代碼表的部分字段進(jìn)行CollectStatistics,優(yōu)化sql的執(zhí)行路徑實(shí)際并發(fā)運(yùn)行環(huán)境的影響6.DuplicateRowsAduplicaterowisarowofatablewhosecolumnvaluesareallidenticaltoanotherrowinthesametable.col_a col_b col_c20 50 A25 50 A25 50 ADuplicateRowsBecauseaPKuniquelyidentifieseachrow,ideallyarelationaltableshouldnothaveduplicaterows!TheANSIstandard,however,permitsduplicaterowsforspecializedsituations,thusTeradatapermitsthemaswell.Youmayselectwhetheryourtablewillorwillnotallowthem.*Note:IfaUPIisselectedonaSETtable,theduplicaterowcheckisreplacedbyacheckforduplicateindexvalues.CREATESETTABLEtable_A : :CREATEMULTISETTABLEtable_B : :Checksfor*anddisallowsduplicaterows.Doesn’tcheckforandallowsduplicaterows.TheTeradatadefaultTheANSIdefault7.RowDistributionUsingaUPI–Case1Notes:Often,butnotalways,thePKcolumn(s)willbeusedasaUPI.PIvaluesforOrder_Numberareknowntobeunique(it’saPK).TeradatawilldistributedifferentindexvaluesevenlyacrossallAMPs.ResultingrowdistributionamongAMPsisveryuniform.Assuresmaximumefficiencyforparalleloperations.AMPAMPAMPAMPo_# c_# o_dt o_st7202 2 4/09 C7415 1 4/13 Co_# c_# o_dt o_st7325 2 4/13 O7103 1 4/10 O7402 3 4/16 Co_# c_# o_dt o_st7188 1 4/13 C7225 2 4/15 Co_# c_# o_dt o_st7324 3 4/13 O7384 1 4/12 COrder8.RowDistributionUsingaNUPI–Case2Notes:Customer_NumbermaybethepreferredaccesscolumnforORDERtable,thusagoodindexcandidate.ValuesforCustomer_Numberaresomewhatnon-unique.ChoiceofCustomer_NumberisthereforeaNUPI.RowswiththesamePIvaluedistributetothesameAMP.Rowdistributionislessuniformorskewed.o_# c_# o_dt o_st7325 2 4/13 O7202 2 4/09 C7225 2 4/15 Co_# c_# o_dt o_st7384 1 4/12 C7103 1 4/10 O7415 1 4/13 C7188 1 4/13 Co_# c_# o_dt o_st7402 3 4/16 C7324 3 4/13 OAMPAMPAMPAMPOrder9.RowDistributionUsingaHighlyNon-UniquePrimaryIndex(NUPI)–Case3OrderNotes:ValuesforOrder_Statusare“highly”non-unique.ChoiceofOrder_StatuscolumnisaNUPI.Onlytwovaluesexist,soonlytwoAMPswilleverbeusedforthistable.Tablewillnotperformwellinparalleloperations.Highlynon-uniquecolumnsarepoorPIchoicesgenerally.Thedegreeofuniquenessiscriticaltoefficiency.AMPAMPAMPAMPo_# c_# o_dt o_st7402 3 4/16 C7202 2 4/09 C7225 2 4/15 C7415 1 4/13 C7188 1 4/13 C7384 1 4/12 Co_# c_# o_dt o_st7103 1 4/10 O7324 3 4/13 O7325 2 4/13 O10.JoinRedistributionSELECT ...FROM Table1T1INNERJOIN Table2T2ON T1.A=T2.A;Joincolumnsarefromthesamedomain. NoRedistributionneeded.T1

A B C PI

100 214 433T2

A B C PI

100 725 002SELECT ...FROM Table3T3INNERJOIN Table4T4ON T3.A=T4.B;Joincolumnsarefromthesamedomain. Redistributionneeded.T3

A B C PI

255 345 225T4 A B C PI 867 255 566SPOOL A B C PI 867 255 566RedistributeT4rowsinspooloncolumnB.11.JoinRedistribution(cont.)Joinisoncolumnsthatisn'tthePrimaryIndexofeithertable.SELECT ...FROM Table5T5INNERJOIN Table6T6ON T5.B=T6.C;Joincolumnsarefromthesamedomain. Redistributionneeded.T5 A B C PI 456 777 876T6 A B C PI 993 228 777RedistributeT5rowsinspooloncolumnB.RedistributeT6rowsinspooloncolumnC.SPOOL A B C PI 456 777 876SPOOL A B C PI 993 228 777IfthecolumnsbeingjoinedtogetherarenotPrimaryIndexcolumns(fromsamedomain),optionstheOptimizermaychoosefrominclude:Redistributebothtablesinspool(asshownabove)DuplicatethesmallertableinspoolacrossallAMPs12.DuplicatingaTableinSpool1Mrows1Mrows1Mrows1Mrows1Mrows1Mrows1Mrows1MrowsTableFormergejoins,theoptimizermaychoosetoduplicateasmalltableoneachAMP.Forproductjoins,theoptimizeralways

duplicatesonetableacrossallAMPs.Ineithercase,eachAMPmusthaveenoughspoolspaceforacompletecopy.Table1Mrows8MrowsSPOOL(TableisduplicatedoneachAMP)1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows13.MergeJoinRowsmustbeonthesameAMPtobejoined.MergeJoinreadsblocksfrombothtablesonlyonce.Usuallychosenforanequalityjoincondition.Generallymoreefficientthanaproductjoin.Mergejoinprocess:IdentifytheSmallerTable.Ifnecessary:Putqualifyingdataofoneorbothtablesintospool(s).MovethespoolrowstoAMPsbasedonthejoincolumnhash.Sortthespoolrowsintojoincolumnhashsequence.Comparetherowswithmatchingjoincolumnrowhashvalues.Causessignificantlyfewercomparisonsthanaproductjoin.A3 DataA3 DataA3 DataB7 DataB7 DataC4 DataC4 DataJoinColumnHashA3 DataB8 DataC4 DataJoinColumnHash14.ProductJoinRowsmustbeonthesameAMPtobejoined.DataDataDataDataDataDataDataDataDataDataDoesnotsorttherows.Mayre-readblocksfromonetableifAMPmemorysizeisexceeded.ItcompareseveryqualifyingTable1rowtoeveryqualifyingTable2row.ThosethatmatchtheWHEREconditionaresavedinspool.ItiscalledaProductJoinbecause:TotalCompares=#QualifiedRowsTable1*#QualifiedRowsTable2TheinternalcomparesbecomeverycostlywhentherearemorerowsthanAMPmemorycanholdatonetime.Theyaregenerallyunintentionalandoftengivemeaninglessoutput.ProductJoinprocess:IdentifytheSmallerTableandduplicateitinspoolonallAMPs.JoineachspoolrowforSmallerTabletoeveryrowforLargerTable.15.腳本SQL案例分析1–業(yè)務(wù)規(guī)則理解以CAS的CLAT任務(wù)腳本為例其中，統(tǒng)計(jì)每個(gè)帳戶的最近的交易日期，通過(guò)金融傳票的明細(xì)進(jìn)行匯總groupby取得(效率較低);

SELECTAcct_Num,max(Tx_Dt)FROMCAS.V_FINANCE_EVENTWHEREsubstring(acct_numfrom19for3)IN('209','229')GROUPBY1;

實(shí)際可以從T_P_LOAN_CONTRACT中l(wèi)ast_tx_dt字段得到

腳本調(diào)整后,運(yùn)行時(shí)間16mins53secsVS.2mins*腳本開發(fā)人員應(yīng)提高對(duì)數(shù)據(jù)模型的理解，加強(qiáng)與設(shè)計(jì)人員的溝通16.腳本SQL案例分析2–并行Insert修改前：

insertintoAselect*fromB;

insertintoAselect*fromC;修改后：insertintoAselect*fromB;insertintoAselect*fromc;*并行插入空表不記錄TransactionJournal*充分利用Teradata向空表Insert較快以及并行操作的特性17.腳本SQL案例分析3–Delete/Insert以T03_AGMT_RELA_H(20726301)任務(wù)腳本為例在腳本中delete表中大部分記錄，然后進(jìn)行插入

DELETEFROMPDATA.T03_AGMT_RELA_H/*修改刪除條件,將刪除條件改為主鍵*/WHERE(…)IN(SELECT…FROMPWORK.VT_T03_AGMT_RELA_H_curWHEREAgmt_Rela_Type_CdNOTIN('01','02','03','04','05','06','07','08','09','17','61','62','63','64','65','66','67','68'));INSERTINTOPDATA.T03_AGMT_RELA_H***Deletecompleted.11169050rowsremoved.***Totalelapsedtimewas59minutesand45seconds.***Insertcompleted.11185846rowsadded.18.腳本SQL案例分析3–Delete/Insert調(diào)整SQL策略先將需要保留的數(shù)據(jù)插入臨時(shí)表清空目標(biāo)表通過(guò)Multi-Statement對(duì)目標(biāo)表進(jìn)行并行insert…select操作*利用Teradata空表insert操作與并行的特性*避免大數(shù)據(jù)量的delete,update操作19.腳本SQL案例分析4–避免大表字段轉(zhuǎn)換在WHERE條件的運(yùn)算操作中，會(huì)出現(xiàn)對(duì)大表的字段進(jìn)行類型轉(zhuǎn)換或者其他處理，然后與小表字段或常量進(jìn)行等值或比較操作

以SGR的T_P_PRE_LOAN_RET_CARD為例WHEREpre_loan_card_acct=b.tmp_Acct_NumANDCAST(SUBSTR(statis_dt,1,4)||SUBSTR(statis_dt,6,2)||SUBSTR(statis_dt,9,2)ASchar(8))='20051025';-----------------------------------------------------------------------------------------WHEREpre_loan_card_acct=b.tmp_Acct_NumAND

statis_dt=CAST(‘2005-10-25’ASDATEformat‘YYYY-MM-DD’)或WHEREpre_loan_card_acct=b.tmp_Acct_NumANDstatis_dt=‘2005-10-25’20.腳本SQL案例分析5–分析SQL的執(zhí)行路徑通過(guò)執(zhí)行explainselect….fromaleftjoinbona…

觀察Teradata優(yōu)化器的所生成的執(zhí)行路徑是否合理SQL執(zhí)行路徑中應(yīng)避免大表的在所有AMP重新分布小表應(yīng)盡量在AMP上重新分布或者在所有AMP上復(fù)制通過(guò)以下技術(shù)手段可以引導(dǎo)優(yōu)化器生成正確而合理的執(zhí)行路徑SQL本身寫法的檢查，Join條件是否正確，避免大表間的笛卡爾乘積相關(guān)表物理模型設(shè)計(jì)是否合理,PI的選擇等對(duì)小表的索引字段以及在表關(guān)聯(lián)join時(shí)多次出現(xiàn)的條件字段收集統(tǒng)計(jì)信息CollectStatistics以T03的T03_CARD_ACCT_BAL_COMPO_H為例21.腳本SQL案例分析5–分析SQL的執(zhí)行路徑Cont.多表joinFROMSDATA_FULL.S01_TGEN042T1LEFTJOINPDATA.T99_STD_CDE_MAP_INFOT2ONT2.SrcTab_Cd='S01_HARDCODE'/*源表名*/ANDT2.Cde_Type='CURRENT_CAL_INT'/*代碼類型*/ANDT1.CTD_INT_BLG_CDE=T2.SrcCde_Cd/*主表與映射表關(guān)聯(lián)條件*/

LEFTJOINPDATA.T99_STD_CDE_MAP_INFOT3ONT3.SrcTab_Cd='S01_TGEN015'/*源表名*/ANDT3.Cde_Type='CURRENCY'/*代碼類型*/ANDT1.CRY_CDE=T3.SrcCde_Cd/*主表與映射表關(guān)聯(lián)條件*/

LEFTJOINPDATA.T99_STD_CDE_MAP_INFOT4ONT4.SrcTab_Cd='S01_TGEN066'/*源表名*/ANDT4.Cde_Type='CRDCARD_BAL_COMPONENT'/*代碼類型*/ANDT1.BCT_IDN_CDE=T4.SrcCde_Cd/*主表與映射表關(guān)聯(lián)條件*/;

通過(guò)執(zhí)行Explain可以觀察該SQL的執(zhí)行路徑,如下22.腳本SQL案例分析5–分析SQL的執(zhí)行路徑Cont.3)Wedoasingle-AMPRETRIEVEstepfromPDATA.T4bywayoftheprimaryindex"PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT'"witharesidualconditionof("(PDATA.T4.SrcTab_Cd='S01_TGEN066')AND(PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT')")intoSpool2(all_amps),whichisduplicatedonallAMPs.ThesizeofSpool2isestimatedwithhighconfidencetobe684rows.Theestimatedtimeforthisstepis0.00seconds.4)Weexecutethefollowingstepsinparallel.1)Wedoanall-AMPsJOINstepfromSDATA_FULL.T1bywayofanall-rowsscanwithnoresidualconditions,whichisjoinedtoSpool2(LastUse)bywayofanall-rowsscan.SDATA_FULL.T1andSpool2areleftouterjoinedusingaproductjoin,withajoinconditionof("SDATA_FULL.T1.BCT_IDN_CDE=SrcCde_Cd").TheinputtableSDATA_FULL.T1willnotbecachedinmemory.TheresultgoesintoSpool3(all_amps),whichisredistributedbyhashcodetoallAMPs.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool3isestimatedwithnoconfidencetobe54,801,029rows.Theestimatedtimeforthisstepis30.76seconds.2)Wedoasingle-AMPRETRIEVEstepfromPDATA.T3bywayoftheprimaryindex"PDATA.T3.Cde_Type='CURRENCY'"witharesidualconditionof("(PDATA.T3.SrcTab_Cd='S01_TGEN015')AND(PDATA.T3.Cde_Type='CURRENCY')")intoSpool4(all_amps),whichisredistributedbyhashcodetoallAMPs.ThesizeofSpool4isestimatedwithhighconfidencetobe146rows.Theestimatedtimeforthisstepis0.00seconds.23.腳本SQL案例分析5–分析SQL的執(zhí)行路徑Cont.

No.DBNameTableNamePerm(MB)SkewIndex1SDATA_FULLS01_TGEN04212,422.32.1PRIMARYINDEX(ACT_IDN_SKY)2PDATAT99_STD_CDE_MAP_INFO0.593.5PRIMARYINDEXXIE1T99_STD_CDE_MAP_INFO(Cde_Type)COLLECT

STATS

USING

SAMPLE

ONSDATA_FULL.S01_TGEN042COLUMN(BCT_IDN_CDE);COLLECT

STATS

USING

SAMPLE

ONSDATA_FULL.S01_TGEN042COLUMN(CRY_CDE);腳本SQL案例分析5–分析SQL的執(zhí)行路徑Cont.3)Weexecutethefollowingstepsinparallel.1)Wedoasingle-AMPRETRIEVEstepfromPDATA.T4bywayoftheprimaryindex"PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT'"witharesidualconditionof("(PDATA.T4.SrcTab_Cd='S01_TGEN066')AND(PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT')")intoSpool2(all_amps),whichisduplicatedonallAMPs.ThesizeofSpool2isestimatedwithhighconfidencetobe684rows.Theestimatedtimeforthisstepis0.00seconds.2)Wedoasingle-AMPRETRIEVEstepfromPDATA.T3bywayoftheprimaryindex"PDATA.T3.Cde_Type='CURRENCY'"witharesidualconditionof("(PDATA.T3.SrcTab_Cd='S01_TGEN015')AND(PDATA.T3.Cde_Type='CURRENCY')")intoSpool3(all_amps),whichisduplicatedonallAMPs.ThenwedoaSORTtoorderSpool3byrowhash.ThesizeofSpool3isestimatedwithhighconfidencetobe16,644rows.Theestimatedtimeforthisstepis0.04seconds.

25.腳本SQL案例分析5–分析SQL的執(zhí)行路徑Cont.4)Wedoanall-AMPsJOINstepfromSDATA_FULL.T1bywayofanall-rowsscanwithnoresidualconditions,whichisjoinedtoSpool2(LastUse)bywayofanall-rowsscan.SDATA_FULL.T1andSpool2areleftouterjoinedusingaproductjoin,withajoinconditionof("SDATA_FULL.T1.BCT_IDN_CDE=SrcCde_Cd").TheinputtableSDATA_FULL.T1willnotbecachedinmemory.TheresultgoesintoSpool4(all_amps),whichisbuiltlocallyontheAMPs.ThenwedoaSORTtoorderSpool4byrowhash.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool4isestimatedwithlowconfidencetobe54,722,052rows.Theestimatedtimeforthisstepis45.95seconds.26.腳本SQL案例分析5–分析SQL的執(zhí)行路徑Cont.優(yōu)化后，性能顯著提高27.腳本SQL案例分析6–物理表的設(shè)計(jì)DELETEFROMPDATA.T03_AGMT_PTY_RELA_H/*修改刪除條件,將刪除條件改為主鍵*/WHERE(Agmt_Id/*協(xié)議號(hào)*/,Agmt_Mdfr/*協(xié)議修飾符*/,Agmt_Pty_Rela_Type_Cd/*協(xié)議當(dāng)事人關(guān)系類型代碼*/)IN(SELECTAgmt_Id/*協(xié)議號(hào)*/,Agmt_Mdfr/*協(xié)議修飾符*/,Agmt_Pty_Rela_Type_Cd/*協(xié)議當(dāng)事人關(guān)系類型代碼*/FROMPWORK.VT_T03_AGMT_PTY_RELA_H_cur2)Insert…***Deletecompleted.29728007rowsremoved.***Totalelapsedtimewas10minutesand40seconds.***Insertcompleted.29814092rowsadded.28.腳本SQL案例分析6–物理表的設(shè)計(jì)CREATE

MULTISET

TABLET03_AGMT_PTY_RELA_H,NO

FALLBACK,

NO

BEFORE

JOURNAL,

NO

AFTER

JOURNAL,

CHECKSUM=DEFAULT(Party_IdCHAR(19)CHARACTERSETLATINCASESPECIFICTITLE'當(dāng)事人編號(hào)'NOT

NULL,Agmt_IdCHAR(21)CHARACTERSETLATINCASESPECIFICTITLE'協(xié)議號(hào)'NOT

NULL,Agmt_MdfrVARCHAR(18)CHARACTERSETLATINCASESPECIFICTITLE'協(xié)議修飾符'NOT

NULL,Agmt_Pty_Rela_Type_CdCHAR(2)CHARACTERSETLATINCASESPECIFICTITLE'協(xié)議當(dāng)事人關(guān)系類型代碼'NOT

NULL,St_DtDATEFORMAT'YYYYMMDD'TITLE'開始日期'NOT

NULL

DEFAULTDATE'1900-01-01',End_DtDATEFORMAT'YYYYMMDD'TITLE'結(jié)束日期'NOT

NULL

DEFAULTDATE'3000-12-31')PRIMARY

INDEXXIE1T03_AGMT_PTY_RELA_H(Agmt_Id)PARTITIONBYRANGE_N(CAST((Agmt_Pty_Rela_Type_Cd)ASINTEGER)BETWEEN1AND100EACH1,NORANGEORUNKNOWN);COLLECTSTATSONT03_AGMT_PTY_RELA_HCOLUMNPARTITION；

29.腳本SQL案例分析6–物理表的設(shè)計(jì)DELETEFROMPDATA.T03_AGMT_PTY_RELA_H/*修改刪除條件,將刪除條件改為主鍵*/WHERE(Agmt_Id/*協(xié)議號(hào)*/,Agmt_Mdfr/*協(xié)議修飾符*/,Agmt_Pty_Rela_Type_Cd/*協(xié)議當(dāng)事人關(guān)系類型代碼*/)IN(SELECTAgmt_Id/*協(xié)議號(hào)*/,Agmt_Mdfr/*協(xié)議修飾符*/,Agmt_Pty_Rela_Type_Cd/*協(xié)議當(dāng)事人關(guān)系類型代碼*/FROMPWORK.VT_T03_AGMT_PTY_RELA_H_cur2)ANDAgmt_Pty_Rela_Type_CdIN('14','15','81','18','19','84','85','16')–取特定的協(xié)議類型

Insert…30.腳本SQL案例分析6–物理表的設(shè)計(jì)

3)Wedoanall-AMPsDELETEfrom8partitionsoftest.T03_AGMT_PTY_RELA_Hwithaconditionof("(test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='14')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='15')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='81')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='18')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='19')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='84')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='85')OR(test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='16')))))))").31.腳本SQL案例分析6–減