Archives of the TeradataForum

Message Posted: Mon, 15 Oct 2001 @ 21:15:30 GMT


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

Subj:		Re: Stats always desirable (was Join question)

From:		Dieter N�th

Hi Petr,

My question is: does COLLECT STATISTICS provide the optimizer with any other info than the number of unique values? The manual says YES, but where is it stored and how can I retrieve it? HELP STATISTICS returns only the number of unique values.

It's up to 100 intervals with informatio about:

Max Value, #records and #other values within interval, #records

Jim Calvert's InTeraStats is available from

www.members.home.net...

Newer versions of Teradata Manager (4.2 up?) provide the same information now 'officially' and using the stats tool you're now even able to fool the optimizer by modifying statistics (if you know what you do).

Whatever the answer is, I find alarming, that adding stats when tuning one query may have disastrous impact on another query (probably hidden somewhere in daily transformation batches or even worth generated by ETL or reporting tools) - which was exactly this case.

It's a cost based optimizer and sometimes it's just misscalculating the costs. Look at the statistics, especially the skewed values, there could be entries for all the skewed values (but i don't know if this was already included in R3).

btw, did you collect statistics on the single row table MD_DWE_Date?

kind regards

Dieter N�th


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

Attachments

Library

Quick Reference