Re: Avoid a subselect "where not equal to"?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 31 Jul 2006 10:38:42 -0700
Message-ID: <1154367522.341128.143020@75g2000cwc.googlegroups.com>

andrew.fabbro_at_gmail.com wrote:
> 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.

Compare the results above to the equivalent, using an inline view: SELECT

  TABLE1.ID,
  TABLE1.NAME,
  TABLE2.ID,
  TABLE2.TABLE1ID,
  TABLE2.STATUS

FROM
  TABLE1,
  TABLE2,
  (SELECT DISTINCT
    TABLE1ID
  FROM
    TABLE2
  WHERE
    STATUS = 'open') TNI
WHERE
  TABLE1.ID = TABLE2.TABLE1ID
  AND TABLE2.STATUS = 'new'
  AND TABLE1.ID = TNI TABLE1ID(+)
  AND TNI.TABLE1ID IS NULL; Above uses an inline view, outer join and then forces the outer join value to be NULL - the equivalent of NOT IN.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jul 31 2006 - 12:38:42 CDT