OraFAQ Forum: Performance Tuning » Replace Not Exists

Home » RDBMS Server » Performance Tuning » Replace Not Exists (Oracle 9.2, UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replace Not Exists [message #443756]

Wed, 17 February 2010 04:25

visuorac
Messages: 10
Registered: February 2010
Location: chennai

Junior Member

Hi ,

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'

Report message to a moderator

Re: Replace Not Exists [message #443767 is a reply to message #443756]

Wed, 17 February 2010 04:47

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Equivalent is to use outer join and IS NULL condition.

Regards
Michel

Report message to a moderator

Re: Replace Not Exists [message #443777 is a reply to message #443756]

Wed, 17 February 2010 05:07

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.

Report message to a moderator

Re: Replace Not Exists [message #443782 is a reply to message #443777]

Wed, 17 February 2010 05:27

Michel Cadot
Messages: 68650
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

Report message to a moderator

Re: Replace Not Exists [message #443803 is a reply to message #443777]

Wed, 17 February 2010 06:42

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 11 23:58:20 CDT 2024