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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 31 Jul 2006 10:51:52 -0700
Message-ID: <1154368312.098293.210930@75g2000cwc.googlegroups.com>

Charles Hooper wrote:
> 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.

A period was missing from the where clause: WHERE
  TABLE1.ID = TABLE2.TABLE1ID
  AND TABLE2.STATUS = 'new'
  AND TABLE1.ID = TNI.TABLE1ID(+)
  AND TNI.TABLE1ID IS NULL; Is it possible in TABLE2 that there is more than one row containing a particular TABLE1ID value - a one to many relationship between TABLE1 and TABLE2, or is there only a one to one relationship - one row in TABLE1 can can only be matched to one row in TABLE2. If it is a one to one relationship, STATUS can have only one value, so the subquery and inline view are unnecessary.

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