Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 01 Aug 2002 @ 14:13:46 GMT

  <Prev Next>   <<First <Prev

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.


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023