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:

  LEFT OUTER JOIN PROD_V.DS_VW1_AVIVA_CALENDAR A2K_CREATE_DT ON (A2K_CREATE_DT.calendar_date= cast(A2K_WORKFLOW_SS.Source_Create_Ts as date))  


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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016