| how to rewrite the outer join statement [message #224860] |
Thu, 15 March 2007 15:12  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have the statement below running on SQL server 2000. It has better performance compare to the regular left outer join as it skips the filter . How can I rewrite this code work on Oracle?
SELECT
s.conflict_id as source_id,
s.disp_comments --some more fields going here
FROM sep_conflict s
left outer join (select source_id from app_activity_history h )as hist (source_id)
on s.conflict_id = hist.source_id
Thanks a lot for the help, mj
|
|
|
|
| Re: how to rewrite the outer join statement [message #224900 is a reply to message #224860] |
Thu, 15 March 2007 19:10  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
SELECT
s.conflict_id as source_id,
s.disp_comments --some more fields going here
FROM sep_conflict s
left outer join app_activity_history h hist
on s.conflict_id = hist.source_id
Ross Leishman
|
|
|
|
| Re: how to rewrite the outer join statement [message #225039 is a reply to message #224900] |
Fri, 16 March 2007 12:29 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
The benefit of the new syntax is to skip the additinal filter.
I estimate the plans on db2 v8 and v9, SQl server 2000 and 2005 and all 4 databases have better plans when using the new syntax. I need the same code for Oracle because all my application code must be the same.
Any idea how to do this?
Thanks a lot, mj
|
|
|
|
| Re: how to rewrite the outer join statement [message #225099 is a reply to message #225039] |
Sat, 17 March 2007 08:06 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle is smarter than that. You don't need to convolute your SQL to make it choose a more optimal path.
The syntax you used is not valid in Oracle. The equivalent syntax is:
SELECT s.conflict_id as source_id,
s.disp_comments --some more fields going here
FROM sep_conflict s
left outer join (
select source_id from app_activity_history h
) hist
on s.conflict_id = hist.source_id
And the difference between performance of this query and the one I posted above is.... wait for it ... none at all!
You are not going to get a database independent version of your original query working. If you feel you need to do it this way (the inline view), by all means don't let me stop you. But don't kid yourself that it is an improvement in Oracle.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
