Home » RDBMS Server » Performance Tuning » Not exists inside exists is having very low performence
Not exists inside exists is having very low performence [message #562583] Thu, 02 August 2012 07:24 Go to next message
narsimha6
Messages: 1
Registered: August 2012
Location: Hyderabad
Junior Member
Hi friends,
I am using a SQL like below.

SELECT columns FROM 4 TABLES with left outer , inner and left outer joins
AND ( EXISTS( this query is performing fast...)
OR ( EXISTS ( SELECT 1 FROM TABLEA WHERE condition
AND NOT EXISTS(SELECT 1 FROM TABLE B , TABLE C WHERE B.XXX = C.XXX AND extremeouter table reference.XXXX = c.XXXX )
)
)
)


In this case it is performing very slow and CPU cost is 25752.

Even I tried the same with outer join like below.

SELECT columns FROM 4 TABLES with left outer , inner and left outer joins
AND ( EXISTS( this query is performing fast...)
OR ( EXISTS ( SELECT 1 FROM TABLE A, TABLE B , TABLE C WHERE B.XXX(+) = C.XXX
AND other conditions
AND ROWID is NULL
AND extremeouter table reference.XXXX = c.XXXX
)
)
)


This is also performing very slow and CPU cost is 25752.
Can anyone please help me on this.
Thanks in advance
Bhaskar
Re: Not exists inside exists is having very low performence [message #562585 is a reply to message #562583] Thu, 02 August 2012 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Not exists inside exists is having very low performence [message #562680 is a reply to message #562585] Fri, 03 August 2012 06:26 Go to previous message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll need to post the complete sql statement, along with the other information Michel asked for, in order for anyone to have a chance at diagnosing your problem.
I will say that an outer-join inside an exists sub-query is almost certainly a waste of time.
Previous Topic: where the second fetch data ?
Next Topic: Performance difference of same query in 2 database
Goto Forum:
  


Current Time: Mon Apr 06 08:24:34 CDT 2020