Archives of the TeradataForum

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

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

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>>