How to decide about Stats for new queries

Database
Enthusiast

Re: How to decide about Stats for new queries

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)

Ambassador

Re: How to decide about Stats for new queries

DIAGNOSTIC HELPSTATS will return all stats the optimizer might utilze.

But this doesn't neccessarily mean that all those stats must be collected.

Enthusiast

Re: How to decide about Stats for new queries

From those recommendations, Only with High confidence should be collected?

Enthusiast

Re: How to decide about Stats for new queries

Even high confidence MAY be collected. Its not mandatory. collect once, then check if they are used and improve performance. else remove.

Enthusiast

Re: How to decide about Stats for new queries

Thanks !!

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?

Enthusiast

Re: How to decide about Stats for new queries

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).

Enthusiast

Re: How to decide about Stats for new queries

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.

Enthusiast

Re: How to decide about Stats for new queries

How one can check If the stats on one or more cols were not used in the query ?

Enthusiast

Re: How to decide about Stats for new queries

Check this out : http://developer.teradata.com/database/articles/identifying-used-unused-and-missing-statistics