Archives of the TeradataForum

Message Posted: Fri, 01 Mar 2013 @ 08:46:53 GMT

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

Hi Sirupa, Ashok wrote:

There's a WHERE-conndition on the calendar table, which changes that outer join to an inner join, but the optimizer is smart enough to automatically adjust that.

But when you cast a timestamp to a date the optimizer doesn't recognize that the cardinality drastically changes and greatly overestimates the number of distinct values, which might lead to bad join plans.

This is a known problem, you should check the step details in DBQL if this is the case, the join to the calendar is the first join and following joins are based on a low number of estimated rows.

If there is a statistic on Source_Create_Ts dropping it might be usefull (in most cases stats on a timestamp are not neccessary).

You should also show the DDL of the views, as there are 4 joins in explain, but only 3 joins in your query, i assume one of the views is more complex than "select columns from table".

Dieter

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