Home » RDBMS Server » Performance Tuning » outer join problem
outer join problem [message #267914] Sun, 16 September 2007 07:45 Go to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi All

Outer join is reducing the performance of the retrival of data .
I need some idea to modify the query.

Thanks in advance

Nusrat
Re: outer join problem [message #267917 is a reply to message #267914] Sun, 16 September 2007 07:54 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Mostly outer join reduce the performance.
If you want somebody to help, post your query first. (with explain plan)

By
Vamsi
Re: outer join problem [message #267919 is a reply to message #267917] Sun, 16 September 2007 08:07 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi Vamsi
I do not have rights to access the database
so i can not provide the explain plan
we know that the query is used in a report is having the outer join which is having lots of null values.
we can not apply the "Where" condition because it will restrict the whole record to display.
We will give the solution to the client only
we will not even touch the database .

Thanks And Regards
Nusrat
Re: outer join problem [message #267925 is a reply to message #267919] Sun, 16 September 2007 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we can not apply the "Where" condition because it will restrict the whole record to display.

An outer join never restrict any record.

Quote:
do not have rights to access the database
so i can not provide the explain plan

Then you cannot optimize the statement.

Regards
Michel
Re: outer join problem [message #267956 is a reply to message #267925] Sun, 16 September 2007 23:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I find in these situations that prayer is equally as effective as the two other leading methods:

Click


Ross Leishman
Re: outer join problem [message #267963 is a reply to message #267914] Sun, 16 September 2007 23:46 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
Use Union all ..

select ename,dname from emp,dept
where emp.deptno = dep.deptno
union all
select ename,dname from emp,dept
where emp.deptno <> dep.deptno

Regards
Shivraj
Re: outer join problem [message #267970 is a reply to message #267914] Mon, 17 September 2007 00:25 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
I think left join is faster than union all.
Re: outer join problem [message #268293 is a reply to message #267963] Tue, 18 September 2007 02:20 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
ShivrajGutte wrote on Mon, 17 September 2007 06:46
select ename,dname from emp,dept
where emp.deptno = dep.deptno
union all
select ename,dname from emp,dept
where emp.deptno <> dep.deptno

Why not simply
select ename,dname from emp CROSS JOIN dept
as it produces the same resultset as your query.
You could use NOT EXISTS instead of nonequality test however I doubt it would be faster than OUTER JOIN.

@Nusrat: Check the methods described in 10g - How to Identify Performance Bottleneck thread.
Without access to database there is not very much you can do. I just wonder how are you supposed to enhance performance without touching anything.
Re: outer join problem [message #268312 is a reply to message #268293] Tue, 18 September 2007 02:59 Go to previous messageGo to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi flyboy
I will only write the script and give to them.
there is no need to touch the database.
I have given the query of union all
waiting for the response from client.

Regards
Nusrat
Re: outer join problem [message #268380 is a reply to message #267914] Tue, 18 September 2007 06:34 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi Nusrat,
it seems you misundestood me.

In the first part I just pointed out the given script gives CROSS JOIN instead of OUTER JOIN, so it is unusable.

If you want to produce resultset of LEFT OUTER JOIN, just issue it. If it is less performant than you expect/require, then follow the steps described in given link. Analyze data (cardinality, uniqueness), compare with table statistics and executed explain plan; then, based on its result, follow with another steps (eg. try to run different execution paths, create indexes, ...)
No "silver bullet" SQL to increase performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to achieve the same result?

There are some performance bugs when using ANSI syntax; however as you did not post the query and your Oracle version (4 decimal places), it is just a blind guess.
Previous Topic: Sql taking 2min with order by clause
Next Topic: Help - Creating Stored Outlines
Goto Forum:
  


Current Time: Wed Dec 07 05:07:04 CST 2016

Total time taken to generate the page: 0.09299 seconds