teradata数据仓库编程实用教程Terad课件

实用教程（Teradata）陆世潮2008年9月实用教程（Teradata）陆世潮问题总结常见问题分类：表属性不对:Set/Multiset问题：INSERT操作慢主索引(PI)设置不合理问题1：数据倾斜度大，空间爆满。问题2：JOIN操作，数据需要重分布。

分区索引(PPI)设置不合理问题：全表扫描连接条件过于复杂问题：系统无法优化执行计划缺乏统计信息问题：系统无法找到最优化的执行计划SQL跑得慢哈！问题总结常见问题分类：SQL跑得慢哈！提纲Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化提纲Teradata体系架构TeradataandMPPSystemsRDBMSARCHTeradata体系架构TeradataandMPPLogicalExampleofNPPIversusPPI4AMPswith

OrdersTabledefined

withPPIonO_Date.RH O_# O_DateRH O_# O_DateRH O_# O_DateRH O_# O_Date'35' 1007 06/01'26' 1002 06/01'24' 1004 06/01'20' 1005 06/01'39' 1011 06/01'36' 1012 06/01'32' 1003 06/01'43' 1010 06/01'14' 1001 06/01'06' 1009 06/01'04' 1008 06/01'08' 1006 06/01'03' 1016 06/02'17' 1013 06/02'48' 1023 06/02'07' 1017 06/02'16' 1021 06/02'45' 1015 06/02'09' 1018 06/02'27' 1014 06/02'44' 1022 06/02'02' 1024 06/02'11' 1019 06/02'22' 1020 06/02'01' 1028 06/03'12' 1031 06/03'28' 1032 06/03'10' 1034 06/03'29' 1033 06/03'34' 1029 06/03'19' 1025 06/03'40' 1035 06/03'47' 1027 06/03'25' 1036 06/03'31' 1026 06/03'46' 1030 06/03'23' 1040 06/04'30' 1038 06/04'42' 1047 06/04'13' 1037 06/04'21' 1045 06/04'36' 1043 06/04'05' 1048 06/04'15' 1042 06/04'33' 1039 06/04'18' 1041 06/04'38' 1046 06/04'41' 1044 06/044AMPswith

OrdersTabledefined

withNPPI.'01' 1028 06/03'12' 1031 06/03'28' 1032 06/03'10' 1034 06/03'29' 1033 06/03'34' 1029 06/03'19' 1025 06/03'40' 1035 06/03'47' 1027 06/03'25' 1036 06/03'31' 1026 06/03'46' 1030 06/03'03' 1016 06/02'17' 1013 06/02'48' 1023 06/02'07' 1017 06/02'16' 1021 06/02'45' 1015 06/02'09' 1018 06/02'27' 1014 06/02'44' 1022 06/02'02' 1024 06/02'11' 1019 06/02'22' 1020 06/02'14' 1001 06/01'35' 1007 06/01'39' 1011 06/01'06' 1009 06/01'26' 1002 06/01'36' 1012 06/01'04' 1008 06/01'24' 1004 06/01'32' 1003 06/01'08' 1006 06/01'20' 1005 06/01'43' 1010 06/01'23' 1040 06/04'30' 1038 06/04'42' 1047 06/04'13' 1037 06/04'21' 1045 06/04'36' 1043 06/04'05' 1048 06/04'15' 1042 06/04'33' 1039 06/04'18' 1041 06/04'38' 1046 06/04'41' 1044 06/04RH O_# O_DateRH O_# O_DateRH O_# O_DateRH O_# O_DateLogicalExampleofNPPIversus提纲Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化提纲表属性：Set&MultiSetSetTable不允许记录重复MultiSetTable允许记录重复默认值:SetTableCreateTable...AS...生成的目标表属性默认为SetTable对SETTable进行INSERT操作，需要检查是否存在重复记录相当的耗资源若真要限定唯一性，可以通过UPI或USI实现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);例子：pmart.RPT_NM_GRP_PRE_WARN_MON内蒙移动集团客户预警指标月报表假设原有1286449条记录插入:152853条记录耗时：15秒表属性：Set&MultiSetSetTable不允许表属性：Set&MultiSet（cont.）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);例子：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字段越多，记录越多差别越明显表属性：Set&MultiSet（cont.）CREAPI（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_MonthPI（PrimaryIndex主索引）的选择PI影响数据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=‘070010’andChannel_ID=‘0100’andcal_month=2007072.连接访问Select*FromFCT_DATA_MONNET_ACTIVE_MONALEFTJOINMID_CHANNEL_INFO_DAILYB

