Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 25 May 2006 @ 16:41:47 GMT


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


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023