Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: SQL running longer with Between function
From:   Dieter Noeth

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


  <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: 24 Jul 2020