实用教程(Teradata).ppt

《实用教程(Teradata).ppt》由会员分享，可在线阅读，更多相关《实用教程(Teradata).ppt（81页珍藏版）》请在三一办公上搜索。

1、实用教程（Teradata）,陆世潮2008年9月,问题总结,常见问题分类：表属性不对:Set/Multiset问题：INSERT操作慢主索引(PI)设置不合理问题1：数据倾斜度大，空间爆满。问题2：JOIN操作，数据需要重分布。分区索引(PPI)设置不合理问题：全表扫描连接条件过于复杂问题：系统无法优化执行计划缺乏统计信息问题：系统无法找到最优化的执行计划,SQL跑得慢哈！,提纲,Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化,Teradata 体系架构,Teradata and MPP Systems,RDBMS ARCH,Logic

2、al Example of NPPI versus PPI,提纲,Teradata架构常见问题，及解决方法Teradata工具实用小技巧JOIN的实现机制JOIN的优化,表属性：Set&MultiSet,Set Table不允许记录重复MultiSet Table允许记录重复默认值:Set TableCreate Table.AS.生成的目标表属性默认为Set Table对SET Table进行INSERT操作，需要检查是否存在重复记录相当的耗资源若真要限定唯一性，可以通过UPI或USI实现,CREATE SET TABLE pmart.RPT_NM_GRP_PRE_WARN_MON,(CAL

3、_Month INTEGER TITLE 统计月份,ORG_NUM CHAR(12)TITLE 集团编号,City_ID CHAR(3)TITLE 地市标识,ORG_SUBS_GRP_NUM CHAR(10)TITLE 集团用户群编号,ORG_Title VARCHAR(200)TITLE 集团名称,ORG_Level CHAR(2)TITLE 集团级别,STAT_Item_Code CHAR(2)TITLE 统计项,STAT_Value DECIMAL(18,2)TITLE 统计值)PRIMARY INDEX(ORG_NUM);,例子：pmart.RPT_NM_GRP_PRE_WARN_MO

4、N 内蒙移动集团客户预警指标月报表,假设原有1286449条记录插入:152853条记录耗时：15秒,表属性：Set&MultiSet（cont.）,CREATE MULTISET TABLE pmart.RPT_NM_GRP_PRE_WARN_MON(CAL_Month INTEGER TITLE 统计月份,ORG_NUM CHAR(12)TITLE 集团编号,City_ID CHAR(3)TITLE 地市标识,ORG_SUBS_GRP_NUM CHAR(10)TITLE 集团用户群编号,ORG_Title VARCHAR(200)TITLE 集团名称,ORG_Level CHAR(2)TI

5、TLE 集团级别,STAT_Item_Code CHAR(2)TITLE 统计项,STAT_Value DECIMAL(18,2)TITLE 统计值)PRIMARY INDEX(ORG_NUM);,例子：pmart.RPT_NM_GRP_PRE_WARN_MON 内蒙移动集团客户预警指标月报表,建议：Teradata中都用 MultiSet,假设原有1286449条记录插入:152853条记录耗时：1秒,例子：CREATE MULTISET TABLE tttemp.VT_SUBS_VIOC_QUAN as(SELECT*FROM tttemp.MID_SUBS_VIOC_QUAN WHERE

6、 CAL_MONTH=200802 AND*)WITH DATA PRIMARY INDEX(subs_id);,临时表,默认为:Set需要指定为:Multiset,字段越多，记录越多差别越明显,PI（Primary Index 主索引）的选择,PI影响数据的存储与访问，其选择标准：不同值尽量多的字段(More Unique Values)使用频繁的字段:包括值访问和连接访问少更新PI字段不宜太多最好是手动指定PI,例子：用户语音业务量中间表CREATE MULTISET TABLE tttemp.MID_SUBS_VIOC_QUAN(CAL_Month INTEGER TITLE 统计月份,

7、City_ID CHAR(4)TITLE 地市标识,Channel_ID CHAR(8)TITLE 渠道标识,Subs_id CHAR(12)TITLE 用户标识,。)PRIMARY INDEX(subs_id);,例子：用户语音业务量临时表CREATE MULTISET TABLE tttemp.VT_SUBS_VIOC_QUAN as(SELECT*FROM tttemp.MID_SUBS_VIOC_QUAN WHERE CAL_MONTH=200802 AND*)WITH DATA PRIMARY INDEX(subs_id);,Subs_ID:频繁使用 Unique Value多,如果