ONA.Channel_ID=B.Channel_ID

andA.City_ID=b.City_IDLEFTJOINVW_CDE_REGION_TYPEC

ONA.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_CodPI（PrimaryIndex主索引）的选择(cont.PPI的使用PPI（PartitionPrimaryIndex，分区索引），把具有相同分区值的数据聚簇存放在一起；类似于SQLServer的聚簇索引（ClusterIndex），Oracle的聚簇表（ClusterTable）。利用PPI，可以快速插入/访问同一个Partition（分区）的数据。CREATEMULTISETTABLEqdata.TB_DQC_KPI_CHECK_RESULT(TX_DATEDATEFORMAT'YYYYMMDD'TITLE'数据日期'NOTNULL,KPI_CODEINTEGERTITLE'指标代码'NOTNULL,

。。。。

)PRIMARYINDEX(KPI_CODE)PARTITIONBYRANGE_N(TX_DATEBETWEENCAST(('20030101')ASDATEFORMAT'YYYYMMDD')ANDCAST(('20191231')ASDATEFORMAT'YYYYMMDD')EACHINTERVAL'1'DAY,NORANGEORUNKNOWN);Select*FromTB_DQC_KPI_CHECK_RESULTWheretx_date=‘20070701’;或Wheretx_datebetween‘20070701’and‘20070731’;或Wheretx_date>‘20070701’;但Wheretx_datelike‘200707%’;不起作用PPI的使用PPI（PartitionPrimaryInPPI的使用（cont.）Partition上不要使用表达式，否则Partition不能被正确使用。T1.tx_date/100=CAST('20070917'ASDATEFORMAT'YYYYMMDD')/100Substring(T1.tx_datefrom1for6)='200709’应该修改为T1.tx_date<=CAST('20070930'ASDATEFORMAT'YYYYMMDD')ANDT1.tx_date>=CAST('20070901'ASDATEFORMAT'YYYYMMDD')PPI的使用（cont.）PPI的使用（cont.）

脚本：tb_030040270.pl/*删除当月*/－－2小时delBASS1.tb_03004whereproc_dt='200709‘;insertintoBASS1.tb_03004－－7小时。。。。

sel...frompview.vw_evt_cust_socustwhereacpt_date<='20071007'andacpt_date>=cast('200710'||'01'asdate)

cast(‘200710’||‘01’asdate)写法错误，PPI不起作用日期的正确写法:Cast(‘20071001’asdateformat‘YYYYMMDD’)在proc_dt建立PPIPPI字段从Load_Date调整为acpt_datePPI的使用（cont.）脚本：tb_030040270.创建可变临时表它仅存活于同一个Session之内注意指定可变临时表为multiset（通常也要指定PI）可变临时表不能带有PPI例子1:createvolatile

multisettablevt_RETAIN_ANLY_MONas(selectcol1,col2,…from…where…groupby….）withdataPRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;例子2:

createvolatilemultisettablevt_RETAIN_ANLY_MON(col1char(2),

col2varchar(12)NOTNULL）PRIMARYINDEX(PI_Cols)ONCOMMITPRESERVEROWS;创建可变临时表它仅存活于同一个Session之内创建可变临时表（cont.）例子3:createvolatilemultisettablevt_RETAIN_ANLY_MONas(selectcol1,cast(‘adc’asvarchar(12))col2from…where…）withnodataPRIMARYINDEX(col1)

ONCOMMITPRESERVEROWS;例子4:createvolatilemultisettablevt_net_gsm_nlaspdata.tb_net_gsm_nlwithnodataONCOMMITPRESERVEROWS;字段col2将用unicode字符集；当跟普通字段（latin字符集）join时，需要进行数据重新分布。不建议失败：因为pdata.tb_net_gsm_nl有PPI而可变临时表不允许有PPI创建可变临时表（cont.）例子3:字段col2将用uni固化临时表固化临时表，就是把查询结果存放到一张物理表。共下次分析或他人使用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&date的转换与关联：如果数据类型一致可以直接使用；在CASEWHENorCOALESCE一定要使用显式的类型转换(CAST)CASEWHENA=BTHENDATE1ELSE‘20061031’END应写成CASEWHENA=BTHENDATE1ELSECAST(‘20061031’ASDATE)END数值运算时，确保运算过程中不丢失计算精度。CAST(100/3ASDEC(5,2))应该写成CAST(100/3.00ASDEC(5,2))数据类型注意非日期字段与日期字段char&date的转换字符(串)与数字相比较比较规则:1)比较两个值(字段)，它们的类型必须一样！2)当字符(串)与数字相比较时，先把字符(串)转换成数字，再进行比较。3)经分系统中容易出错的，有Cal_Month字段Case1Table1CREATETABLEEmp1(Emp_no CHAR(6),Emp_name CHAR(20))PRIMARYINDEX(Emp_no);Statement1SELECT *FROM Emp1WHERE Emp_no='1234';Statement2SELECT *FROM Emp1WHERE Emp_no=1234;Table1CREATETABLEEmp2(Emp_no INTEGER,Emp_name CHAR(20))PRIMARYINDEX(Emp_no);Statement1SELECT *FROM Emp2WHERE Emp_no=1234;Statement2SELECT *FROM Emp2WHERE Emp_no='1234';Case2ResultsinFullTableScanResultsinunnecessaryconversion字符(串)与数字相比较比较规则:Case1Table1S目标列的选择减少目标列，可以少消耗SPOOL空间，从而提高SQL的效率当系统任务繁忙，系统内存少的时候，效果尤为明显。举例：GSM语言话单表，PDATA.TB_NET_GSM_NL共有73字段，以下SQL供返回1.6亿条记录左边的SQL，记录最长为：698字节，平均399字节右边的SQL，记录最长为：59字节，平均30字节两者相差400多GB的SPOOL空间，IO次数也随着相差甚大！SPOOL空间估计：497GBSPOOL空间估计：42GBSELECTSUBS_ID,MSISDN,Begin_Date,Begin_Time,Call_DUR,CHRG_DURFROMPDATA.TB_NET_GSM_NLWHEREPROC_DATEBETWEEN‘20070701’AND‘20070731’

SELECT*FROMPDATA.TB_NET_GSM_NLWHEREPROC_DATEBETWEEN‘20070701’AND‘20070731’目标列的选择减少目标列，可以少消耗SPOOL空间，从而提高SWhere条件的限定根据Where条件先进行过滤数据集，再进行连接(JOIN)等操作这样，可以减少参与连接操作的数据集大小，从而提高效率好的查询引擎，可以自动优化；但有些复杂SQL，查询引擎优化得并不好。注意：系统的SQL优化，只是避免最差的，选择相对优的，未必能够得到最好的优化结果。SELECTA.TX_DATE,A.KPI_CODE,B.SRC_NAME,A.KPI_VALUEFROM(select*fromqdata.tb_dqc_kpi_check_resultwhereTX_DATE='20070701'ANDKPI_CODE=65)ALEFTJOIN(SELECT*FROMqdata.tb_dqc_kpi_def

whereKPI_CODE=65andN_TYPE=‘M’)BONA.KPI_CODE=B.KPI_CODESELECTA.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_CODE

WHEREA.TX_DATE='20070701'ANDA.KPI_CODE=65ANDB.N_TYPE=‘M’

rewriteWhere条件的限定根据Where条件先进行过滤数据集，再进用CaseWhen替代UNIONselcity_id,channel_id,cust_brand_id,sum(stat_values)as

stat_valuesfrom(

selectt.city_id语音杂志计费量,coalesce(v.channel_id,b.channel_id,'-')aschannel_id,cust_brand_id,sum(casewhenSMS_SVC_Type_Level_SECND='017'andCall_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='20070914'groupby1,2,3

unionallselectt.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='20070914'andSMS_SVC_Type_Level_SECNDlike'02%'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替代UNIONselcity_id,用CaseWhen替代UNION(cont.)selcity_id,channel_id,cust_brand_id,sum(stat_values)as

stat_valuesfrom(selectt.city_id,coalesce(v.channel_id,b.channel_id,'-')aschannel_id,cust_brand_id,sum(CASEWHENSMS_SVC_Type_Level_SECND='017'andCall_Type_Codein('00','10','01','11')THENsms_quan语音杂志计费量

WHENSMS_SVC_Type_Level_SECNDlike'02%'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,3SQL优化重写用CaseWhen替代UNION(cont.)selc用OR替代UNIONSelectcity_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='20070914‘andSMS_SVC_Type_Level_SECND='017'andCall_Type_Codein('00','10','01','11')groupby1,2,3

unionallselectt.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='20070914'andSMS_SVC_Type_Level_SECNDlike'02%'andSMS_SVC_Type_Level_SECNDnotin('021','022')groupby1,2,3)TGroupby1,2,3两个子查询的表连接部分完全一样两个子查询除了取数据条件，其它都一样。Unionall是多余的，它需要重复扫描数据，进行重复的JOIN可以用OR替代union此类的问题，在脚本中经常见到。用OR替代UNIONSelectcity_id,ch用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='20070914‘and

（（SMS_SVC_Type_Level_SECND='017'语音杂志计费量andCall_Type_Codein(‘00’,‘10’,‘01’,‘11’)）

OR(SMS_SVC_Type_Level_SECNDlike'02%'梦网短信计费量

andSMS_SVC_Type_Level_SECNDnotin('021','022')))Groupby1,2,3SQL优化重写用OR替代UNION(cont.)selectt.cit去掉多余的Distinct与Groupbyselt.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

='20071007'

andso_meth_codein('0','1','2')andPROC_STS_Code='-1'groupby1,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

='20071007'

groupby1,2,3,4,5)tgroupby1,2,3,4,5;既然t查询外层有groupby操作去重，那么子查询内的Groupby去重是多余的。而且，两个子查询groupby后再用unionall，就可能再产生重复记录，那么groupby也失去意义了。解决方法：

把t查询内部的两个groupby去掉即可类似的Distinct问题，可效仿解决。去重去重去重去掉多余的Distinct与Groupbyselt.oGroupbyvs.DistinctDistinct是去除重复的操作Groupby是聚集操作某些情况下，两者可以起到相同的作用。两者的执行计划不一样，效率也不一样建议：使用Groupbyselectsubs_id,acct_idfromPVIEW.VW_FIN_ACCT_SUBS_HISwhereefct_date<='20070701'andend_date>'20070701'

groupby1,2selectDISTINCT

subs_id,acct_idfromPVIEW.VW_FIN_ACCT_SUBS_HISwhereefct_date<='20070701'andend_date>'20070701'

Groupbyvs.DistinctDistinctUnionvs.UnionallUnion与Unionall的作用是将多个SQL的结果进行合并。Union将自动剔除集合操作中的重复记录；需要耗更多资源。Unionall则保留重复记录，一般建议使用Unionall。第一个SELECT语句，决定输出的字段名称，标题，格式等要求所有的SELECT语句：1)必须要有同样多的表达式数目；2)相关表达式的域必须兼容select*from(select'a')T1(col1)unionselect*from(select'bc')T2(col2)select*from(select'bc')T3(col3)unionallselect*from(select'a')T1(col1)unionallselect*from(select'bc')T2(col2)col3'a‘‘bc‘‘bc'col1'a‘‘b'Unionvs.UnionallUnion与Unio先Groupby再join脚本：rpt_mart_new_comm_mon0400.pl11小时Selectcasewhenb.CUST_Brand_IDisnullthen'5020'whenb.CUST_Brand_IDin('2000','5010')then'5020'elseb.CUST_Brand_IDend,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0))asThsy_Accum_New_SUBS_CHRG_DUR,sum(casewhenb.call_type_code='20'thenb.Bas_CHRG_DUR_Unitelse0END)

