Archives of the TeradataForum
Message Posted: Sat, 05 Apr 2008 @ 12:53:33 GMT
| 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 :-)
Dieter
| 
