Re: Outer join - same query, different results...

<rpmfantasy_at_gmail.com> wrote in message news:1124604948.634828.54870_at_g47g2000cwa.googlegroups.com...
> Its Oracle 9.2.0.4.
>
> I rewrote the query as:
>
> select count(1)
> from mtl_material_transactions m
> , (select *
> from po_headers_all ph
> , po_lines_all pl
> where ph.po_header_id = pl.po_header_id) phpl
> , mtl_unit_transactions mu
> , mtl_generic_dispositions g
> , wip_entities w
> , oe_order_lines_all o
> , rcv_transactions r
> where
> m.SOURCE_LINE_ID = r.transaction_id(+)
> and r.po_line_id = phpl.PO_LINE_ID(+)
> and m.ORGANIZATION_ID = w.organization_id(+)
> and m.TRANSACTION_SOURCE_ID = w.wip_entity_id(+)
> and m.SOURCE_LINE_ID = o.line_id(+)
> and m.TRANSACTION_ID = mu.transaction_id(+)
> and m.ORGANIZATION_ID = g.organization_id (+)
> and m.TRANSACTION_SOURCE_ID = g.disposition_id(+)
>
> so that 'phpl' is an inline view rather than 2 seprate tables
>
> And I get the same result ... this runs fine but the ANSI standard
> implementation fails.
>
> Removing po_headers_all takes care of the query. If I remove
> po_headers_all it works fine.
>

I just did a metalink search on

    ansi outer 1445

Possibly you are hitting bug 4199351
(which has a couple of 'base bug' levels to follow).

The workarounds offered were:
set _complex_view_merging=false
Use ORACLE JOIN instead of ANSI JOIN
or
Reduce the number of LEFT OUTER JOINs

No comment about when the bug would be fixed.

As a test, you might remove each table in turn from the join to see if it is the inline view (i.e. not this bug) or just the reduction in joins (i.e. possibly this bug).

Then try the original query with:

    alter session set "_complex_view_merging"=false

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005