Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sat, 05 Apr 2008 @ 12:53:33 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: PPI/constant view issue on V2R6.2
From:   Dieter Noeth

Victor Sokovin wrote:

  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.  

I've seen that, too, but didn't know that it worked in earlier releases. In some patch levels of 6.2 a single-row view with a hard-coded date like Lubomir's was resolved by the optimizer without any cross-join. In 6.1 and newer patches of 6.2 it switched to a cross join without partition elimination.

In all patch levels of TD12 it's resolving it again, but only if the view is not nested:

     replace view bla as select '2008-04-05' as today;
     replace view as select today + 1 as tomorrow, ... from bla;

doesn't work, just

     replace view bla as
     select '2008-04-05' as today, today + 1 as tomorrow, ...;

In TD12 all the problems with calculations using current_date are solved, too, it's always like a date literal.

  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.  

Which will result in a cross-join, too. But probably "enhanced by dynamic partition elimination".

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

  dw.transaction.Post_Date = ${DW_DATE} .  

That's the only case where you don't rely on the optimizer.

  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.  

Maybe it's a TD12 feature only, which was ported back accidentally :-)


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020