Re: Outer Join Performance

An outer join is functionally different from a normal join, and may be there to supply a correct business solution.

Having said that, recent versions of Oracle can recognise cases where outer joins are redundant and ignore them

    (e.g.
    where t1.col1(+) = t2.colA
    and t1.col2 = 'X'
    )

Typically, an outer join has to be postponed in the join order, hence the removal of an outer join may change the number of ways in which the optimiser will attempt to resolve a query, and many of these ways may get the (wrong) answers in a more cost-effective fashion.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Julia Cristina Varela de Montoya wrote in message <7dev3g$6gr$1_at_bgtnsc01.worldnet.att.net>...
>Our directors are under the assumption that outer-joins cause a performance
>decrease, and have instructed me to remove them from various places in
code,
>regardless of the fact that fewer rows may return.
>
>After doing explain plans, I see that outer-joins have less execution
steps,
>and do in fact return rows faster. Can someone summarize for me what I can
>say to management, or am I dreaming?
>
>
Received on Fri Mar 26 1999 - 11:33:16 CST