Home » SQL & PL/SQL » SQL & PL/SQL » Query with NOT IN (Oracle 10g)
Query with NOT IN [message #360004] Wed, 19 November 2008 03:32 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have written a query which works fine with results when written as :

)
SELECT wowsm_wo_id woid, wowsm_ws_id wsid
    FROM ord_wkor_ws_mapping
 WHERE wowsm_wo_id || '#' || wowsm_ws_id NOT IN (
          SELECT b.vwoid || '#' || b.vwsid
            FROM vworkorder_states b,
                 (SELECT   MAX (vupdateddate) dt, vwoid, vwsid
                      FROM vworkorder_states
                  GROUP BY vwoid, vwsid) a
           WHERE dt = vupdateddate AND a.vwoid = b.vwoid)  
)

and returns only 6 records. Now I do not want to use NOT IN as it makes it slower.
So when I write it as :
)
SELECT  wowsm_wo_id woid , wowsm_ws_id wsid
    FROM ord_wkor_ws_mapping,
    (SELECT b.vwoid ,b.vwsid
            FROM vworkorder_states b,
                 (SELECT   MAX (vupdateddate) dt, vwoid, vwsid
                      FROM vworkorder_states
                  GROUP BY vwoid, vwsid) a
           WHERE dt = vupdateddate AND a.vwoid = b.vwoid) c
 WHERE  ( c.vwoid || '#' || c.vwsid )!= (wowsm_wo_id ||'#'|| wowsm_ws_id


it is giving wrong result that is 1264 records.

Please guide me on this as what is wrong with the upper second query. Is it making any cartesian by mistake.

Please advice,

Regards,
Mahi
Re: Query with NOT IN [message #360010 is a reply to message #360004] Wed, 19 November 2008 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What's wrong? Query are not equivalent:
SQL> select dname 
  2  from dept
  3  where deptno not in (select deptno from emp)
  4  /
DNAME
--------------
OPERATIONS

1 row selected.

SQL> select dname 
  2  from dept d,
  3       (select deptno from emp) e
  4  where e.deptno != d.deptno
  5  /
DNAME
--------------
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
SALES
SALES
SALES
SALES
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS

42 rows selected.

Regards
Michel
Re: Query with NOT IN [message #360014 is a reply to message #360010] Wed, 19 November 2008 03:56 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks Michel for explaining me this.

How to change my query to give correct result but without NOT IN.

Regards,
Mahi
Re: Query with NOT IN [message #360016 is a reply to message #360004] Wed, 19 November 2008 04:17 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about posting the explain plan of the first query. Also could you please confirm the cardinalities you see in the explain plan are matching your expectations ?

Regards

Raj
Re: Query with NOT IN [message #360025 is a reply to message #360014] Wed, 19 November 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use NOT EXISTS taking care of NULL values.

But you have to prove that NOT IN is the root of your performances problem and not other thing.

Regards
Michel
Re: Query with NOT IN [message #360033 is a reply to message #360025] Wed, 19 November 2008 05:18 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
SELECT dname
FROM dept a
WHERE NOT EXISTS (SELECT b.deptno FROM emp b
                  WHERE a.deptno=b.deptno)

I tried this and it works.
Is it fine to use this instead of the earlier NOT IN query.

[Updated on: Wed, 19 November 2008 05:20]

Report message to a moderator

Re: Query with NOT IN [message #360046 is a reply to message #360033] Wed, 19 November 2008 06:07 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In case of standard dept and emp, yes.
In case of your query also (thanks to the '#' you added).
Generally speaking it is not equivalent if the selected value can be null.

Just insert a row in emp with deptno as NULL and you will see.

Regards
Michel

Previous Topic: ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type
Next Topic: mutating table error with an extra trigger
Goto Forum:
  


Current Time: Tue Dec 06 08:45:47 CST 2016

Total time taken to generate the page: 0.14395 seconds