Hi Luke,
reading from the manuals: " the system automatically determines the appropriate sample size to generate accurate statistics for good query plans and performance."
Basically, when you collect stats on table using sample, Teradata itself reads the table and collects "incremental" statistics until their values are stable enough.
Once you have collected statisdtics on a column using sample, recollection are also sampled. If you want to change the way you are collecting them, you have to drop them and recollect in the "standard" way.
Hope this helps, bye,
TDUser
Hi TDuser,
yes thank you that has helped, I saw that in the documentation but did not understand fully, but now do
Hi Luke,
the global flag is set by dbscontrol -> internal field 46: CollectStatsSample
On a session level it's:
DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=xx" ON FOR SESSION;
For a singe Collect Stats:
DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=xx" ON FOR REQUEST;
followed by a "Collect stats using sample..."
Dieter
Hi dnoeth,
Thanks so much, this is so helpful, just ideal as you can have the sampling per collection, great.
Is this available in any doc as I could not find anything like this anywhere..?
Hi Luke,
sorry, but you won't find any documentation about it.
Most DIAGNOSTIC statements are undocumented, but sometimes you can guess the meaning of a keyword :-)
Dieter
Collecting stats can be executed with collecting a sample percentage which is supposed to be a good way to get stats for very large tables, but this is set somewhere which is system wide, ie when collecting stats and using sampling, this percentage value will be used each time sampling is used on small or large tables.
Does anyone know where I can find this percentage value and how to change this if needed, as I would like to test this in our environment to see the performance difference..?
thanks