Home » RDBMS Server » Performance Tuning » Replace Not Exists (Oracle 9.2, UNIX)
icon14.gif  Replace Not Exists [message #443756] Wed, 17 February 2010 04:25 Go to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
Hi , Laughing

I want to replace this not exists condition with joins or any other logic.It is taking long time to execute.

Please help me...

AND NOT EXISTS (
SELECT
'X'
FROM
uimsmgr.uabscon f
WHERE
f.uabscon_prem_code = a.uabscon_prem_code
AND f.uabscon_status_ind != 'P'
AND f.uabscon_status_ind != 'C'
Re: Replace Not Exists [message #443767 is a reply to message #443756] Wed, 17 February 2010 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Equivalent is to use outer join and IS NULL condition.

Regards
Michel
Re: Replace Not Exists [message #443777 is a reply to message #443756] Wed, 17 February 2010 05:07 Go to previous messageGo to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
Please give me the example or replaced original query.Its an urgent so please put your answer in actual solution.
Thanks in advance.
Re: Replace Not Exists [message #443782 is a reply to message #443777] Wed, 17 February 2010 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I din't say you will have better performances.
I just said it SQLy equivalent.

SQL> select * 
  2  from dept d
  3  where not exists (select null 
  4                    from emp e 
  5                    where e.deptno = d.deptno)
  6  /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

SQL> select d.*
  2  from dept d, emp e
  3  where e.deptno (+) = d.deptno 
  4    and e.deptno is null
  5  /
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

1 row selected.

Regards
Michel
Re: Replace Not Exists [message #443803 is a reply to message #443777] Wed, 17 February 2010 06:42 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Its an urgent so please put your answer in actual solution

Please don't use the keyword "urgent" in any of the public forums. It will attract wrong attention. You are lucky that @Michael replied to you.

Also this statement carries no value at all.
Quote:

It is taking long time to execute.

Define long time. To me long time could be few seconds or it could be few minutes or it could be few hours. Quantify it. How to quantify you can find it in the sticky post on this forum section.

Hopefully from next time you will avoid all these and before you post if you google it you can find the answers by yourself. Like for example i did a search in google and found this link.

http://www.pythian.com/news/765/oracle-is-outer-join-better-than-not-exists/

As @Michael, rightly said in your context outer join may not reduce the response time.

Regards

Raj
Previous Topic: Tuning Distributed Queries
Next Topic: Can some review my materialized view?
Goto Forum:
  


Current Time: Sun Sep 25 11:09:43 CDT 2016

Total time taken to generate the page: 0.08273 seconds