Re: What is wrong with this query?

From: Marc de Brouwer <mbrouwer_at_nl.oracle.com>
Date: 1995/05/18
Message-ID: <3pg00c$ao1_at_nlsu110.nl.oracle.com>#1/1


jvgreco_at_primenet.com (TurkBear) writes:

>In article <D8n7AC.6oo_at_cs.dal.ca> ae873_at_ccn.cs.dal.ca (Murray Kaiser) writes:
 

>>Joe Nardone (joe_at_access1.digex.net) wrote:
 

>>: select bill_acct_id from transmission
>>: where bill_acct_id not in (select acct_id from convert_acct);
 

>>I, too, use such SQL statements for exceptions reporting all the time.
 

>>Are you sure that bill_acct_id and acct_id are of the same data types?
>>One isn't varchar2 and the other char or number? The same size?

Here's my 5c:

The guy with the suggestion about the possible NULL occurrences in the convert_acct table would be my first bet. When a subquery used after NOT IN contains NULLs, the main query returns no rows. Its in the manual. Effectively, Oracle does a (a != b1 AND a != b2 AND a != b3 AND ...) where a is the bill_acct_id and b1,b2,b3,etc. are the acct_id values from the subquery. When one of them is a NULL, this returns NULL for the comparison. The ANDs make sure the entire result will be NULL (x AND null = null).

The discussion about IN / NOT IN / EXISTS / NOT EXISTS:

NON-correlated (i.e. independent) subqueries with IN operator often outperform (NOT) EXISTS constructs when the subquery result is relatively small compared to the number of rows the main (outer) query processes. Oracle first computes the records satisfying the subquery and looks up the matching records in the main query after that. It effectively reverses the query. This does not happen with NOT IN. BTW, I am talking rule based optimizing here, not cost-based.

Anyway,

I guess the fastest way to get your results would be to use a MINUS (how come we see so little set operators (except UNIONs) these days?  Does nobody think set-oriented anymore?)

Select bill_acct_id
from transmission
minus
select acct_id
from convert_acct

This is, of course, assuming that the columns are of the same type and both are indexed.
You may be surprised at the performance.


Marc de Brouwer          |   Standard disclamer applies:
Consultant               |   "These views are my own and I do not
Oracle Netherlands       |    represent my employer in this ..." etc.
mbrouwer_at_nl.oracle.com | You know what I mean.
Received on Thu May 18 1995 - 00:00:00 CEST

Original text of this message