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

Martin T. wrote:
Ed Prochak wrote:
Martin T. wrote:
andrew.fabbro@xxxxxxxxx wrote:
(snipped)

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.


But ... thats exactly what you want to know, isn't it?
-> "Give me all new ones where no open ones exist."
So I guess it's OK to ask Oracle exactly that :)

I think this query can scale very well, with the right indexes ...

best,
Martin

Indices alone won't help. Charles Hooper gave a good solution. An outer
join where you pick the rows with NULL works. But it all depends on
the version of ORACLE.

Ed

I wouldn't have been able to do what he did, so I didn't dare comment
on it ;)

I compared the two SELECTs though - the one with the NOT IN clause
seems to generate the nicer query plan with my 9i2 CBO than the one
with the outer join (of course it would depend on real data etc.)

I would stick with the OPs query until someone prooves a better
solution (i.e. faster for real data or more readable).

whatever - just my 002

best,
Martin

In 8i, the method that I posted, if the data set is more than a couple
dozen rows, will normally execute much faster than the subquery method
- 50 to 100 times faster is not impossible. The inline view only needs
to be resolved once, while the subquery needs to be resolved once per
row in the result set.

As indicated by Ed, different version of Oracle may handle the SQL
statement differently, possibly rewriting the SQL statement into a more
efficient form. Keep in mind that the query plan is just that - just
an educated guess at the cost of the execution plan, and the
anticipated access path. The database may not follow the query plan
when the SQL statement is executed. A 10053 cost based optimizer trace
of the actual SQL execution may provide clues, as should a 10046 trace.
"Cost-Based Oracle Fundamentals" by Jonathan Lewis gives the scary
details.

Note that there is a potential bug in the original poster's SQL
statement:
Table1.id != (SELECT table1id FROM table2 WHERE status = 'open');

If the above select returns more than one row... Also, there is no
restriction on the subquery to return only the one table1id in the
subquery that is relevant to the row.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

.

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Multiple AND criteria
    ... A subquery is like a whole SELECT query inside another query. ... Then switch it to SQL view to ... FROM tblPart INNER JOIN tblPartAttrib ...
    (microsoft.public.access.queries)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... Perahps we've begin to uncover some bugs in the Access query ... If you PASTE SQL test in the following format into the SQL window ... around a field or table name within the VirtTbl1 subquery, ... I would think that producing a correct error message for that sort of query ...
    (microsoft.public.access.queries)
Loading