Archives of the TeradataForum

Message Posted: Tue, 11 Dec 2007 @ 12:35:16 GMT


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

Subj:		Re: Collect Stats on PI: Index vs Column

From:		william.gregg

Dave's comments:

I know it's not supposed to be this way, but I have noticed anecdotally (pre-v2r6, haven't checked on this version) that the optimizer often made better decisions based on stats on an index on a column than it did stats on just the column itself, even if it did not then use the index for selection.

This makes me do basic things like always making sure FK columns have an index and stats, because it can make the difference between a small table selection or not. Rule of thumb should be, try stats on column, then try index/stats on the column, and see which one works best. If it's a wash, just keep the stats.

I observed the same as Dave (sometimes the theory fails).

With respect to index versus column stats: I'd really like to believe that collecting column stats is just as good as index stats for single column indices; it would make life much easier -- we live in interesting times.

On the bright side, I have observed that sampled stats on indices are just as good as the full sample version and take 2/3 the time to collect.

I've also found that most NUSIs (built on the grounds of being a FK) are not used by the Optimizer. We ran a local test that tracked usage of 123 NUSIs on 7 large tables and found that only 33 were used in a test suite of 1700 queries.

We also found that running the same set of 1700 queries against tables without NUSIs (that's right the big 0) only took 20% longer than the version with 123 NUSIs.

A word of caution: the 1700 queries (IMHO) were biased toward table scanning (across wide swaths of time) rather than focused queries looking at a few days or a month of activity.

We live in very interesting times.

Rgrds,

Bill Gregg
Thomson Teradata Team


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

Attachments

Library

Quick Reference