Teradata 基本查询语言和SQL server 是一致的。有很小的区别。
功能没有SQL 全面,界面没有SQL 好看~
1. teradata 里面经常会报一种错误: no enough spool space
这种错误一般都是query 需要优化,需要简化;
2. 常用的语句优化之一(SQL 里面也经常用到)
Try to use the filter when joining instead of calculater with CASE WHEN
例子:
1) Requirement:
Left Outer Join ws to ph ON ws.IID = ph.IID
WHERE ph.PRIMARY_INDIVIDUAL_PHONE_FLG = ‘Y‘ to obtain the phone.
Left Outer Join ws to em ON ws.IID = em.IID
WHERE ph.PRIMARY_INDIVIDUAL_EMAIL_FLG = ‘Y‘ to obtain the email address.
2) 分析:
where 条件是left join的右表。
所以用case when 或者 join on and 都可以
3)性能比较:
case when
run了60 min后报错,提示no enough spool space,就是需要简化!!语句跑太多资源了!
join on and
仅仅跑了4min11s,大大的提高!