| outer join problem [message #267914] |
Sun, 16 September 2007 07:45  |
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 #267919 is a reply to message #267917] |
Sun, 16 September 2007 08:07  |
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 #267956 is a reply to message #267925] |
Sun, 16 September 2007 23:15 |
rleishman
Messages: 3728
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
- Ignore it and hope it goes away
- Actively track its performance until something changes.
Ross Leishman
|
|
|
|
| Re: outer join problem [message #267963 is a reply to message #267914] |
Sun, 16 September 2007 23:46 |
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 #268293 is a reply to message #267963] |
Tue, 18 September 2007 02:20 |
flyboy
Messages: 1903
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
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 |
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 |
flyboy
Messages: 1903
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
