For any new query on new table when no prior stats collected...
- Wil Running Diagnostic Helpstats give all the stats needed for the query?
I mean rather looking in joins/where clause of the query to check for needed stats, can i just use Diagnostic Helpstats (easy & quick)
DIAGNOSTIC HELPSTATS will return all stats the optimizer might utilze.
But this doesn't neccessarily mean that all those stats must be collected.
Even high confidence MAY be collected. Its not mandatory. collect once, then check if they are used and improve performance. else remove.
How i can check which stats are being used for the query?
:-) Sorry not expert in Teradata
What would be the impact of keeping the stats which are not being used?
Perform an explain on the query. If the stats on one or more cols are not used in your query, drop them since they take up space in the dictionary table(s).
Stats collection is an ever refining process. Don't get too much overwhelmed about it. Run the queries , do stats , run them again and keep on adding /removing following the basic guidelines and thats preety much you would do.