Home » RDBMS Server » Performance Tuning » how to rewrite the outer join statement
how to rewrite the outer join statement [message #224860] Thu, 15 March 2007 15:12 Go to next message
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 Go to previous messageGo to next message
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
should be fine - the optimizer takes care of the rest.

Ross Leishman
Re: how to rewrite the outer join statement [message #225039 is a reply to message #224900] Fri, 16 March 2007 12:29 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Oracle 8.1.7 statistics problem
Next Topic: to find out how many times a table got queried
Goto Forum:
  


Current Time: Thu Dec 05 18:52:45 CST 2024