Archives of the TeradataForum
Message Posted: Thu, 08 Jan 2004 @ 22:33:02 GMT
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
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:
Any difference in performance? It would be good to know which release you are on to place this case into the right basket.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|