Archives of the TeradataForum

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

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

Lubomir,

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.

Victor

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