Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 04 Apr 2008 @ 22:27:19 GMT

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

Subj:   Re: PPI/constant view issue on V2R6.2
From:   Victor Sokovin

  Before posting to this forum I tried to anonymize our SQL and explains a little bit. Unfortunatelly I've forgotten to change DWE_DATE to DW_DATE in explain from V2R6.  


Thanks for the confirmation. This case then looks like a clear example of a situation when a new release does not always mean a better release. We've all been there in our careers, and Teradata is not an exception. Hopefully, release 12, or 13, or N+1 will sort out all the problems.

In the meantime, the optimizer can use some help with figuring out that the view is 1-row big. My suggestion would be to replace the view with a real table and try and run the query with statistics collected on the table (and also without statistics collected - never overlook that option!). What may confuse the optimizer in this particular case is that the underlying table is not really materialized.

Another approach would be to embed the SQL in a script with the WHERE clause like this:

     dw.transaction.Post_Date = ${DW_DATE} .

In the script, the variable DW_DATE should be assigned its value before the SQL statement is executed. After the statement is parsed it would look like a statement with the Post_Date set to a literal, and you have verified that such cases are still working as expected.

I do realize that both suggestions require modification of the existing code (hey, that's what keeps us busy). Perhaps there is a magic global setting allowing to go back to the "older optimizer" but I am not aware of it.


  <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: 28 Jun 2020