NOT IN is very inefficient

From: <bjohnsto_usa_net_at_my-dejanews.com>
Date: 1998/09/24
Message-ID: <6uefph$7ii$1@nnrp1.dejanews.com>#1/1

I have a query:

SELECT my_field FROM my_table1
WHERE my_field NOT IN

(SELECT my_field FROM my_table2)

Oracle (v7.3.4) is doing a table scan of the my_table2 (and my_table1) even though there is an index on table2. Performance is terrible. If I replace NOT IN with IN the performance is fine.

Is Oracle doing something stupid or am I? How can I get the results a smarter (faster) way? Is there something inherent in NOT IN which would stymie all databases or is the Oracle optimiser uniquely limited (again).

Brendan Johnston

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Sep 24 1998 - 00:00:00 CDT