Archives of the TeradataForum

Message Posted: Sat, 12 Apr 2014 @ 05:10:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi James,

I'll start off on this topic, but you will probably receive many replies some of which will contradict other replies (be warned!).

Firstly, do NOT collect stats on every column on every table. In my opinion that will be a monumental waste of time and resources (cpu and disk io).

You're absolutely correct that you won't know what columns the users are using in WHERE clauses. Join the club!

Standard Collect Stats guidelines/recommendations are:

- collect stats on all PI columns

- collect stats on all PPI columns

- collect stats on all NUSI's

- collect stats on columns used in selection and/or joins that have skewed data values, but probably only if the skew causes problems.

The last point is the tricky one and to a certain extent you will have to run queries, find that certain columns need stats and then add them to your refresh processing. You can't always predict the columns that will/will not require stats on it. You do need to have an understanding of your data and the types of queries run by your users.

But if you start with the guidelines then that should eliminate most of the really bad plans caused by lack of stats.

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>