Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 08 Dec 2009 @ 19:52:41 GMT


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


Subj:   Re: Theoretical collection over collecting stats
 
From:   Joseph D silva

As others have mentioned, it doesn't make this individual step any faster, the role of that stats comes into picture in the case of a join scenario.

To give an example,

Let's say you had Table A (1000, 000 rows) being joined to table B (10, 000 rows) on their non PI columns, ( A.key = B.key) and a filter on (A.col1 = 'val') and say there are about 40 Amps in the system.

One of the general join strategies on the absence of stats on col1 (which has no index either) would be to duplicate table B...

But what if A.col1 was fairly unique ? stats on A.col1 would have told the optimizer to scan table A, pull the records from A which has col1 = 'val' (which could be like 5 records or so ...) and duplicate it... (duplicating 5 records is better than duplicating 10,000 records)

So as you see the stats on A.col1 helped optimizer make a much smarter plan, though it didn't change the speed of the step that needs to access those records from table A.


Hope that helps.

Joseph D'silva



     
  <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