Teradata tip of the day 05/19

A simple tip for today:

Not sure if the stats have been collected well? Is your query performing badly inspite of tuning?
Run the command below and then run EXPLAIN plan on that query:
DIAGNOSTIC HELPSTATS ON FOR SESSION;

You will see suggestions from the optimizer on what columns stats need to be collected, at the end of EXPLAIN plan. See an example below:

     BEGIN RECOMMENDED STATS ->
  8) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W INDEX (KW ,ROTATION_ID ,PRTNR_CREATIVE_ID ,PRTNR_PRDCT_ID ,FTR_ITEM_ID ,CLICK_DT)".  (HighConf)

  9) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W COLUMN (TRANS_DT ,CNTRY_ID)".  (HighConf)
 10) "COLLECT STATISTICS working.DW_KWDM_PRTNR_MPX_REV_W COLUMN (CONTENT_MATCH_YN_ID ,TRANS_DT ,CNTRY_ID)".  (HighConf)
     <- END RECOMMENDED STATS

Similarly,

To get a more detailed explain plan, run the command below before executing EXPLAIN:
DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;

~Meena