8、不指定PI，系统默认为:Cal_Month,PI（Primary Index 主索引）的选择(cont.),例子：梦网客户活跃客户分析CREATE MULTISET TABLE PMART.FCT_DATA_MONNET_ACTIVE_MON(CAL_Month INTEGER TITLE 统计月份,City_ID CHAR(4)TITLE 地市标识,Channel_ID CHAR(8)TITLE 渠道标识,Mont_SVC_Type_Cod CHAR(3)TITLE 梦网业务类型编码,Mont_SVC_CAT_MicroCls_Cod CHAR(3)TITLE 梦网业务分类小类编码,Mont

9、_SVC_CHRG_Type_Cod CHAR(2)TITLE 梦网业务计费类型编码,THR_Brand_Cod CHAR(1)TITLE 三大品牌编码,Mont_Consume_Level_Cod CHAR(2)TITLE 梦网消费层次编码,Consume_Level_Cod CHAR(2)TITLE 消费层次编码,。)PRIMARY INDEX(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_L

10、evel_Cod,Consume_Level_Cod);PI：9字段 2字段：City_ID,Channel_ID 调整PI后，在右边的SQL中，PI是否起作用？,以下SQL，PI是否起作用？：1.值访问Select*From FCT_DATA_MONNET_ACTIVE_MONWhere City_ID=070010 and Channel_ID=0100 and cal_month=2007072.连接访问Select*From FCT_DATA_MONNET_ACTIVE_MON ALEFT JOIN MID_CHANNEL_INFO_DAILY B ON A.Channel_ID=B.

11、Channel_ID and A.City_ID=b.City_ID LEFT JOIN VW_CDE_REGION_TYPE C ON A.City_ID=C.City_ID 3、值访问连接访问Select*From FCT_DATA_MONNET_ACTIVE_MON A,VT_INFO BWHERE A.Channel_ID=B.Channel_ID AND A.City_ID=B.City_ID AND A.CAL_MONTH=200707 AND A.Consume_Level_Cod=B.Consume_Level_Cod,PPI的使用,PPI（Partition Primary

12、Index，分区索引），把具有相同分区值的数据聚簇存放在一起；类似于SQL Server的聚簇索引（Cluster Index），Oracle的聚簇表（Cluster Table）。利用PPI，可以快速插入/访问同一个Partition（分区）的数据。,CREATE MULTISET TABLE qdata.TB_DQC_KPI_CHECK_RESULT(TX_DATE DATE FORMAT YYYYMMDD TITLE 数据日期 NOT NULL,KPI_CODE INTEGER TITLE 指标代码 NOT NULL,。)PRIMARY INDEX(KPI_CODE)PARTITION

13、BY RANGE_N(TX_DATE BETWEEN CAST(20030101)AS DATE FORMAT YYYYMMDD)AND CAST(20191231)AS DATE FORMAT YYYYMMDD)EACH INTERVAL 1 DAY,NO RANGE OR UNKNOWN);,Select*From TB_DQC_KPI_CHECK_RESULTWhere tx_date=20070701;或Where tx_date between 20070701 and 20070731;或Where tx_date 20070701;但Where tx_date like 2007

14、07%;不起作用,PPI的使用（cont.）,Partition上不要使用表达式，否则Partition不能被正确使用。T1.tx_date/100=CAST(20070917AS DATE FORMAT YYYYMMDD)/100Substring(T1.tx_date from 1 for 6)=200709应该修改为 T1.tx_date=CAST(20070901 AS DATE FORMAT YYYYMMDD),PPI的使用（cont.）,脚本：tb_030040270.pl/*删除当月*/2小时 del BASS1.tb_03004 where proc_dt=200709;ins

15、ert into BASS1.tb_03004 7小时。,sel.from pview.vw_evt_cust_so cust where acpt_date=cast(200710|01 as date)cast(200710|01 as date)写法错误，PPI不起作用日期的正确写法:Cast(20071001 as date format YYYYMMDD),在proc_dt建立PPI,PPI字段从Load_Date调整为acpt_date,创建可变临时表,它仅存活于同一个Session之内注意指定可变临时表为multiset（通常也要指定PI）可变临时表不能带有PPI例子1:crea

