Home » SQL & PL/SQL » SQL & PL/SQL » equivalent querry / NOT EXISTS (merged)
equivalent querry / NOT EXISTS (merged) [message #287031] Mon, 10 December 2007 23:49 Go to next message
donind
Messages: 95
Registered: February 2007
Member
HI,

How to rewrite the same querry without using exist.


UPDATE NC_BOD_DETAIL NBD1
SET NBD1.SYS_NC_TYPE = 'INSERT'
WHERE NOT EXISTS (SELECT 1
FROM MST_BOD_DETAIL MBD1
WHERE MBD1.OM_PARAMETER_ID = NBD1.OM_PARAMETER_ID
AND MBD1.EFF_START_DATE = NBD1.EFF_START_DATE)
AND NBD1.SYS_NC_TYPE = 'UPDATE';




Thanks in advance
Re: Exists and NOt exists [message #287034 is a reply to message #287031] Mon, 10 December 2007 23:56 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You can use NOT IN keyword instead of EXISTS

...WHERE OM_PARAMETER_ID NOT IN(SELECT OM_PARAMETER_ID  FROM MST_BOD_DETAIL WHERE ...)


Regards,
Kiran.

[Updated on: Mon, 10 December 2007 23:56]

Report message to a moderator

equivalent querry [message #287041 is a reply to message #287031] Tue, 11 December 2007 00:15 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Here below is an update statement. I have written equivalent update view . Just let me know whether i am correct or not


UPDATE NC_ORDR_MULT_MSTR NOM1
      SET NOM1.SYS_NC_TYPE = 'INSERT'
      WHERE NOT EXISTS (SELECT 1
                        FROM MST_ORDR_MULT_MSTR MOM1
                        WHERE MOM1.OM_PARAMETER_ID = NOM1.OM_PARAMETER_ID)
      AND NOM1.SYS_NC_TYPE = 'UPDATE';


equivalent querry

UPDATE (select nom1.sys_nc_type nom1_nc_type from NC_ORDR_MULT_MSTR NOM1
 where nom1.om_parameter_id not in
 (select MOM1.OM_PARAMETER_ID from MST_ORDR_MULT_MSTR MOM1) ) ab 
set ab.nom1_nc_type='INSERT' 
where ab.nom1_nc_type='UPDATE';




Any help really appreciated.

Thanks in advance

[Updated on: Tue, 11 December 2007 00:16]

Report message to a moderator

Re: Exists and NOt exists [message #287043 is a reply to message #287031] Tue, 11 December 2007 00:18 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Link


Quote:

When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, query can use an index within the sub-query.


Regards,
Kiran Aithal.
Re: Exists and NOt exists [message #287057 is a reply to message #287034] Tue, 11 December 2007 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NOT IN and NOT EXISTS are not equivalent:
SQL> create table t (id integer, val varchar2(10));

Table created.

SQL> insert into t values (1, null);

1 row created.

SQL> select * from t a where not exists (select null from t b where a.val = b.val);
        ID VAL
---------- ----------
         1

1 row selected.

SQL> select * from t a where val not in (select val from t b);

no rows selected

Beware of NULL.

Regards
Michel

[Updated on: Tue, 11 December 2007 00:44]

Report message to a moderator

Re: equivalent querry [message #287059 is a reply to message #287041] Tue, 11 December 2007 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost.

Regards
Michel
Re: equivalent querry / NOT EXISTS (merged) [message #287060 is a reply to message #287031] Tue, 11 December 2007 00:47 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

Beware of NULL.


I realized now.

Kiran.
Re: Exists and NOt exists [message #287314 is a reply to message #287043] Tue, 11 December 2007 22:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
kir_ait wrote on Tue, 11 December 2007 17:18

Link


Quote:

When using “NOT IN”, the query performs nested full table scans, whereas for “NOT EXISTS”, query can use an index within the sub-query.




This is - as Homer Simpson might have put it - Grade A Bull-Plop. This opinion is over 10 years out of date.

NOT IN sub-queries have not forced a nested plan since v7.2

7.3 introduced the Anti-Join, which permitted HASH or SORT-MERGE joins between the outer query and the sub-query (NOT nested). In 7.3 and 8.0 you needed to use the HASH_AJ or MERGE_AJ hint, and 8i introduced the more versatile UNNEST hint. As of 9i, unnesting of all sub-queries is handled automatically by the CBO whenever it reckons it is a good idea to do so.

This makes NOT IN and NOT EXISTS very similar in performance. The other alternative - a left-outer join that filters the successful joins is similar in performance as well.

There are some circumstances (such as NULL keys in the sub-query) where an anti-join is not possible. In those cases you can use NVL or WHERE clauses to manage/filter nulls.

Always make sure CBO is using the plan you expect though. Run it through Explain Plan and confirm that there is an ANTI-JOIN step, or that the second step of the FILTER step is indexed (preferably a UNIQUE access to a UNIQUE index).

Ross Leishman
Re: equivalent querry / NOT EXISTS (merged) [message #287323 is a reply to message #287031] Tue, 11 December 2007 23:29 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Quote:

This makes NOT IN and NOT EXISTS very similar in performance


Got the point.

Thanks Ross.

Kiran.
Previous Topic: example Subquery
Next Topic: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
Goto Forum:
  


Current Time: Sun Dec 04 15:00:35 CST 2016

Total time taken to generate the page: 0.08982 seconds