Home » SQL & PL/SQL » SQL & PL/SQL » how to avoid outer join? (10g)
how to avoid outer join? [message #388116] Mon, 23 February 2009 05:06 Go to next message
deepbans
Messages: 32
Registered: February 2009
Member
Is there any alternative to avoid outer join?

can anybody give example how can a same sql can be genrated without using outer join
Re: how to avoid outer join? [message #388118 is a reply to message #388116] Mon, 23 February 2009 05:16 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Could you post your Outer Join Query so that we can try to modify it without that.

Regards,
Ashoka BL
Bengaluru
Re: how to avoid outer join? [message #388119 is a reply to message #388116] Mon, 23 February 2009 05:21 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,


    You can use "in" and "not in" clause and use a subquery to get the desired output. But outer join will have better effect than having a "in" and "not in" clause.

    You can use nested selects to return values for individual columns from tables that may have missing records, instead of an outer join.



Regards,
Ashoka BL
Bengaluru
Re: how to avoid outer join? [message #388120 is a reply to message #388116] Mon, 23 February 2009 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select ... in/exists ...
union all
select ... not in/exists ...

Regards
Michel
Re: how to avoid outer join? [message #388127 is a reply to message #388120] Mon, 23 February 2009 05:54 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
select oeh.order_number order_number, oeh.header_id,order_header_id,
oeh.org_id om_org_id,chsc.sales_credit_type_id, chsc.source_header_id,bid_hzcsua.cust_acct_site_id
from
hz_cust_site_uses_all bid_hzcsua,
xxcsm.csm_header_sales_credits chsc,
oe_order_headers_all oeh
where
oeh.header_id = chsc.source_header_id(+)
AND oeh.invoice_to_org_id = bid_hzcsua.site_use_id(+)


can you please explain it through this example...
Re: how to avoid outer join? [message #388128 is a reply to message #388127] Mon, 23 February 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to remove outer join?

Regards
Michel
Re: how to avoid outer join? [message #388136 is a reply to message #388128] Mon, 23 February 2009 07:09 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
actually, This query i have given is only a small part of my final query..

And my query is taking a long time in execution..
I am tuning that query..

Will removing the outer join help me in tuning the query?
Re: how to avoid outer join? [message #388137 is a reply to message #388127] Mon, 23 February 2009 07:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You may come out from the definition of OUTER JOIN:
Quote:
An outer join returns all rows that satisfy the inner join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

By the way, the first IN/EXISTS may be replaced by using INNER JOIN, so it may look somehow like (as not all columns have table alias prefix):
SELECT oeh.order_number order_number, oeh.header_id,
       order_header_id, oeh.org_id om_org_id, chsc.sales_credit_type_id,
       chsc.source_header_id,bid_hzcsua.cust_acct_site_id
FROM hz_cust_site_uses_all bid_hzcsua,
     xxcsm.csm_header_sales_credits chsc,
     oe_order_headers_all oeh
WHERE oeh.header_id = chsc.source_header_id
  AND oeh.invoice_to_org_id = bid_hzcsua.site_use_id
UNION ALL
SELECT <columns from oeh, otherwise NULL>
FROM oe_order_headers_all oeh
WHERE NOT EXISTS (SELECT 1
      FROM xxcsm.csm_header_sales_credits chsc
      WHERE oeh.header_id = chsc.source_header_id)
   OR NOT EXISTS (SELECT 1
      FROM hz_cust_site_uses_all bid_hzcsua
      WHERE oeh.invoice_to_org_id = bid_hzcsua.site_use_id);

Be aware, that this (like all other possible workarounds) is more complex and less performant than using OUTER JOIN.
Re: how to avoid outer join? [message #388140 is a reply to message #388136] Mon, 23 February 2009 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And my query is taking a long time in execution..
I am tuning that query..

If this is the REAL question why don't you ask it and then provide the usual and requested information for tuning?

Regards
Michel
Re: how to avoid outer join? [message #388141 is a reply to message #388137] Mon, 23 February 2009 07:21 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Thanks for solution.
Re: how to avoid outer join? [message #388142 is a reply to message #388137] Mon, 23 February 2009 07:26 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
flyboy wrote on Mon, 23 February 2009 14:13
Be aware, that this (like all other possible workarounds) is more complex and less performant than using OUTER JOIN.

deepbans wrote on Mon, 23 February 2009 14:09
Will removing the outer join help me in tuning the query?

The answer to your question is stated above (I just submitted my post after yours without reading).
deepbans wrote on Mon, 23 February 2009 14:21
Thanks for solution.

Anyway, if you would be satisfied with worse performance, enjoy it.

[Edit: added the last quote]

[Updated on: Mon, 23 February 2009 07:28]

Report message to a moderator

Previous Topic: function & wrap utility (merged)
Next Topic: exracting huge data from remote database
Goto Forum:
  


Current Time: Mon Dec 05 08:54:19 CST 2016

Total time taken to generate the page: 0.10649 seconds