16、te volatile multiset table vt_RETAIN_ANLY_MON as(select col1,col2,from where group by.）with data PRIMARY INDEX(PI_Cols)ON COMMIT PRESERVE ROWS;例子2:create volatile multiset table vt_RETAIN_ANLY_MON(col1 char(2),col2 varchar(12)NOT NULL）PRIMARY INDEX(PI_Cols)ON COMMIT PRESERVE ROWS;,创建可变临时表（cont.）,例子3

17、:create volatile multiset table vt_RETAIN_ANLY_MON as(select col1,cast(adc as varchar(12)col2 from where）with no data PRIMARY INDEX(col1)ON COMMIT PRESERVE ROWS;例子4:create volatile multiset table vt_net_gsm_nl as pdata.tb_net_gsm_nl with no data ON COMMIT PRESERVE ROWS;,字段col2将用unicode字符集；当跟普通字段（lat

18、in字符集）join时，需要进行数据重新分布。不建议,失败：因为pdata.tb_net_gsm_nl 有PPI而可变临时表不允许有PPI,固化临时表,固化临时表，就是把查询结果存放到一张物理表。共下次分析或他人使用Session断开之后，仍然可以使用。示例1：CREATE MULTISET TABLE tttemp.TMP_BOSS_VOIC as（select*from pview.vw_net_gsm_nl)WITH no DATA PRIMARY INDEX(subs_id);INSERT INTO tttemp.TMP_BOSS_VOICSELECT*FROM pview.vw_ne

19、t_gsm_nl WHERE*;示例2：CREATE MULTISET TABLE tttemp.TMP_BOSS_VOIC as（select*from pview.vw_net_gsm_nl WHERE*)WITH DATA PRIMARY INDEX(subs_id);示例3：(复制表，数据备份）CREATE MULTISET TABLE tttemp.TMP_BOSS_VOIC AS pdata.tb_net_gsm_nl WITH DATA;,数据类型,注意非日期字段与日期字段char&date的转换与关联：如果数据类型一致可以直接使用；在CASE WHEN or COALESCE一

20、定要使用显式的类型转换(CAST)CASE WHEN A=B THEN DATE1 ELSE 20061031 END应写成CASE WHEN A=B THEN DATE1 ELSE CAST(20061031 AS DATE)END数值运算时，确保运算过程中不丢失计算精度。CAST(100/3 AS DEC(5,2)应该写成CAST(100/3.00 AS DEC(5,2),字符(串)与数字相比较,比较规则:1)比较两个值(字段)，它们的类型必须一样！2)当字符(串)与数字相比较时，先把字符(串)转换成数字，再进行比较。3)经分系统中容易出错的，有Cal_Month字段,Case 1,Tab

21、le 1CREATE TABLE Emp1(Emp_noCHAR(6),Emp_nameCHAR(20)PRIMARY INDEX(Emp_no);,Statement 1SELECT*FROMEmp1WHEREEmp_no=1234;,Statement 2SELECT*FROMEmp1WHEREEmp_no=1234;,Table 1CREATE TABLE Emp2(Emp_noINTEGER,Emp_nameCHAR(20)PRIMARY INDEX(Emp_no);,Statement 1SELECT*FROMEmp2WHEREEmp_no=1234;,Statement 2SELE

22、CT*FROMEmp2WHEREEmp_no=1234;,Case 2,Results in Full Table Scan,Results in unnecessary conversion,目标列的选择,减少目标列，可以少消耗SPOOL空间，从而提高SQL的效率当系统任务繁忙，系统内存少的时候，效果尤为明显。举例：GSM语言话单表，PDATA.TB_NET_GSM_NL共有73字段，以下SQL供返回1.6亿条记录左边的SQL，记录最长为：698字节，平均399字节右边的SQL，记录最长为：59字节，平均30字节两者相差400多GB的SPOOL空间，IO次数也随着相差甚大！,SPOOL空间估

23、计：497 GB,SPOOL空间估计：42 GB,SELECT SUBS_ID,MSISDN,Begin_Date,Begin_Time,Call_DUR,CHRG_DURFROM PDATA.TB_NET_GSM_NLWHERE PROC_DATE BETWEEN 20070701 AND 20070731,SELECT*FROM PDATA.TB_NET_GSM_NLWHERE PROC_DATE BETWEEN 20070701 AND 20070731,Where条件的限定,根据Where条件先进行过滤数据集，再进行连接(JOIN)等操作这样，可以减少参与连接操作的数据集大小，从而提高

24、效率好的查询引擎，可以自动优化；但有些复杂SQL，查询引擎优化得并不好。注意：系统的SQL优化，只是避免最差的，选择相对优的，未必能够得到最好的优化结果。,SELECT A.TX_DATE,A.KPI_CODE,B.SRC_NAME,A.KPI_VALUEFROM(select*from qdata.tb_dqc_kpi_check_result where TX_DATE=20070701 AND KPI_CODE=65)ALEFT JOIN(SELECT*FROM qdata.tb_dqc_kpi_def where KPI_CODE=65 and N_TYPE=M)BON A.KPI_C

25、ODE=B.KPI_CODE,SELECT A.TX_DATE,A.KPI_CODE,coalesce(B.SRC_NAME,no name),A.KPI_VALUEFROM qdata.tb_dqc_kpi_check_result ALEFT JOIN qdata.tb_dqc_kpi_def BON A.KPI_CODE=B.KPI_CODE WHERE A.TX_DATE=20070701 AND A.KPI_CODE=65 AND B.N_TYPE=M,rewrite,用Case When替代UNION,sel city_id,channel_id,cust_brand_id,sum

26、(stat_values)as stat_valuesfrom(.select t.city_id-语音杂志计费量,coalesce(v.channel_id,b.channel_id,-)as channel_id,cust_brand_id,sum(case when SMS_SVC_Type_Level_SECND=017 and Call_Type_Code in(00,10,01,11)then sms_quan else 0 END)as stat_values from PVIEW.vw_mid_sms_svc_quan_daily t left join VT_SUBS v o

27、n t.subs_id=v.subs_id left join PVIEW.vw_FCT_CDE_BUSN_CITY_TYPE b on t.city_id=b.City_ID where cal_date=20070914 group by 1,2,3 union all select t.city_id-梦网短信计费量,coalesce(v.channel_id,b.channel_id,-)as channel_id,cust_brand_id,sum(sms_quan)as stat_values from PVIEW.vw_mid_sms_svc_quan_daily t left

28、join VT_SUBS v on t.subs_id=v.subs_id left join PVIEW.vw_FCT_CDE_BUSN_CITY_TYPE b on t.city_id=b.City_ID where cal_date=20070914 and SMS_SVC_Type_Level_SECND like 02%and SMS_SVC_Type_Level_SECND not in(021,022)group by 1,2,3.)tmpGroup by 1,2,3,两个子查询的表连接部分完全一样两个子查询除了取数据条件，其它都一样。Union all是多余的，它需要重复扫描数

29、据，进行重复的JOIN可以用Case when替代union,作业:KPI_NWR_SMS_BILL_QUAN描述:点对点短信计费量脚本:kpi_nwr_sms_bill_quan0600.pl,用Case When替代UNION(cont.),sel city_id,channel_id,cust_brand_id,sum(stat_values)as stat_valuesfrom(select t.city_id,coalesce(v.channel_id,b.channel_id,-)as channel_id,cust_brand_id,sum(CASE WHEN SMS_SVC_T

30、ype_Level_SECND=017 and Call_Type_Code in(00,10,01,11)THEN sms_quan-语音杂志计费量 WHEN SMS_SVC_Type_Level_SECND like 02%and SMS_SVC_Type_Level_SECND not in(021,022)THEN sms_quan-梦网短信计费量 ELSE 0 END)as stat_values from PVIEW.vw_mid_sms_svc_quan_daily t left join VT_SUBS v on t.subs_id=v.subs_id left join PV

31、IEW.vw_FCT_CDE_BUSN_CITY_TYPE b on t.city_id=b.City_ID where cal_date=20070914.)tmpGroup by 1,2,3,SQL优化重写,用OR替代UNION,Select city_id,channel_id,cust_brand_id,sum(sms_quan)stat_valuesfrom(select t.city_id-语音杂志计费量,coalesce(v.channel_id,b.channel_id,-)as channel_id,cust_brand_id,sum(sms_quan)stat_values

32、 from PVIEW.vw_mid_sms_svc_quan_daily t left join VT_SUBS v on t.subs_id=v.subs_id left join PVIEW.vw_FCT_CDE_BUSN_CITY_TYPE b on t.city_id=b.City_ID where cal_date=20070914 and SMS_SVC_Type_Level_SECND=017 and Call_Type_Code in(00,10,01,11)group by 1,2,3 union all select t.city_id-梦网短信计费量,coalesce(

33、v.channel_id,b.channel_id,-)as channel_id,cust_brand_id,sum(sms_quan)as stat_values from PVIEW.vw_mid_sms_svc_quan_daily t left join VT_SUBS v on t.subs_id=v.subs_id left join PVIEW.vw_FCT_CDE_BUSN_CITY_TYPE b on t.city_id=b.City_ID where cal_date=20070914 and SMS_SVC_Type_Level_SECND like 02%and SM

34、S_SVC_Type_Level_SECND not in(021,022)group by 1,2,3)T Group by 1,2,3,两个子查询的表连接部分完全一样两个子查询除了取数据条件，其它都一样。Union all是多余的，它需要重复扫描数据，进行重复的JOIN可以用OR替代union 此类的问题，在脚本中经常见到。,用OR替代UNION(cont.),select t.city_id,coalesce(v.channel_id,b.channel_id,-)as channel_id,cust_brand_id,sum(sms_quan)as stat_values from P

35、VIEW.vw_mid_sms_svc_quan_daily t left join VT_SUBS v on t.subs_id=v.subs_id left join PVIEW.vw_FCT_CDE_BUSN_CITY_TYPE b on t.city_id=b.City_ID where cal_date=20070914 and（SMS_SVC_Type_Level_SECND=017-语音杂志计费量 and Call_Type_Code in(00,10,01,11)）OR(SMS_SVC_Type_Level_SECND like 02%-梦网短信计费量 and SMS_SVC_

36、Type_Level_SECND not in(021,022)Group by 1,2,3,SQL优化重写,去掉多余的Distinct与Group by,sel t.operator,t.acpt_channel_id,t.acpt_city_id,t.subs_id,t.acpt_date as evt_date From(sel operator,ACPT_Channel_ID,acpt_city_id,subs_id,acpt_date from pview.vw_evt_cust_so cust where acpt_date=20071007 and so_meth_code in

37、(0,1,2)and PROC_STS_Code=-1 group by 1,2,3,4,5union all sel operator_num as operator,ACPT_Channel_ID,acpt_city_id,subs.subs_id,charge_date as acpt_date from pview.vw_fin_busi_rec bus join crmmart.subs_day_info_daily subs on subs.msisdn=bus.msisdn where charge_date=20071007 group by 1,2,3,4,5)t group

38、 by 1,2,3,4,5;,既然t查询外层有group by操作去重，那么子查询内的Group by去重是多余的。而且，两个子查询group by后再用union all，就可能再产生重复记录，那么group by也失去意义了。解决方法：把t查询内部的两个group by去掉即可 类似的Distinct问题，可效仿解决。,去重,去重,去重,Group by vs.Distinct,Distinct是去除重复的操作Group by是聚集操作某些情况下，两者可以起到相同的作用。两者的执行计划不一样，效率也不一样建议：使用Group by,select subs_id,acct_idfrom PV

39、IEW.VW_FIN_ACCT_SUBS_HISwhere efct_date 20070701 group by 1,2,select DISTINCT subs_id,acct_idfrom PVIEW.VW_FIN_ACCT_SUBS_HISwhere efct_date 20070701,Union vs.Union all,Union与Union all的作用是将多个SQL的结果进行合并。Union将自动剔除集合操作中的重复记录；需要耗更多资源。Union all则保留重复记录，一般建议使用Union all。第一个SELECT语句，决定输出的字段名称，标题，格式等要求所有的SELE

40、CT语句：1)必须要有同样多的表达式数目；2)相关表达式的域必须兼容,select*from(select a)T1(col1)unionselect*from(select bc)T2(col2),select*from(select bc)T3(col3)union allselect*from(select a)T1(col1)union allselect*from(select bc)T2(col2),col3-abcbc,col1-ab,先Group by再join,脚本：rpt_mart_new_comm_mon0400.pl 11小时Select case when b.CUS

41、T_Brand_ID is null then 5020 when b.CUST_Brand_ID in(2000,5010)then 5020 else b.CUST_Brand_ID end,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0)as Thsy_Accum_New_SUBS_CHRG_DUR,sum(case when b.call_type_code=20 then b.Bas_CHRG_DUR_Unit else 0 END)from VTNEW_SUBS_THISYEAR t inner join VTDUR_MON b on t.Subs_ID=b.

42、Subs_ID left join PVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVL c on b.Long_Type_Level_SECND=c.Long_Type_Level_SECND left join PVIEW.vw_MID_CDE_ROAM_TYPE_LVL d on b.Roam_Type_Level_SECND=d.Roam_Type_Level_SECND group by 1;,记录数情况：t:580万，b:9400万,c:8,d:8 主要问题：假如连接顺序为：(b join c)join d)join t)则是(9400万 join 8)join

