Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 17 Jan 2005 @ 13:12:24 GMT


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


Subj:   Group by vs distinct, optimizer confidence and estimates
 
From:   Ole Dunweber

Hello,

Something weird is going here!

I get differing confidence when using group by and distinct in a simple select on a small lookup table with up-to-date statistics. If I put a view on top of the table, then the distinct get a totally wrong estimate!!

     SELECT DISTINCT WeekId
       FROM LKP_A_D.Lt_Time_Day

"The size of Spool 1 is estimated with high confidence to be 183 rows"

     SELECT WeekId
       FROM LKP_A_D.Lt_Time_Day
      GROUP BY 1

"Spool 3 is estimated with low confidence to be 183 rows"

There is a NUSI on column WeekId, which is used by the distinct-query.

But then if I put a simple view on top of the lookup-table, the distinct query only get low confidence and a totally wrong estimate!!! (Still using the NUSI).

     SELECT DISTINCT WeekId
       FROM LKP_A_I.Lv_Time_Day (view)

"The size of Spool 1 is estimated with high confidence to be 1,280 rows."

At least the group by version "is stable":

     SELECT WeekId
       FROM LKP_A_I.Lv_Time_Day (view)
      GROUP BY 1

"The size of Spool 3 is estimated with low confidence to be 183 rows"

We are on R5.0.3


Best Regards,

Ole Dunweber
Coop Nordic



     
  <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