Archives of the TeradataForum

Message Posted: Fri, 13 Oct 2006 @ 15:56:32 GMT

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

Silei:

I am more amazed that you got an explain from invalid SQL:

     > Select * from BIG_TABLE, BATCH_TABLE
     > where CRERATED_DATE = max(BATCH_TABLE.BATCH_DATE);

This is an improper use of an aggregate. Plus, it is a cartesian product join (no join condition between Big_table and batch_table - therefore, never a single partition, but instead a full table scan (many times). Look for a product join with a join condition of (1=1).

What happens if you do it correctly:

     Select * from BIG_TABLE
     where CRERATED_DATE = (sel max(BATCH_DATE) from BATCH_TABLE);

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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