 |
 |
Archives of the TeradataForum
Message Posted: Thu, 25 May 2006 @ 16:41:47 GMT
| Subj: | | Re: Optimizer makes erroneous estimate of table size... |
| |
| From: | | Victor Sokovin |
| | In the absence of statistics, the table size estimate made by the optimizer is based on Dynamic AMP Sampling. The script output below shows
that the estimate made by dynamic AMP sampling was incorrect in one of the Explain outputs. | |
Marcus, you are right that APM samples are used by the optimizer if it does not find relevant stats. If I recall correctly, the sample is about
10-15%. In your example, this is just 25 rows per AMP or so. It is indeed strange that even after possibly sampling a very non-representative 25
rows it would conclude that the entire table contained only 2 rows but let's attribute this to some global rounding-up. Because the table is so
small the rounding error variance can be relatively significant.
Because random sampling is involved we cannot draw any conclusions after only one sample. Try to do more experiments and see what the average
estimated table size is going to tend to. You could write a script which would, say, clone the EMP table to EMP_N, where N between 1 and 100, and
then perform the same set of actions on EMP_N as in your original script. As EMP_N is freshly created, there is no risk of "residual" stats or
explain plans.
It would be interesting to see the results.
Regards,
Victor
| |