fromVTNEW_SUBS_THISYEARtinnerjoinVTDUR_MONbont.Subs_ID=b.Subs_IDleftjoin

PVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVLconb.Long_Type_Level_SECND=c.Long_Type_Level_SECNDleftjoin

PVIEW.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脚本：rpt_mart_new_先Groupby再join(cont.)脚本：rpt_mart_new_comm_mon0400.pl40秒Selectcasewhenb.CUST_Brand_IDisnullthen'5020'whenb.CUST_Brand_IDin('2000','5010')then'5020'elseb.CUST_Brand_IDend,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0))asThsy_Accum_New_SUBS_CHRG_DUR,sum(casewhenb.call_type_code='20'thenb.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)bleftjoin

PVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVLconb.Long_Type_Level_SECND=c.Long_Type_Level_SECNDleftjoin

PVIEW.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，就是：

332×8×84、最终结果：5记录，共40秒先Groupby再join(cont.)脚本：rpt_m先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_date

ANDDT.quarter='3'GROUPBY1;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;先Groupby再join（cont.）先汇总再连接，可以提取公共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_MON

whereCAL_Month=200708提取公共SQL，形成临时表，较少扫描(IO)次数。该脚本，经过优化之后，从50分钟缩减至10分钟提取公共SQL形成临时表脚本：rpt_nmmart_comm关联条件(1)SelectA.a2,B.b2fromAjoinBonsubstring(A.a1from1for7)=B.b1应该写为SelectA.a2,B.b2from(selectsubstring(a1from1for7)asa1_new ,a2fromA)A_newjoinBona1_new=b1关联条件(1)SelectA.a2,B.b2关联条件(2)SelectA.a2,B.b2fromAjoinBonTRIM(A.a1)=TRIM(B.b1)应该写为SelectA.a2,B.b2fromAjoinBonA.a1=B.b1关联条件(2)SelectA.a2,B.b2SQL书写不当可能会引起笛卡儿积以下面两个SQL为例，它们将进行笛卡儿积操作。例子1：Selectemployee.emp_no,employee.emp_nameFromemployeeA例子2：SELECTA.EMP_Name,B.Dept_NameFROMemployeeA,DepartmentBWherea.dept_no=b.dept_no;表Employee与表A进行笛卡儿积表A与表B进行笛卡儿积表A与表B进行InnerJoinSQL书写不当可能会引起笛卡儿积以下面两个SQL为例，它们将修改表定义常见的表定义修改操作：增加字段修改字段长度建议的操作流程Renametabledb.tablexasdb.tabley;通过Showtable语句获得原表db.tablex的定义定义新表：db.tablexInsertintodb.tablex(。。。)select。。。Fromdb.tabley;Droptabledb.tabley;Teradata提供ALTERTABLE语句，可进行修改表定义但，不建议采用ALTERTABLE方式。修改表定义常见的表定义修改操作：插入/更新/删除记录时，尽量不要Abort当目标表有数据时，插入和更新操作，以及部分删除，都产生TJ如果此时abort该操作，系统将会回滚DeleteBASS1.tb_03004

