Archives of the TeradataForum

Message Posted: Mon, 29 Nov 2010 @ 21:13:40 GMT

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

JK,

There are two methods of changing the default 2% sampling size:

     1.  Systemwide:  DBS Control setting Internal Field  46.
             CollectStatsSample     = 2 (percent)

     2.  At the session level:  DIAGNOSTIC "COLLECTSTATS, SAMPLESIZE=30" ON FOR SESSION;

For some indexes, the optimizer may change the sampling percent to be greater than what you specified. This is normal behavior.

With indexes, blocks at a time are read, so the actual number of rows sampled are rarely equal to the percent specified. Plus NUSIs usually carry many base table row ID for a given index value, and the percentage is applied to the NUSI rows, not the base table rows ID within the NUSI rows.

So you often get a much higher percentage sampled than you intended with NUSIs. For example, I collected stats an index with only 25 values with sample size at 30% and ended up getting 60% of the rows sampled.

Thanks, -Carrie

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