43、8)join 580万)数据分布时间长(IO多)，连接次数多解决方法：先执行(t join b)，然后groupby，再join c，d,先Group by再join(cont.),脚本：rpt_mart_new_comm_mon0400.pl40秒Select case when b.CUST_Brand_ID is null then 5020 when b.CUST_Brand_ID in(2000,5010)then 5020 else b.CUST_Brand_ID end,sum(COALESCE(b.Bas_CHRG_DUR_Unit,0)as Thsy_Accum_New_SU

44、BS_CHRG_DUR,sum(case when b.call_type_code=20 then b.Bas_CHRG_DUR_Unit else 0 END)from(select CUST_Brand_ID,call_type_code,Long_Type_Level_SECND,Roam_Type_Level_SECND,sum(Bas_CHRG_DUR_Unit)Bas_CHRG_DUR_Unit,count(*)quan from VTDUR_MON where subs_id in(select subs_id from VTNEW_SUBS_THISYEAR)group by

45、 1,2,3,4)b left join PVIEW.vw_MID_CDE_LONG_CALL_TYPE_LVL c on b.Long_Type_Level_SECND=c.Long_Type_Level_SECND left join PVIEW.vw_MID_CDE_ROAM_TYPE_LVL d on b.Roam_Type_Level_SECND=d.Roam_Type_Level_SECND group by 1;,记录数情况：t:580万，b:9400万,c:8,d:8 处理过程：先执行(t join b)，然后groupby，再join c，d结果：1、VTDUR_MON jo

46、in VTNEW_SUBS_THISYEAR PI相同，merge join，只需10秒2、经过group by，b表只有332记录3、b join c join d，就是：332 8 84、最终结果：5记录，共40秒,先Group by再join（cont.）,先汇总再连接，可以减少参与连接的数据集大小，减少比较次数，从而提高效率。以下面SQL为例，假设历史表（History）有1亿条记录左边的SQL，需要进行 1亿 90次比较右边的SQL，则只需要 1亿 1 次比较,SELECT H.product_id,sum(H.account_num)FROM History H,Calendar

47、DTWHERE H.sale_date=DT.calendar_date AND DT.quarter=3 GROUP BY 1;,SELECT H.product_id,SUM(H.account_num)FROM History H,(SELECT min(calendar_date)min_date,max(calendar_date)max_date FROM Calendar WHERE quarter=3)DT WHERE H.sale_date BETWEEN DT.min_date and DT.max_dateGROUP BY 1;,提取公共SQL形成临时表,脚本：rpt_n

48、mmart_comm_subs_mon0403.pl出现以下SQL代码段，共5次，平均每次执行需10分钟。FROM PVIEW.VW_MID_VOIC_SVC_QUAN_MON a,PVIEW.VW_MID_CDE_SUBS_BRAND_LVL b,vt_subs c WHERE a.CUST_Brand_ID=b.SUBS_Brand_Level_Third AND a.CAL_Month=200708 AND a.SUBS_ID=c.SUBS_ID。整个脚本需要扫描以下SQL 14次，平均每次执行需3分钟PVIEW.VW_MID_VOIC_SVC_QUAN_MON where CAL_Mo

49、nth=200708提取公共SQL，形成临时表，较少扫描(IO)次数。该脚本，经过优化之后，从50分钟缩减至10分钟,关联条件(1),Select A.a2,B.b2 from A join Bon substring(A.a1 from 1 for 7)=B.b1应该写为Select A.a2,B.b2 from(select substring(a1 from 1 for 7)as a1_new,a2 from A)A_newjoin Bon a1_new=b1,关联条件(2),Select A.a2,B.b2 from A join Bon TRIM(A.a1)=TRIM(B.b1)应该

50、写为Select A.a2,B.b2 from A join Bon A.a1=B.b1,SQL书写不当可能会引起笛卡儿积,以下面两个SQL为例，它们将进行笛卡儿积操作。例子1：Select employee.emp_no,employee.emp_nameFrom employee A例子2：SELECT A.EMP_Name,B.Dept_NameFROM employee A,Department BWhere a.dept_no=b.dept_no;,修改表定义,常见的表定义修改操作：增加字段修改字段长度建议的操作流程Rename table db.tablex as db.table