whereproc_dt='200709‘

;UPDATECustomerSETCredit_Limit=Credit_Limit*1.20;

DELETEFROMTrans WHERETrans_Date<990101;插入/更新/删除记录时，尽量不要Abort当目标表有数据时，Update/Delete操作UPDATECustomerSETCredit_Limit=Credit_Limit*1.20;

CREATEmultisetTABLECustomer_NASCustomerwithnodata;INSERTINTOCustomer_N SELECTCredit_Limit*1.20FROMCustomer;DROPTABLECustomer;RENAMETABLECustomer_NTOCustomer;CREATEmultisetTABLETrans_NasTranswithnodata;INSERTINTOTrans_N SELECT*FROMTransWHERETrans_Date>981231;DROPTABLETrans;RENAMETABLETrans_NTOTrans;先建立空表，通过insert/select方式插入数据－－这是非常快的操作！先备份，然后做变更操作，更加安全！对于大表进行Update/DELETE操作，将耗费相当多的资源与相当长的时间。Update/Delete操作，需要事务日志TJ（TransientJournal）以防意外中断导致数据受到破坏在Update/Delete操作中途被Cancel，系统则需回滚，这将耗更多的资源与时间！在经分系统中，应严防此类事件发生！DELETEFROMTrans WHERETrans_Date<990101;Update/Delete操作UPDATECustomer经分系统的实体命名规范实体的命名，最长不超过30个字母；通常要求都是大写。实体的命名：<前缀>_<主体>_后缀前缀：表：基础表以TB_开头中间表以MID_开头应用模块的表以相应的主体缩写开头视图：一般地，视图名称与表名称一一对应。以VW_开头。对于TB_开头的表，把TB_替换成VW；对于其他表，加上VW_即可。宏：以M_或者Macro_开头后缀：历史表：_HIS月表：_MON日表：_DAILY经分系统的实体命名规范实体的命名，最长不超过30个字母；通常实体的命名规范－－示例TB_OFR_SUBS_HIS 用户历史Efct_date,End_date示例：Select*Frompview.vw_ofr_subs_hisWhereefct_date<=cast(‘20080401’asdateformat‘yyyymmdd’)andend_date>cast(‘20080401’asdateformat‘yyyymmdd’)MID_COMP_OPPN_DISTRICT_MON区域管理月中间表RPT_CHK_FREE_RES_LOS_DAILY免费资源促销资料丢失用户表VW_MID_SUBS_INFO_MON视图：用户资料月中间表实体的命名规范－－示例TB_OFR_SUBS_HIS提纲Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化提纲Teradata扩展SQL(1)–SHOWSHOWTABLE pdata.tb_net_gsm_nl;显示表pdata.tb_net_gsm_nl的定义Teradata扩展SQL(1)–SHOWSHOWTABTeradata扩展SQL(2)–HELPHELPDATABASE MIS;

