Archives of the TeradataForum

Message Posted: Thu, 08 Jan 2004 @ 22:33:02 GMT

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

Ganga,

The explains are quite useful. If I read them correctly, the second query is executed basically as the 'fast' query

select * from obc_master.recontacts
where ocid = 9 and mid = 48 ;

with the additional join to the temp table B. This is like the derived table I wanted to use which did materialize this time.

The slow query fails in Step 3. The full scan of the large table with the condition ("(obc_master.recmtd.ACTIVITY_CODE = 1) OR (obc_master.recmtd.ACTIVITY_CODE = 2)") is done way to early as we know that the result set here is about 20 million rows. The product join with another set in Spool 2, with a few thousands rows in it (you might have a better estimate), is bound to be slow. Why the optimizer has chosen this way is hard to tell. Perhaps there is something in the data distribution which would justify or at least explain this decision.

More interesting and practical is to look at the solution you suggested. You do use the 'new' syntax (ON clause) here again. Have you also tried the 'old' syntax:

select *
from obc_master.recontacts A, obc_master.lu_temp B
where A.act = B.act
and ocid = 9 and mid = 48 ; ?

Any difference in performance? It would be good to know which release you are on to place this case into the right basket.

Regards,

Victor

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