TeraData基础培训.ppt

TeraData基础培训,Teradata学习推荐资料：TFv800ReadBook1.pdfTFv800ReadBook2.pdfTeradataSQL.pdfETLAutomationManualV2.5.2(GB).docperl编程24学时教程。,TeradataClient安装TeradataSQLAssistant.IFERRORCODE0THEN.QUIT12;-获取历史表中除了s_date,e_date以外，其它字段SELECTColumnNameCOLSFROMDBC.ColumnsxWHEREDataBaseName=$TARGETDBANDTableName=$HTANDcolumnNameNOTIN($HT_s_date,$HT_e_date)ORDERBYColumnID;.IFERRORCODE0THEN.QUIT12;-创建当日表PI与历史表同CREATEVOLATILEMULTISETTABLE$NDAS(SELECT$COLSFROM$TARGETDB.$HT)WITHNODATAPRIMARYINDEX($PICOLS)ONCOMMITPRESERVEROWS;.IFERRORCODE0THEN.QUIT12;,DSQL功能介绍,Dsql的启动方法dsqlc-fv1=txt1v2=nn1第一参数为数据库登陆信息文件,其内容包括：TD_SYS=dbcTD_USER=billTD_PASS=bill#口令自动加密。#长度小于16时，认为是明文，加密后长度为16Dsql脚本中包含两类命令控制命令,与Bteq一样以“.”开始SQLRequest命令，非以“.”开始，以“;”和换行符作为结束。,DSQL功能介绍（工作变量）,DSQL工作变量DSQL的工作变量为“字符串”类型，可以出现在脚本的任意位置。使用方法和perl/shell的变量相似；$var1或$var1DSQL的工作变量，除了从初始参数文件中获取外，它增加了select结果转存到工作变量中的功能。这就使得sql脚本真正地“动态”了。变量的赋值方法：1)从初始参数文件中获得2)从select的结果中获得，变量名称就是“字段名称”，变量的值为“字段内容”；当select的结果为多条记录时，变量的值为该字段所有记录内容以“,”连接起来的大字符串。3)启动dsql的附加参数，可以对变量赋值4)通过控制命令.runfile=V5=abcV6=22,DSQL功能介绍（流程控制）,DSQL控制命令DSQL的控制命令与bteq的控制命令表现形式很相似；由于ETL脚本很少使用Bteq做数据的Import和export,所以DSQL舍弃了数据Import和export功能。并且也舍弃了.logon命令，仅使用初始参数文件的内容进行Teradata的连接，脚本中所有的SQL语句都是通过这个连接提交到TeradataDSQL控制命令1).IF条件判断命令2).Goto/.Label命令2).RunFile子脚本调用3).Return子脚本返回4).QuitDSQL结束命令5).OS操作系统功能调用6).DefineSQL-Block定义命令7).Sleep程序挂起n秒钟,DSQL将会给ETL带来?,ETL脚本“单一”的执行环境脚本真正地“动态”脚本更简洁和“易读”可以取消一条记录一个字段的StageTable可以方便地把复杂SQL语句中的“单值”DerivedTable隔离出去Insertintotarget1(f1,f2)selectf1,f2fromsrc1,(selectmax(f2)maxf2fromsrc2)MWheref2=maxf2-Selectmax(f2)maxf2fromsrc2;Insertintotarget1(f1,f2)Selectf1,f2Wheref2=$maxf2,DSQL将会给ETL带来?,知识“共享”更方便把那些同种类型的“处理过程”归纳总结，“抽象”成相对固定的算法，便于其他项目的使用；.runfile=.runfile=$HOME/bin/insert_his.dsqlHT=tab1把那些对某种类型字段做同样的“操作”归纳总结，“抽象”成相对固定的宏定义(SQLBlock),在其他ETL脚本中可以方便的重复使用,或在当前脚本中多次引用，从而提高脚本开发的效率.Definechar8_to_date(1,)SelectEND;CREATEMUTLISETTABLETEMP_XXas.;INSERTINTOTEMP_XX.;DELETEFROMTB_XXXWHERESTATISTICS_DT=CAST(:report_dateASDATEFORMATYYYYMMDD);INSERTINTOTB_XXXSELECTSTATISTICS_DT,.,.FROMTEMP_XXGROUPBY1,2,.;SETreturnflag=1;END;,参数变量声明In输入Out输出INOUT输入输出,参数变量声明,变量赋值,调存储过程CALLDWMART_XXX.PROC_TEST(20100501,returnflag),Macro,CREATEMACROnew_dept(deptINTEGER,budgetDEC(10,2)DEFAULT0,nameCHAR(30),mgrINTEGER)AS(INSERTINTOdepartment(department_number,department_name,budget_amount,manager_employee_number)VALUES(:dept,:name,:budget,:mgr);SELECTdepartment_number(TITLEnumber),department_name(TITLEname),budget_amouunt(TITLEbudget),manager_employee_number(TITLEmanager)FROMdepartmentWHEREdepartment_number=:dept;);,运行宏new_deptEXECnew_dept(505,610000.00,MarketingResearch,1007);,View,REPLACEVIEWXXXX.VIE_SYS_XXXASLOCKINGTABLETB_XXX_AFORACCESSLOCKINGTABLEPDATA.TB_XXX_BFORACCESSLOCKINGTABLEPDATA.TB_XXX_CFORACCESSSELECTT1.COLUMN1,.,T2.COLUMN1,.,T3.COLUMN1,.FROMPDATA.TB_XXX_AT1INNERJOINPDATA.TB_XXX_BT2ONT1.COLUMNXX=T2.COLUMNXXANDT1.COLUMNXY=T2.COLUMNXYINNERJOINPDATA.TB_XXX_CT3ONT1.COLUMNX=T3.COLUMNXWHERET1.COLUMN1=XXXXX;,REPLACEVIEWXXXX.VIE_SYS_XXXASSELECTT1.COLUMN1,.,T2.COLUMN1,.,T3.COLUMN1,.FROMPDATA.TB_XXX_AT1INNERJOINPDATA.TB_XXX_BT2ONT1.COLUMNXX=T2.COLUMNXXANDT1.COLUMNXY=T2.COLUMNXYINNERJOINPDATA.TB_XXX_CT3ONT1.COLUMNX=T3.COLUMNXWHERET1.COLUMN1=XXXXX;,Table,Set,不允许记录重复CREATESETTABLEpmart.RPT_NM_GRP_PRE_WARN_MON,(CAL_MonthINTEGERTITLE统计月份,ORG_NUMCHAR(12)TITLE集团编号,City_IDCHAR(3)TITLE地市标识,ORG_SUBS_GRP_NUMCHAR(10)TITLE集团用户群编号,ORG_TitleVARCHAR(200)TITLE集团名称,ORG_LevelCHAR(2)TITLE集团级别,STAT_Item_CodeCHAR(2)TITLE统计项,STAT_ValueDECIMAL(18,2)TITLE统计值)PRIMARYINDEX(ORG_NUM);,MultiSet,允许记录重复CREATEMULTISETTABLEpmart.RPT_NM_GRP_PRE_WARN_MON,(CAL_MonthINTEGERTITLE统计月份,ORG_NUMCHAR(12)TITLE集团编号,City_IDCHAR(3)TITLE地市标识,ORG_SUBS_GRP_NUMCHAR(10)TITLE集团用户群编号,ORG_TitleVARCHAR(200)TITLE集团名称,ORG_LevelCHAR(2)TITLE集团级别,STAT_Item_CodeCHAR(2)TITLE统计项,STAT_ValueDECIMAL(18,2)TITLE统计值)PRIMARYINDEX(ORG_NUM);,假设原有1286449条记录插入:152853条记录耗时：15秒,假设原有1286449条记录插入:152853条记录耗时：1秒,UNIQUE,建议：Teradata中都用MultiSet,若真要限定唯一性，可以通过UPI或USI实现,Table,永久表临时表可变临时表在一个会话中，能够被多个查询使用。占用SPOOLSPACE，在cache中保留表的定义，spool缓冲区中物化，不使用数据字典每个会话可以建立64个，可以随时被手动删除，会话结束时自动删除。全局临时表每个会话最多可以物化32个全局临时表。空间占用TemporarySpace，对会话而言，每个实例是本地的。物化表中内容与其他会话不共享。会话结束后，物化的表被自动删除。(但基础定义仍然存储在数据字典中)导出表只有一个查询要求使用临时表，其他查询都不要求。空间占用SPOOLSPACE，使用SELECT语句在spool缓冲区中创建导出表。查询结果只使用一次。,永久表,CREATEMULTISETTABLEtctap.tap_c_kpi_assess_2,NOFALLBACK,NOBEFOREJOURNAL,NOAFTERJOURNAL,CHECKSUM=DEFAULT(KPI_IDCHAR(8)CHARACTERSETLATINCASESPECIFICTITLEKPI标识NOTNULL,FREQ_IDCHAR(2)CHARACTERSETLATINCASESPECIFICTITLE频度代码NOTNULL,DATA_DATEINTEGERTITLE指标日期NOTNULL,DIM_VALUE_ID1CHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度1NOTNULLDEFAULTZ,DIM_VALUE_ID2CHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度2NOTNULLDEFAULTZ,DIM_VALUE_ID3CHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度3NOTNULLDEFAULTZ,.,DIM_VALUE_ID18CHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度18NOTNULLDEFAULTZ,DIM_VALUE_ID19CHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度19NOTNULLDEFAULTZ,DIM_VALUE_ID20CHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度20NOTNULLDEFAULTZ,DIM_VALUE_IDnCHAR(8)CHARACTERSETLATINCASESPECIFICTITLE维度nNOTNULLDEFAULTZ,KPI_ASSESS_TYPE_IDCHAR(4)CHARACTERSETLATINCASESPECIFICTITLEKPI目标类型IDNOTNULL,KPI_ASSESS_RESULT_IDCHAR(2)CHARACTERSETLATINCASESPECIFICTITLEKPI目标值返回值IDNOTNULL,KPI_ASSESS_VALUEDECIMAL(18,2)TITLEKPI目标值)PRIMARYINDEX(DATA_DATE,KPI_ID,DIM_VALUE_ID1)PARTITIONBYRANGE_N(DATA_DATEBETWEEN20000101AND20191231)UNIQUEINDEX(KPI_ID,FREQ_ID,DATA_DATE,DIM_VALUE_ID1,DIM_VALUE_ID2,DIM_VALUE_ID3,.,DIM_VALUE_ID18,DIM_VALUE_ID19,DIM_VALUE_ID20,DIM_VALUE_IDn,KPI_ASSESS_TYPE_ID,KPI_ASSESS_RESULT_ID);COLLECTSTATISTICSONtctap.tap_c_kpi_assess_2COLUMN(DATA_DATE,KPI_ID,DIM_VALUE_ID1);COLLECTSTATISTICSONtctap.tap_c_kpi_assess_2COLUMN(DATA_DATE);COLLECTSTATISTICSONtctap.tap_c_kpi_assess_2COLUMN(KPI_ID,FREQ_ID,DATA_DATE,DIM_VALUE_ID1,DIM_VALUE_ID2,DIM_VALUE_ID3,.,DIM_VALUE_ID18,DIM_VALUE_ID19,DIM_VALUE_ID20,DIM_VALUE_IDn,KPI_ASSESS_TYPE_ID,KPI_ASSESS_RESULT_ID);COLLECTSTATISTICSONtctap.tap_c_kpi_assess_2COLUMN(KPI_ID);,设计原则：1、调整PI，在保留一定数据分布均匀度的基础上，把PI字段从N个减少为3个，提高数据查询速度。2、增加PPI，提高数据插入性能与查询性能。3、增加USI，而不是把Multiset改为Set；虽然这两种方式都能保证数据记录的唯一性，但是前者效率会更高。4、增加统计信息，有助于系统优化SQL查询。,可变临时表,建表语句：CREATEVOLATILEMULTISETTABLEvt_RETAIN_ANLY_MON(col1,col2）PRIMARYINDEX(PI_Cols);复制表结构：CREATEMULTISETVOLATILETABLEVT_APP_AGG_INDEX_HASDWMart_DOA.DOA_APP_AGG_INDEX_HWITHDATA(NODATA)ONCOMMITPRESERVEROWS;CREATEVOLATILEMULTISETTABLEVT_RETAIN_ANLY_MONAS(SELECTcol1,col2,FROMWHEREGROUPBY.）WITHDATAPRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;,ONCOMMITPRESERVEROWS;,全局临时表,建表语句CREATEGLOBALTEMPORARYTABLEgt_deptsal(deptnoSMALLINT,avgsalDEC(9,2),maxsalDEC(9,2),minsalDEC(9,2),sumsalDEC(9,2),empcntSMALLINT)ONCOMMITPRESERVEROWS;CREATEINDEX(empcnt)ONgt_deptsal;COLLECTSTATISTICSONgt_deptsalINDEX(deptno);COLLECTSTATISTICSONgt_deptsalCOLUMNavgsal;HELPSTATISTICSgt_deptsal;,TEMPORARY,导出表,示例：SELECT*FROM(SELECTdeptno,SUM(sal)ASsal_sumFROMscott.empGROUPBYdeptno)tmp,固化临时表,固化临时表，就是把查询结果存放到一张物理表。共下次分析或他人使用。Session断开之后，仍然可以使用。在性能优化中也常被用到的一种做法。示例1：CREATEMULTISETTABLEtttemp.TMP_BOSS_VOICAS（SELECT*FROMpview.vw_net_gsm_nl)WITHNODATAPRIMARYINDEX(subs_id);INSERTINTOtttemp.TMP_BOSS_VOICSELECT*FROMpview.vw_net_gsm_nlWHERE*;示例2：CREATEMULTISETTABLEtttemp.TMP_BOSS_VOICAS（SELECT*FROMpview.vw_net_gsm_nlWHERE*)WITHDATAPRIMARYINDEX(subs_id);示例3：(复制表，数据备份）CREATEMULTISETTABLEtttemp.TMP_BOSS_VOICASpdata.tb_net_gsm_nlWITHDATA;,修改表定义,常见的表定义修改操作：增加字段修改字段长度建议的操作流程Renametabledb.tablexasdb.tabley;通过Showtable语句获得原表db.tablex的定义定义新表：db.tablexInsertintodb.tablex(。)select。Fromdb.tabley;Droptabledb.tabley;Teradata提供ALTERTABLE语句，可进行修改表定义但，不建议采用ALTERTABLE方式。,Update/Delete操作,UPDATECustomerSETCredit_Limit=Credit_Limit*1.20;,CREATEmultisetTABLECustomer_NASCustomerwithnodata;INSERTINTOCustomer_NSELECTCredit_Limit*1.20FROMCustomer;DROPTABLECustomer;RENAMETABLECustomer_NTOCustomer;,CREATEmultisetTABLETrans_NasTranswithnodata;INSERTINTOTrans_NSELECT*FROMTransWHERETrans_Date981231;DROPTABLETrans;RENAMETABLETrans_NTOTrans;,先建立空表，通过insert/select方式插入数据这是非常快的操作！先备份，然后做变更操作，更加安全！,对于大表进行Update/DELETE操作，将耗费相当多的资源与相当长的时间。Update/Delete操作，需要事务日志TJ（TransientJournal）以防意外中断导致数据受到破坏在Update/Delete操作中途被Cancel，系统则需回滚，这将耗更多的资源与时间！,DELETEFROMTransWHERETrans_DateThecontentsofSpool1aresentbacktotheuserastheresultofstatement1.Thetotalestimatedtimeis24.38seconds.,n-TableJoins,Agenda,TeradataClient安装TeradataSQLAssistant,如果改变一下上面的问题，要求查找员工姓中任意位置有字母E或者S的员工，则应使用ANY或SOME。SELECTfirst_name,last_nameFROMemployeeWHERElast_nameLIKEANY(%E%,%S%);,大小写敏感,SELECT1WHERESc(CS)=sc;SELECT1WHERE(Sc(UPPERCASE)(CASESPECIFIC)=sc;SELECT1WHERESc(CASESPECIFIC)=sc;SELECT1WHERESc(NOTCASESPECIFIC)=sc;SELECTUPPER(sc);SELECTLOWER(Sc);SELECTSc(UPPERCASE);,Teradata/Oracle常用语法比较,Teradata/Oracle常用语法比较（cont.）,TRIM函数,语法意义TRIM()去除字符数据中前后端的空格或者二进制数据中前后头的零TRIM(BOTHFROM)同上TRIM(TRAILINGFROM)去除后端的空格或二进制零TRIM(LEADINGFROM)去除前端的空格或二进制零SQL:输出：SELECTTRIM(abc);abcSELECTTRIM(TRAILINGFROMabc)abcSELECTTRIM(LEADINGFROMabc)abcTeradata没replace函数,SQL变量,SELECTDATABASE;显示当前数据库DBCSELECTUSER;显示当前Session登陆的用户名DBCSELECTDATE,CURRENT_DATE;显示当前日期2010-05-21,2010-05-21定义格式:SELECTCAST(DATEASDATEFORMATYYYYMMDD)SELECTTIME,CURRENT_TIMESTAMP(0);显示当前时间08:43:17,2007-08-062010-05-2108:43:54+00:00转换:SELECTCAST(CURRENT_TIMESTAMP(0)ASCHAR(19);2010-05-2108:44:10SELECTCAST(CURRENT_TIMESTAMP(0)ASTIMESTAMP(0)FORMATYYYY-MM-DDBHH:MI:SS),日期(DATE)的操作,取当前天：SELECTCAST(CURRENT_DATEASDATEFORMATYYYYMMDD)取当前天的前一天，后一天SELECTCAST(CURRENT_DATE-1ASDATEFORMATYYYYMMDD)SELECTCAST(CURRENT_DATE+1ASDATEFORMATYYYYMMDD)取前(后)一个月的同一天SELECTADD_MONTHS(CURRENT_DATE,-1)SELECTADD_MONTHS(CURRENT_DATE,1)若current_date为20100331,20100331,20090229结果是什么？SELECTADD_MONTHS(DATE2009-02-28,-12)SELECTADD_MONTHS(DATE2009-02-28,1)取当前天所在月的第一天SELECTSUBSTR(CAST(CURRENT_DATEASDATEFORMATYYYYMMDD),1,6)|01;取当前天所在月的最后一天SELECTCAST(SUBSTR(CAST(ADD_MONTHS(CURRENT_DATE,1)ASDATEFORMATYYYYMMDD),1,6)|01ASDATEFORMATYYYYMMDD)-1日期相减SELECT(DATE2007-03-01-DATE2004-01-01)day(4);SELECT(DATE2007-03-01-DATE2004-01-01)month(4);时间相减SELECT(CAST(endtimeASTIMESTAMP)-CAST(starttimeASTIMESTAMP)MINUTE(4)ASm；SELECT(CAST(endtimeASTIMESTAMP)-CAST(starttimeASTIMESTAMP)SECOND(4)ASm；,日期(DATE)的操作(cont.),SELECTEXTRACT(YEARFROMDATE);SELECTEXTRACT(MONTHFROMDATE+30);SELECTEXTRACT(DAYFROMDATE+2);SELECTEXTRACT(HOURFROMTIME);SELECTEXTRACT(MINUTEFROMTIME);SELECTEXTRACT(SECONDFROMTIME);SELECTADD_MONTHS(DATE,2);SELECTADD_MONTHS(DATE,12*8);SELECTADD_MONTHS(2002-03-19,12);,系统日历：sys_calendar.calendar,时间范围：1900-01-01至2100-12-31包括：年/季/度/月/周/天的信息day_of_weekday_of_monthday_of_yearmonth_of_yearquarter_of_year,IN,Rank,INSERTINTOwt_k(Currency_Cd,Fx_Mid_Price)SELECTTRIM(Currency_Cd),CAST(Fx_Mid_Price/UnitASDECIMAL(18,8)FROM$PDATADB.T99_EXCHANGE_RATE_PARAM_HWHERERecord_Stat_Cd=0ANDEffective_Dt20070701;但Wheretx_datelike200707%;不起作用,PPI的使用（cont.）,Partition上不要使用表达式，否则Partition不能被正确使用。T1.tx_date/100=CAST(20070917ASDATEFORMATYYYYMMDD)/100Substring(T1.tx_datefrom1for6)=200709应该修改为T1.tx_date=CAST(20070901ASDATEFORMATYYYYMMDD),PPI的使用（cont.）,脚本：tb_030040270.pl/*删除当月*/2小时delBASS1.tb_03004whereproc_dt=200709;insertintoBASS1.tb_030047小时。,sel.frompview.vw_evt_cust_socustwhereacpt_date=cast(200710|01asdate)cast(200710|01asdate)写法错误，PPI不起作用日期的正确写法:Cast(20071001asdateformatYYYYMMDD),在proc_dt建立PPI,PPI字段从Load_Date调整为acpt_date,数据类型,注意非日期字段与日期字段char,既然t查询外层有groupby操作去重，那么子查询内的Groupby去重是多余的。而且，两个子查询groupby后再用unionall，就可能再产生重复记录，那么groupby也失去意义了。解决方法：把t查询内部的两个groupby去掉即可类似的Distinct问题，可效仿解决。,去重,去重,去重,Groupbyvs.Distinct,Distinct是去除重复的操作Groupby是聚集操作某些情况下，两者可以起到相同的作用。两者的执行计划不一样，效率也不一样建议：使用Groupby,selectsubs_id,acct_idfromPVIEW.VW_FIN_ACCT_SUBS_HISwhereefct_date20070701groupby1,2,selectDISTINCTsubs_id,acct_idfromPVIEW.VW_FIN_ACCT_SUBS_HISwhereefct_date20070701,Unionvs.Unionall,Union与Unionall的作用是将多个SQL的结果进行合并。Union将自动剔除集合操作中的重复记录；需要耗更多资源。Unionall则保留重复记录，一般建议使用Unionall。第一个SELECT语句，决定输出的字段名称，标题，格式等要求所有的SELECT语句：1)必须要有同样多的表达式数目；2)相关表达式的域必须兼容,select*from(selecta)T1(col1)unionselect*from(selectbc)T2(col2),select*from(selectbc)T3(col3)unionallselect*from(selecta)T1(col1)unionallselect*from(selectbc)T2(col2),col3-abcbc,col1-ab,先Groupby再join,运行时间11小时Selectcasewhenb.CUST_Brand_IDisnullthen5020whenb.CUST_Brand_IDin(2000,5010)then5020elseb.CUST_Brand_IDend,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0)asThsy_Accum_New_SUBS_CHRG_DUR,sum(casewhenb.call_type_code=20thenb.Bas_CHRG_DUR_Unitelse0END)fromVT_ttinnerjoinVT_bbont.Subs_ID=b.Subs_IDleftjoinPVIEW.vw_cconb.Long_Type_Level_SECND=c.Long_Type_Level_SECNDleftjoinPVIEW.vw_ddonb.Roam_Type_Level_SECND=d.Roam_Type_Level_SECNDgroupby1;,记录数情况：t:580万，b:9400万,c:8,d:8主要问题：假如连接顺序为：(bjoinc)joind)joint)则是(9400万join8)join8)join580万)数据分布时间长(IO多)，连接次数多解决方法：先执行(tjoinb)，然后groupby，再joinc，d,先Groupby再join(cont.),40秒Selectcasewhenb.CUST_Brand_IDisnullthen5020whenb.CUST_Brand_IDin(2000,5010)then5020elseb.CUST_Brand_IDend,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0)asThsy_Accum_New_SUBS_CHRG_DUR,sum(casewhenb.call_type_code=20thenb.Bas_CHRG_DUR_Unitelse0END)from(selectCUST_Brand_ID,call_type_code,Long_Type_Level_SECND,Roam_Type_Level_SECND,sum(Bas_CHRG_DUR_Unit)Bas_CHRG_DUR_Unit,count(*)quanfromVT_twheresubs_idin(selectsubs_idfromVT_b)groupby1,2,3,4)bleftjoinPVIEW.vw_cconb.Long_Type_Level_SECND=c.Long_Type_Level_SECNDleftjoinPVIEW.vw_MID_CDE_ROAM_TYPE_LVLdonb.Roam_Type_Level_SECND=d.Roam_Type_Level_SECNDgroupby1;,记录数情况：t:580万，b:9400万,c:8,d:8处理过程：先执行(tjoinb)，然后groupby，再joinc，d结果：1、VT_TjoinVT_bPI相同，mergejoin，只需10秒2、经过groupby，b表只有332记录3、bjoincjoind，就是：332884、最终结果：5记录，共40秒,先Groupby再join（cont.）,先汇总再连接，可以减少参与连接的数据集大小，减少比较次数，从而提高效率。以下面SQL为例，假设历史表（History）有1亿条记录左边的SQL，需要进行1亿90次比较右边的SQL，则只需要1亿2次比较,SELECTH.product_id,sum(H.account_num)FROMHistoryH,CalendarDTWHEREH.sale_date=DT.calendar_dateANDDT.quarter=3GROUPBY1;,SELECTH.product_id,SUM(H.account_num)FROMHistoryH,(SELECTmin(calendar_date)min_date,max(calendar_date)max_dateFROMCalendarWHEREquarter=3)DTWHEREH.sale_dateBETWEENDT.min_dateandDT.max_dateGROUPBY1;,提取公共SQL形成临时表,出现以下SQL代码段，共5次，平均每次执行需10分钟。FROMPVIEW.VW_xx_MONa,PVIEW.VW_MID_yyb,vt_subscWHEREa.CUST_Brand_ID=b.SUBS_Brand_Level_ThirdANDa.CAL_Month=200908ANDa.SUBS_ID=c.SUBS_ID。整个脚本需要扫描以下SQL14次，平均每次执行需3分钟PVIEW.VW_xx_MONwhereCAL_Month=200908提取公共SQL，形成临时表，较少扫描(IO)次数。该脚本，经过优化之后，从50分钟缩减至10分钟,关联条件(1),SelectA.a2,B.b2fromAjoinBonsubstring(A.a1from1for7)=B.b1应该写为SelectA.a2,B.b2from(selectsubstring(a1from1for7)asa1_new,a2fromA)A_newjoinBona1_new=b1,关联条件(2),SelectA.a2,B.b2fromAjoinBonTRIM(A.a1)=TRIM(B.b1)应该写为SelectA.a2,B.b2fromAjoinBonA.a1=B.b1,SQL书写不当可能会引起笛卡儿积,以下面两个SQL为例，它们将进行笛卡儿积操作。例子1：Selectemployee.emp_no,employee.emp_nameFromemployeeA例子2：SELECTA.EMP_Name,B.Dept_NameFROMemployeeA,DepartmentBWherea.dept_no=b.dept_no;,改变查询计划的手段,在表上点右键选择SpaceSummary菜单，查看Skewfactor项，大于20时需要调整表的PI收集统计信息关联字段上的统计信息Partition上的统计信息Where条件上的统计信息Groupby字段上的统计信息查看某个表的统计信息情况：helpstatDBName.TableName通过Explain查看，尚需统计哪些信息？diagnostichelpstatsonforsession;总的来说尽量应当定时在系统不繁忙的时候做；执行频率应当和数据变化量成正比；,优化示例1：数据分布与JOIN方法,select200709,a.City_ID,a.SUBS_ID,a.BELONG_DISTRICTfrom(selectCity_ID,SUBS_ID,BELONG_DISTRICTfromdwview.vw_awhereCAL_Month=200612unionselectCity_ID,SUBS_ID,BELONG_DISTRICTfromdwview.vw_b)ainnerjoindwview.vw_cbona.subs_id=b.subs_idWhereCAL_Month=200709AndSUBS_STS_CodenotIn(10,11,12,13,20,30,60)andSUBS_STS_EFCT_Datebetween200709|01and20070930groupby1,2,3,4;,主要问题：1、把表b进行Duplicate统计信息不齐全，认为表b经过条件过滤只有130条记录，实际上有200万记录左右2、用ProductJoin连接算法表A有1000万记录Duplicate连接，共进行比较次数：1000万200万3、最优的Join方法？4、解决办法：对表b收集统计相应字段的信息必要的话，固化表A，并统计字段subs_id,优化示例2：数据分布与JOIN方法,11小时Selectcasewhenb.CUST_Brand_IDisnullthen5020whenb.CUST_Brand_IDin(2000,5010)then5020elseb.CUST_Brand_IDend,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0)asThsy_Accum_New_SUBS_CHRG_DUR,。fromVT_ttinnerjoinVT_bbont.Subs_ID=b.Subs_IDleftjoinPVIEW.vw_Cconb.Long_Type_Level_SECND=c.Long_Type_Level_SECNDleftjoinPVIEW.vw_Ddonb.Roam_Type_Level_SECND=d.Roam_Type_Level_SECNDgroupby1;,记录数情况：t:580万，b:9400万,c:8,d:8都有统计信息主要问题：1、连接顺序：(bJoinc)joind)joint2、对表b进行3次redistribute3、连接算法：MergeJoin4、原因：b表经过汇总而得，虽然知有总记录数，但未知各个join字段的情况,解决办法？固化b，并对b表连接字段进行统计调整连接顺序？,优化示例3固化临时表,脚本：rpt_mart_new_comm_mon0400.pl.Droptablettemp.mart_new_comm_mon0400_DUR_1;Createmultisettablettemp.mart_new_comm_mon0400_DUR_1as()withdataprimaryindex(subs_id);Collectstatonttemp.mart_new_comm_mon0400_DUR_1columnsubs_id;Selectcasewhenb.CUST_Brand_IDisnullthen5020whenb.CUST_Brand_IDin(2000,5010)then5020elseb.CUST_Brand_IDend,。fromVTNEW_SUBS_THISYEARtinnerjointtemp.mart_new_comm_mon0400_DUR_1bont.Subs_ID=b.Subs_IDleftjoin.groupby1;Droptablettemp.mart_new_comm_mon0400_DUR_1;,固化临时表：注意命名的唯一性,并非所有临时表都需要固化!,优化示例4强制多表JOIN的顺序,v_title,sum(.)ASfeefromPVIEW.VW_STL_GSM_INTERaINNERJOINpview.vw_CdE_TARRIF_dISTbONa.OPPER_Home_Area=b.Long_PrefixINNERJOINdwpview.vw_LOC_provcONv_code=v_idWHERESelf_Home_AreaIN-主叫为本省用户(selectcity_Idfromttemp.vt_city_Id)andtrim(SETLMT_Area)in-结算地为内蒙(selectcity_Idfromttemp.vt_city_Id)andSELF_Telco_Operator_Code=2andPROC_Datebetween20070601and20070630groupby1,418记录,32记录,12记录,9亿记录,12记录,JOIN顺序：1.ajoinb:比较次数9亿418结果9亿条2、(ajoinb)joinc:比较次数9亿32结果9亿条9亿418+9亿32次比较9亿450次比较,优化示例4强制多表JOIN的顺序(cont.),v_title,sum(.)ASfeefrom(select*fromttemp.TB_STL_GSM_INTER06whereSELF_Telco_Operator_Code=2andPROC_Datebetween20070601and20070630andSelf_Home_AreaIN-主叫为本省用户(selectcity_Idfromttemp.vt_city_Id)andtrim(SETLMT_Area)in-结算地为内蒙(selectcity_Idfromttemp.vt_city_Id)aINNERjoin(selectb1.Long_Prefix,v_titleFROMpview.vw_CdE_TARRIF_dISTb1INNERjoindwpview.vw_LOC_provc1ONv_code=v_id)bONa.OPPER_Home_Area=b.Long_Prefixgroupby1,SQL重写JOIN顺序：1.bjoinc:比较次数32418结果:418条2、ajoin(bjoinc):比较次数9亿418结果9亿条9亿418次比较,优化示例5强制多表JOIN的顺序,selecta.subs_id,a.Phone_NUM,*,sum(coalesce(c.last_arpu,0)last_arpu,sum(coalesce(d.moths_arpu,0)moths_arpu,sum(coalesce(f.accum_intg,0)CUS