Avoid a subselect "where not equal to"?

From: <andrew.fabbro_at_gmail.com>
Date: 31 Jul 2006 09:49:35 -0700
Message-ID: <1154364575.190177.63490@h48g2000cwc.googlegroups.com>

A colleague asked for help with this query...I got it working for him, but someone else mentioned that it would perform horribly at scale.

So here's the problem:

CREATE TABLE table1 (id integer, name varchar2(10)); CREATE TABLE table2 (id integer, table1id integer, name status(10));

He wants to join on table1.id = table2.table1id and status = 'new' - that part is easy - but does not want any rows from table1 that also have table2.status = 'open'.

For example:

INSERT INTO table1 (id, name) VALUES ( 1, 'x' );
INSERT INTO table1 (id, name) VALUES ( 2, 'y' );
INSERT INTO table1 (id, name) VALUES ( 3, 'z' );
INSERT INTO table2 (id, table1id, name) VALUES ( 10, 1, 'new');
INSERT INTO table2 (id, table1id, name) VALUES ( 11, 1, 'open');
INSERT INTO table2 (id, table1id, name) VALUES ( 12, 2, 'new');

...and the query he wants would return only the second row of table1 (not row 1 because it has an 'open' in table2, and not row 3 because it does not have a 'new' in table2).

So my solution was:

SELECT Table1.id,Table1.name,Table2.id,Table2.Table1id,Table2.Status FROM Table1,Table2

WHERE   Table1.id = Table2.Table1id AND
        Table2.Status = 'new' AND
        Table1.id != (SELECT table1id FROM table2 WHERE status =

'open');

And that worked fine. However, someone else referred to this as a "nasty" query, opining that "where not in (select...) queries are some of the worst" in a performance sense.

Oracle's plan is not pretty (this is for the sample database just shown, not the production data set)...

| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)|
Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     2 |   214 |    10  (10)|
00:00:01 |


|*  1 |  HASH JOIN          |        |     2 |   214 |     7  (15)|

00:00:01 |


|*  2 |   TABLE ACCESS FULL | TABLE1 |     2 |    94 |     3   (0)|

00:00:01 |


|*  3 |    TABLE ACCESS FULL| TABLE2 |     1 |    47 |     3   (0)|

00:00:01 |


|*  4 |   TABLE ACCESS FULL | TABLE2 |     2 |   120 |     3   (0)|

00:00:01 |

...but I can't think of any other way to get the desired result...anyone? (For 10g).

Thx. Received on Mon Jul 31 2006 - 11:49:35 CDT