SQL Performance tuning in Teradata

為DB進行collect statistics時,除了根據過往的經驗外,在Teradata中其實是可以讓個engine 給點提議的

要讓engine 為你提供要collect的選擇,首先需要執行以下statement

DIAGNOSTIC HELPSTATS ON FOR SESSION;

之後再EXPLAIN 一下要行的sql

EXPLAIN SEL * FROM tableA a INNER JOIN tableB b ON a.ref_cd = b.ref_cd 
LEFT OUTER JOIN tableC c ON a.serv_cd = c.serv_cd
LEFT OUTER JOIN tableD d ON a.row_id = d.row_id AND CAST('20141101' AS DATE FORMAT 'yyyymmdd') BETWEEN d.start_date AND d.end_date
WHERE event_date = CAST('20141101' AS DATE FORMAT 'yyyymmdd')

在explain 完個execution plan 後的地方就會出現到Engine 為你提議要collect statistics 的地方

 10) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 1 minute and 12 seconds. 
     BEGIN RECOMMENDED STATS ->
 11) "COLLECT STATISTICS tableA COLUMN EVENT_DATE". 
     (HighConf)
 12) "COLLECT STATISTICS tableA COLUMN ROW_ID". 
     (HighConf)
 13) "COLLECT STATISTICS tableA COLUMN SERV_CD". 
     (HighConf)
 14) "COLLECT STATISTICS tableA COLUMN REF_CD". 
     (HighConf)
     <-END RECOMMENDED STATS

根據Engine 的提議執行完COLLECT STATISTICS後,再EXPLAIN個plan一次,就會發現預計的時間快了不少呢!

10) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 21.37 seconds.