 |
 |
Archives of the TeradataForum
Message Posted: Thu, 01 Aug 2002 @ 14:13:46 GMT
| Subj: | | Re: Incorrect Processing of NOT EXISTS? |
| |
| From: | | Geoffrey Rommel |
| | Has anyone had any problems with NOT EXISTS not working as expected? I've just produced explains for 3 methods of achieving the same
result. | |
Here's a fourth method:
select t1.colA
from small_table t1
left outer join big_table t2
on t1.colA = t2.colA
where t2.colA is null
| | I would have expected method 2 to have a similar explain to method 3. However methods 1 & 2 both read big_table into spool
(which takes over an hour), whereas method 3 just reads the index (which takes about a minute).... Does anyone know if this is correct and
has anyone else had any similar problems? | |
I have encountered this too. In general, there is no way to predict the path the optimizer will take. Moreover, "not in" and "not
exists" are different, as you probably know, because of the infamous null problem. The only way to know which of several equivalent queries
is best is to explain them. Unfortunately, the query plans could change with the next release of the optimizer ... but that's life in the
big world of database administration.
As for whether this is "correct", well, if you get the correct results, then it's correct. As recently mentioned in this forum, NCR will
put a higher priority on fixing incorrect results. Nevertheless, it's reasonable to ask them to look into this.
--wgr
| |