列举数据库MIS的内容

HELPTABLEpdata.tb_net_gsm_nl;列举该表的字段Teradata扩展SQL(2)–HELPHELPTeradata扩展SQL(3)--MACRO宏（MACRO）是Teradata对ANSISQL的扩展。宏（MACRO）是一组SQL的集合SQL之间用分号”；”隔开没有ifthenelse语句与存储过程（StoreProcedure）不一样：存储过程类似于C语言，需要先编译，才能执行；而宏不需要。Teradata扩展SQL(3)--MACRO宏（MACTeradata扩展SQL(4)–

临时表可变临时表（VolatileTable）是一种比较常用的Teradata临时表一般用它来存储公共部分的数据，以提高程序的执行效率。它仅存活于同一个Session之内INSERTINTOTarget_tableSelect*From(selproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='02‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='03unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='04‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='14‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='15‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='16‘unionselproc_dttx_date,kpi_code,kpi_valuefrompview.vw_kpi_daywherekpi_code='22‘)T1Wheretx_date=‘20070701’需要访问表pview.vw_kpi_day

7

次，效率低下！Teradata扩展SQL(4)–临时表可变临时表（VoTeradata扩展SQL(4)–

临时表Createvolatilemultisettablevt_kpi_dayas( Selectproc_dttx_date,kpi_code,kpi_value frompview.vw_kpi_day wheretx_date=‘20070701’)WITHDATAPrimaryIndex(kpi_code)ONCOMMITPRESERVEROWS;INSERTINTOTarget_tableSelect*From( selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='02‘union selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='03’union selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='04‘union selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='14‘union selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='15‘union selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='16‘union selproc_dttx_date,kpi_code,kpi_valuefromvt_kpi_daywherekpi_code='22‘)T1只需要访问表pview.vw_kpi_day1次，效率提高！Teradata扩展SQL(4)–临时表CreatevSQL变量SELECTDATABASE;显示当前数据库PVIEWSELECTUSER;显示当前Session登陆的用户名luscSELECTDATE,CURRENT_DATE;显示当前日期20070806,20070806定义格式:SELECTCAST(DATEASDATEFORMAT'YYYYMMDD')SelectTIME,CURRENT_TIMESTAMP(0);显示当前时间18:46:35,2007-08-0618:46:34+00:00转换:SELECTCAST(CURRENT_TIMESTAMP(0)ASCHAR(19));2007-08-0618:47:59SQL变量SELECTDATABASE;显示当前数据库日期(DATE)的操作取当前天：selectcast(current_dateasDATEFORMAT'YYYYMMDD')取当前天的前一天，后一天selectcast(current_date-1asDATEFORMAT'YYYYMMDD')selectcast(current_date+1asDATEFORMAT'YYYYMMDD')取前(后)一个月的同一天Selectadd_months(current_date,-1)Selectadd_months(current_date,1)若current_date为20070331，结果是什么？取当前天所在月的第一天selectsubstr(cast(current_dateasdateformat'YYYYMMDD'),1,6)||'01';取当前天所在月的最后一天selectcast(substr(cast(add_months(current_date,1)asdateformat'YYYYMMDD'),1,6)||'01‘asdateformat'YYYYMMDD')-1日期相减SELECT(DATE'2007-03-01'-DATE'2004-01-01')day(4);SELECT(DATE'2007-03-01'-DATE'2004-01-01')month(4);日期(DATE)的操作取当前天：日