Hi I'm very new to teradata, and most of the websites are a bit advanced for me. What does collect stats, explain, and DIAGNOSTIC HELPSTATS ON FOR SESSION do ?
'Collect stats' builds information about the data values in one or more columns and some information about the table. For instance if you 'collect stats' on a single column the dbms will store: number of rows in the table and for the selected column max value, min value, modal value, number of rows with null, number of rows with modal value. Plus lots more.
- This information is then used by the optimiser (query planner) to build a plan which runs the query in the quickest way possible.
Explain is the mechanism which allows a person to see the query plan built by the optimiser without running the query.
'diagnostic help stats' is a command which can be used to find out which sets of statistics the optimiser would like to have but doesn't - i.e. recommendations. Note that the optimiser will often produce many 'stats recommendations', you need to be careful about which ones you actually implement. I usually only look at recommendations which have a 'high' confidence. And always test.