实用教程(Teradata)

实用教程（Teradata）,陆世潮2008年9月,问题总结,常见问题分类：表属性不对:Set/Multiset问题：INSERT操作慢主索引(PI)设置不合理问题1：数据倾斜度大，空间爆满。问题2：JOIN操作，数据需要重分布。分区索引(PPI)设置不合理问题：全表扫描连接条件过于复杂问题：系统无法优化执行计划缺乏统计信息问题：系统无法找到最优化的执行计划,SQL跑得慢哈！,提纲,Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化,Teradata体系架构,TeradataandMPPSystems,RDBMSARCH,LogicalExampleofNPPIversusPPI,提纲,Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化,表属性：Set,例子：pmart.RPT_NM_GRP_PRE_WARN_MON内蒙移动集团客户预警指标月报表,假设原有1286449条记录插入:152853条记录耗时：15秒,表属性：Set,例子：pmart.RPT_NM_GRP_PRE_WARN_MON内蒙移动集团客户预警指标月报表,建议：Teradata中都用MultiSet,假设原有1286449条记录插入:152853条记录耗时：1秒,例子：CREATEMULTISETTABLEtttemp.VT_SUBS_VIOC_QUANas(SELECT*FROMtttemp.MID_SUBS_VIOC_QUANWHERECAL_MONTH=200802AND*)WITHDATAPRIMARYINDEX(subs_id);,临时表,默认为:Set需要指定为:Multiset,字段越多，记录越多差别越明显,PI（PrimaryIndex主索引）的选择,PI影响数据的存储与访问，其选择标准：不同值尽量多的字段(MoreUniqueValues)使用频繁的字段:包括值访问和连接访问少更新PI字段不宜太多最好是手动指定PI,例子：用户语音业务量中间表CREATEMULTISETTABLEtttemp.MID_SUBS_VIOC_QUAN(CAL_MonthINTEGERTITLE统计月份,City_IDCHAR(4)TITLE地市标识,Channel_IDCHAR(8)TITLE渠道标识,Subs_idCHAR(12)TITLE用户标识,。)PRIMARYINDEX(subs_id);,例子：用户语音业务量临时表CREATEMULTISETTABLEtttemp.VT_SUBS_VIOC_QUANas(SELECT*FROMtttemp.MID_SUBS_VIOC_QUANWHERECAL_MONTH=200802AND*)WITHDATAPRIMARYINDEX(subs_id);,Subs_ID:频繁使用UniqueValue多,如果不指定PI，系统默认为:Cal_Month,PI（PrimaryIndex主索引）的选择(cont.),例子：梦网客户活跃客户分析CREATEMULTISETTABLEPMART.FCT_DATA_MONNET_ACTIVE_MON(CAL_MonthINTEGERTITLE统计月份,City_IDCHAR(4)TITLE地市标识,Channel_IDCHAR(8)TITLE渠道标识,Mont_SVC_Type_CodCHAR(3)TITLE梦网业务类型编码,Mont_SVC_CAT_MicroCls_CodCHAR(3)TITLE梦网业务分类小类编码,Mont_SVC_CHRG_Type_CodCHAR(2)TITLE梦网业务计费类型编码,THR_Brand_CodCHAR(1)TITLE三大品牌编码,Mont_Consume_Level_CodCHAR(2)TITLE梦网消费层次编码,Consume_Level_CodCHAR(2)TITLE消费层次编码,。)PRIMARYINDEX(CAL_Month,City_ID,Channel_ID,Mont_SVC_Type_Cod,Mont_SVC_CAT_MicroCls_Cod,Mont_SVC_CHRG_Type_Cod,THR_Brand_Cod,Mont_Consume_Level_Cod,Consume_Level_Cod);PI：9字段2字段：City_ID,Channel_ID调整PI后，在右边的SQL中，PI是否起作用？,以下SQL，PI是否起作用？：1.值访问Select*FromFCT_DATA_MONNET_ACTIVE_MONWhereCity_ID=070010andChannel_ID=0100andcal_month=2007072.连接访问Select*FromFCT_DATA_MONNET_ACTIVE_MONALEFTJOINMID_CHANNEL_INFO_DAILYBONA.Channel_ID=B.Channel_IDandA.City_ID=b.City_IDLEFTJOINVW_CDE_REGION_TYPECONA.City_ID=C.City_ID3、值访问连接访问Select*FromFCT_DATA_MONNET_ACTIVE_MONA,VT_INFOBWHEREA.Channel_ID=B.Channel_IDANDA.City_ID=B.City_IDANDA.CAL_MONTH=200707ANDA.Consume_Level_Cod=B.Consume_Level_Cod,PPI的使用,PPI（PartitionPrimaryIndex，分区索引），把具有相同分区值的数据聚簇存放在一起；类似于SQLServer的聚簇索引（ClusterIndex），Oracle的聚簇表（ClusterTable）。利用PPI，可以快速插入/访问同一个Partition（分区）的数据。,CREATEMULTISETTABLEqdata.TB_DQC_KPI_CHECK_RESULT(TX_DATEDATEFORMATYYYYMMDDTITLE数据日期NOTNULL,KPI_CODEINTEGERTITLE指标代码NOTNULL,。)PRIMARYINDEX(KPI_CODE)PARTITIONBYRANGE_N(TX_DATEBETWEENCAST(20030101)ASDATEFORMATYYYYMMDD)ANDCAST(20191231)ASDATEFORMATYYYYMMDD)EACHINTERVAL1DAY,NORANGEORUNKNOWN);,Select*FromTB_DQC_KPI_CHECK_RESULTWheretx_date=20070701;或Wheretx_datebetween20070701and20070731;或Wheretx_date20070701;但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,创建可变临时表,它仅存活于同一个Session之内注意指定可变临时表为multiset（通常也要指定PI）可变临时表不能带有PPI例子1:createvolatilemultisettablevt_RETAIN_ANLY_MONas(selectcol1,col2,fromwheregroupby.）withdataPRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;例子2:createvolatilemultisettablevt_RETAIN_ANLY_MON(col1char(2),col2varchar(12)NOTNULL）PRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;,创建可变临时表（cont.）,例子3:createvolatilemultisettablevt_RETAIN_ANLY_MONas(selectcol1,cast(adcasvarchar(12)col2fromwhere）withnodataPRIMARYINDEX(col1)ONCOMMITPRESERVEROWS;例子4:createvolatilemultisettablevt_net_gsm_nlaspdata.tb_net_gsm_nlwithnodataONCOMMITPRESERVEROWS;,字段col2将用unicode字符集；当跟普通字段（latin字符集）join时，需要进行数据重新分布。不建议,失败：因为pdata.tb_net_gsm_nl有PPI而可变临时表不允许有PPI,固化临时表,固化临时表，就是把查询结果存放到一张物理表。共下次分析或他人使用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;,数据类型,注意非日期字段与日期字段char,Statement1SELECT*FROMEmp1WHEREEmp_no=1234;,Statement2SELECT*FROMEmp1WHEREEmp_no=1234;,Table1CREATETABLEEmp2(Emp_noINTEGER,Emp_nameCHAR(20)PRIMARYINDEX(Emp_no);,Statement1SELECT*FROMEmp2WHEREEmp_no=1234;,Statement2SELECT*FROMEmp2WHEREEmp_no=1234;,Case2,ResultsinFullTableScan,Resultsinunnecessaryconversion,目标列的选择,减少目标列，可以少消耗SPOOL空间，从而提高SQL的效率当系统任务繁忙，系统内存少的时候，效果尤为明显。举例：GSM语言话单表，PDATA.TB_NET_GSM_NL共有73字段，以下SQL供返回1.6亿条记录左边的SQL，记录最长为：698字节，平均399字节右边的SQL，记录最长为：59字节，平均30字节两者相差400多GB的SPOOL空间，IO次数也随着相差甚大！,SPOOL空间估计：497GB,SPOOL空间估计：42GB,SELECTSUBS_ID,MSISDN,Begin_Date,Begin_Time,Call_DUR,CHRG_DURFROMPDATA.TB_NET_GSM_NLWHEREPROC_DATEBETWEEN20070701AND20070731,SELECT*FROMPDATA.TB_NET_GSM_NLWHEREPROC_DATEBETWEEN20070701AND20070731,Where条件的限定,根据Where条件先进行过滤数据集，再进行连接(JOIN)等操作这样，可以减少参与连接操作的数据集大小，从而提高效率好的查询引擎，可以自动优化；但有些复杂SQL，查询引擎优化得并不好。注意：系统的SQL优化，只是避免最差的，选择相对优的，未必能够得到最好的优化结果。,SELECTA.TX_DATE,A.KPI_CODE,B.SRC_NAME,A.KPI_VALUEFROM(select*fromqdata.tb_dqc_kpi_check_resultwhereTX_DATE=20070701ANDKPI_CODE=65)ALEFTJOIN(SELECT*FROMqdata.tb_dqc_kpi_defwhereKPI_CODE=65andN_TYPE=M)BONA.KPI_CODE=B.KPI_CODE,SELECTA.TX_DATE,A.KPI_CODE,coalesce(B.SRC_NAME,noname),A.KPI_VALUEFROMqdata.tb_dqc_kpi_check_resultALEFTJOINqdata.tb_dqc_kpi_defBONA.KPI_CODE=B.KPI_CODEWHEREA.TX_DATE=20070701ANDA.KPI_CODE=65ANDB.N_TYPE=M,rewrite,用CaseWhen替代UNION,selcity_id,channel_id,cust_brand_id,sum(stat_values)asstat_valuesfrom(.selectt.city_id-语音杂志计费量,coalesce(v.channel_id,b.channel_id,-)aschannel_id,cust_brand_id,sum(casewhenSMS_SVC_Type_Level_SECND=017andCall_Type_Codein(00,10,01,11)thensms_quanelse0END)asstat_valuesfromPVIEW.vw_mid_sms_svc_quan_dailytleftjoinVT_SUBSvont.subs_id=v.subs_idleftjoinPVIEW.vw_FCT_CDE_BUSN_CITY_TYPEbont.city_id=b.City_IDwherecal_date=20070914groupby1,2,3unionallselectt.city_id-梦网短信计费量,coalesce(v.channel_id,b.channel_id,-)aschannel_id,cust_brand_id,sum(sms_quan)asstat_valuesfromPVIEW.vw_mid_sms_svc_quan_dailytleftjoinVT_SUBSvont.subs_id=v.subs_idleftjoinPVIEW.vw_FCT_CDE_BUSN_CITY_TYPEbont.city_id=b.City_IDwherecal_date=20070914andSMS_SVC_Type_Level_SECNDlike02%andSMS_SVC_Type_Level_SECNDnotin(021,022)groupby1,2,3.)tmpGroupby1,2,3,两个子查询的表连接部分完全一样两个子查询除了取数据条件，其它都一样。Unionall是多余的，它需要重复扫描数据，进行重复的JOIN可以用Casewhen替代union,作业:KPI_NWR_SMS_BILL_QUAN描述:点对点短信计费量脚本:kpi_nwr_sms_bill_quan0600.pl,用CaseWhen替代UNION(cont.),selcity_id,channel_id,cust_brand_id,sum(stat_values)asstat_valuesfrom(selectt.city_id,coalesce(v.channel_id,b.channel_id,-)aschannel_id,cust_brand_id,sum(CASEWHENSMS_SVC_Type_Level_SECND=017andCall_Type_Codein(00,10,01,11)THENsms_quan-语音杂志计费量WHENSMS_SVC_Type_Level_SECNDlike02%andSMS_SVC_Type_Level_SECNDnotin(021,022)THENsms_quan-梦网短信计费量ELSE0END)asstat_valuesfromPVIEW.vw_mid_sms_svc_quan_dailytleftjoinVT_SUBSvont.subs_id=v.subs_idleftjoinPVIEW.vw_FCT_CDE_BUSN_CITY_TYPEbont.city_id=b.City_IDwherecal_date=20070914.)tmpGroupby1,2,3,SQL优化重写,用OR替代UNION,Selectcity_id,channel_id,cust_brand_id,sum(sms_quan)stat_valuesfrom(selectt.city_id-语音杂志计费量,coalesce(v.channel_id,b.channel_id,-)aschannel_id,cust_brand_id,sum(sms_quan)stat_valuesfromPVIEW.vw_mid_sms_svc_quan_dailytleftjoinVT_SUBSvont.subs_id=v.subs_idleftjoinPVIEW.vw_FCT_CDE_BUSN_CITY_TYPEbont.city_id=b.City_IDwherecal_date=20070914andSMS_SVC_Type_Level_SECND=017andCall_Type_Codein(00,10,01,11)groupby1,2,3unionallselectt.city_id-梦网短信计费量,coalesce(v.channel_id,b.channel_id,-)aschannel_id,cust_brand_id,sum(sms_quan)asstat_valuesfromPVIEW.vw_mid_sms_svc_quan_dailytleftjoinVT_SUBSvont.subs_id=v.subs_idleftjoinPVIEW.vw_FCT_CDE_BUSN_CITY_TYPEbont.city_id=b.City_IDwherecal_date=20070914andSMS_SVC_Type_Level_SECNDlike02%andSMS_SVC_Type_Level_SECNDnotin(021,022)groupby1,2,3)TGroupby1,2,3,两个子查询的表连接部分完全一样两个子查询除了取数据条件，其它都一样。Unionall是多余的，它需要重复扫描数据，进行重复的JOIN可以用OR替代union此类的问题，在脚本中经常见到。,用OR替代UNION(cont.),selectt.city_id,coalesce(v.channel_id,b.channel_id,-)aschannel_id,cust_brand_id,sum(sms_quan)asstat_valuesfromPVIEW.vw_mid_sms_svc_quan_dailytleftjoinVT_SUBSvont.subs_id=v.subs_idleftjoinPVIEW.vw_FCT_CDE_BUSN_CITY_TYPEbont.city_id=b.City_IDwherecal_date=20070914and（SMS_SVC_Type_Level_SECND=017-语音杂志计费量andCall_Type_Codein(00,10,01,11)）OR(SMS_SVC_Type_Level_SECNDlike02%-梦网短信计费量andSMS_SVC_Type_Level_SECNDnotin(021,022)Groupby1,2,3,SQL优化重写,去掉多余的Distinct与Groupby,selt.operator,t.acpt_channel_id,t.acpt_city_id,t.subs_id,t.acpt_dateasevt_dateFrom(seloperator,ACPT_Channel_ID,acpt_city_id,subs_id,acpt_datefrompview.vw_evt_cust_socustwhereacpt_date=20071007andso_meth_codein(0,1,2)andPROC_STS_Code=-1groupby1,2,3,4,5unionallseloperator_numasoperator,ACPT_Channel_ID,acpt_city_id,subs.subs_id,charge_dateasacpt_datefrompview.vw_fin_busi_recbusjoincrmmart.subs_day_info_dailysubsonsubs.msisdn=bus.msisdnwherecharge_date=20071007groupby1,2,3,4,5)tgroupby1,2,3,4,5;,既然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,脚本：rpt_mart_new_comm_mon0400.pl11小时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)fromVTNEW_SUBS_THISYEARtinnerjoinVTDUR_MONbont.Subs_ID=b.Subs_IDleftjoinPVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVLconb.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主要问题：假如连接顺序为：(bjoinc)joind)joint)则是(9400万join8)join8)join580万)数据分布时间长(IO多)，连接次数多解决方法：先执行(tjoinb)，然后groupby，再joinc，d,先Groupby再join(cont.),脚本：rpt_mart_new_comm_mon0400.pl40秒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(*)quanfromVTDUR_MONwheresubs_idin(selectsubs_idfromVTNEW_SUBS_THISYEAR)groupby1,2,3,4)bleftjoinPVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVLconb.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、VTDUR_MONjoinVTNEW_SUBS_THISYEARPI相同，mergejoin，只需10秒2、经过groupby，b表只有332记录3、bjoincjoind，就是：332884、最终结果：5记录，共40秒,先Groupby再join（cont.）,先汇总再连接，可以减少参与连接的数据集大小，减少比较次数，从而提高效率。以下面SQL为例，假设历史表（History）有1亿条记录左边的SQL，需要进行1亿90次比较右边的SQL，则只需要1亿1次比较,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形成临时表,脚本：rpt_nmmart_comm_subs_mon0403.pl出现以下SQL代码段，共5次，平均每次执行需10分钟。FROMPVIEW.VW_MID_VOIC_SVC_QUAN_MONa,PVIEW.VW_MID_CDE_SUBS_BRAND_LVLb,vt_subscWHEREa.CUST_Brand_ID=b.SUBS_Brand_Level_ThirdANDa.CAL_Month=200708ANDa.SUBS_ID=c.SUBS_ID。整个脚本需要扫描以下SQL14次，平均每次执行需3分钟PVIEW.VW_MID_VOIC_SVC_QUAN_MONwhereCAL_Month=200708提取公共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;,修改表定义,常见的表定义修改操作：增加字段修改字段长度建议的操作流程Renametabledb.tablexasdb.tabley;通过Showtable语句获得原表db.tablex的定义定义新表：db.tablexInsertintodb.tablex(。)select。Fromdb.tabley;Droptabledb.tabley;Teradata提供ALTERTABLE语句，可进行修改表定义但，不建议采用ALTERTABLE方式。,插入/更新/删除记录时，尽量不要Abort,当目标表有数据时，插入和更新操作，以及部分删除，都产生TJ如果此时abort该操作，系统将会回滚,DeleteBASS1.tb_03004whereproc_dt=200709;,UPDATECustomerSETCredit_Limit=Credit_Limit*1.20;,DELETEFROMTransWHERETrans_Date981231;DROPTABLETrans;RENAMETABLETrans_NTOTrans;,先建立空表，通过insert/select方式插入数据这是非常快的操作！先备份，然后做变更操作，更加安全！,对于大表进行Update/DELETE操作，将耗费相当多的资源与相当长的时间。Update/Delete操作，需要事务日志TJ（TransientJournal）以防意外中断导致数据受到破坏在Update/Delete操作中途被Cancel，系统则需回滚，这将耗更多的资源与时间！在经分系统中，应严防此类事件发生！,DELETEFROMTransWHERETrans_DateThecontentsofSpool1aresentbacktotheuserastheresultofstatement1.Thetotalestimatedtimeis24.38seconds.,n-TableJoins（多表连接）,多表连接可以分解为两两连接.对下面的SQL，查询引擎可以选择较优的执行计划：例如，Plan1或者Plan2。SELECT.FROMTable_A,Table_B,Table_C,Table_DWHERE.;对下面的SQL，查询引擎只能选择Plan2，否则结果有可能不对。SELECT.FROMTable_AleftjoinTable_BonA.c1=B.c2INNERJOINTable_CONB.c2=c.c3LEFTJOINTable_DOND.C4=A.C1WHERE.;,提纲,Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化,改变查询计划的手段,修改PI收集统计信息关联字段上的统计信息Partition上的统计信息Where条件上的统计信息Groupby字段上的统计信息查看某个表的统计信息情况：helpstatDBName.TableName查看详尽的统计情况：select*frompview.vw_statistic_info通过Explain查看，尚需统计哪些信息？diagnostichelpstatsonforsession;,优化示例1：数据分布与JOIN方法,脚本：tb_rmis_bb40_mon2900280.pl，16小时select200709,a.City_ID,a.SUBS_ID,a.BELONG_DISTRICTfrom(selectCity_ID,SUBS_ID,BELONG_DISTRICTfromMMART.TB_MIS_DISTRICT_DIVISIONwhereCAL_Month=200612unionselectCity_ID,SUBS_ID,BELONG_DISTRICTfromMMART.TB_MIS_DISTRICT_DIVISION_NEW)ainnerjoinpview.vw_mid_subs_info_monbona.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,优化示例1explain,5)Weexecutethefollowingstepsinparallel.1)Wedoanall-AMPsRETRIEVEstepfromMMART.TB_MIS_DISTRICT_DIVISION_NEWbywayofanall-rowsscanwithnoresidualconditionsintoSpool1(all_amps),whichisredistributedbyhashcodetoallAMPs.ThenwedoaSORTtoorderSpool1bythesortkeyinspoolfield1eliminatingduplicaterows.ThesizeofSpool1isestimatedwithlowconfidencetobe7,278,252rows.Theestimatedtimeforthisstepis1.46seconds.2)Wedoanall-AMPsRETRIEVEstepfromasinglepartitionofPMART.MID_SUBS_INFO_MONwithaconditionof(PMART.MID_SUBS_INFO_MON.CAL_Month=200709)witharesidualconditionof(PMART.MID_SUBS_INFO_MON.SUBS_STS_EFCT_Date=DATE2007-09-01)AND(PMART.MID_SUBS_INFO_MON.SUBS_STS_EFCT_Date=DATE2007-09-30)AND(。)intoSpool5(all_amps),whichisduplicatedonallAMPs.Theinputtablewillnotbecachedinmemory,butitiseligibleforsynchronizedscanning.ThesizeofSpool5isestimatedwithlowconfidencetobe130rows.Theestimatedtimeforthisstepis0.04seconds.6)Wedoanall-AMPsJOINstepfromSpool5(LastUse)bywayofanall-rowsscan,whichisjoinedtoSpool1(LastUse)bywayofanall-rowsscan.Spool5andSpool1arejoinedusingaproductjoin,withajoinconditionof(SUBS_ID=SUBS_ID).TheresultgoesintoSpool4(all_amps),whichisbuiltlocallyontheAMPs.ThesizeofSpool4isestimatedwithindexjoinconfidencetobe14,556,503rows.Theestimatedtimeforthisstepis3.05seconds.,优化示例2：数据分布与JOIN方法,脚本：rpt_mart_new_comm_mon0400.pl11小时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,。fromVTNEW_SUBS_THISYEARtinnerjoinVTDUR_MONbont.Subs_ID=b.Subs_IDleftjoinPVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVLconb.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都有统计信息主要问题：1、连接顺序：(bJoinc)joind)joint2、对表b进行3次redistribute3、连接算法：MergeJoin4、原因：b表经过汇总而得，虽然知有总记录数，但未知各个join字段的情况,解决办法？固化b，并对b表连接字段进行统计调整连接顺序？,。2)Next,wedoanall-AMPsRETRIEVEstepfromLUSC.bbywayofanall-rowsscanwithnoresidualconditionsintoSpool4(all_amps),whichisredistributedbyhashcodetoallAMPs.ThenwedoaSORTtoorderSpool4byrowhash.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool4isestimatedwithhighconfidencetobe94,078,021rows.Theestimatedtimeforthisstepis17minutesand6seconds.3)Weexecutethefollowingstepsinparallel.1)Wedoanall-AMPsJOINstepfromSpool4(LastUse)bywayofaRowHashmatchscan,whichisjoinedtoPMART.MID_CDE_LONG_CALL_TYPE_LVLbywayofaRowHashmatchscanwithnoresidualconditions.Spool4andPMART.MID_CDE_LONG_CALL_TYPE_LVLareleftouterjoinedusingamergejoin,withajoinconditionof(Long_Type_Level_SECND=PMART.MID_CDE_LONG_CALL_TYPE_LVL.Long_Type_Level_SECND).TheresultgoesintoSpool5(all_amps),whichisbuiltlocallyontheAMPs.Theresultsp