|
Home > Archive > Oracle Database Server > August 2005 > Outer join - same query, different results...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Outer join - same query, different results...
|
|
| rpmfantasy@gmail.com 2005-08-18, 5:53 pm |
| I have a query such as:
select *
from mtl_material_transactions m
, po_headers_all ph
, po_lines_all pl
, 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 ph.PO_HEADER_ID = pl.PO_HEADER_ID
and r.po_line_id = pl.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(+)
This query runs fine.
But the same query (I think) written as:
select *
from
mtl_material_transactions m
left outer join rcv_transactions r
on m.SOURCE_LINE_ID = r.transaction_id
left outer join (select * from po_lines_all pl join po_headers_all ph
on pl.PO_HEADER_ID = ph.po_header_id) phpl
on r.po_line_id = phpl.po_line_id
left outer join wip_entities w
on m.ORGANIZATION_ID = w.organization_id and m.TRANSACTION_SOURCE_ID
= w.wip_entity_id
left outer join mtl_unit_transactions mu
on m.TRANSACTION_ID = mu.transaction_id
left outer join oe_order_lines_all o
on m.SOURCE_LINE_ID = o.line_id
left outer join mtl_generic_dispositions g
on m.ORGANIZATION_ID = g.organization_id and m.TRANSACTION_SOURCE_ID
= g.disposition_id
gives me
ORA-01445: cannot select ROWID from a join view without a key-preserved
table
What gives?
| |
| rpmfantasy@gmail.com 2005-08-18, 5:53 pm |
| Just fyi, I even tried:
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 this query still works, no ORA errors.
| |
| Jonathan Lewis 2005-08-20, 7:47 am |
| <rpmfantasy@gmail.com> wrote in message
news:1124397901.886745.214270@g43g2000cwa.googlegroups.com...
>I have a query such as:
>
> select *
> from mtl_material_transactions m
> , po_headers_all ph
> , po_lines_all pl
> , 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 ph.PO_HEADER_ID = pl.PO_HEADER_ID
> and r.po_line_id = pl.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(+)
>
> This query runs fine.
>
> But the same query (I think) written as:
>
> select *
> from
> mtl_material_transactions m
> left outer join rcv_transactions r
> on m.SOURCE_LINE_ID = r.transaction_id
> left outer join (select * from po_lines_all pl join po_headers_all ph
> on pl.PO_HEADER_ID = ph.po_header_id) phpl
> on r.po_line_id = phpl.po_line_id
> left outer join wip_entities w
> on m.ORGANIZATION_ID = w.organization_id and m.TRANSACTION_SOURCE_ID
> = w.wip_entity_id
> left outer join mtl_unit_transactions mu
> on m.TRANSACTION_ID = mu.transaction_id
> left outer join oe_order_lines_all o
> on m.SOURCE_LINE_ID = o.line_id
> left outer join mtl_generic_dispositions g
> on m.ORGANIZATION_ID = g.organization_id and m.TRANSACTION_SOURCE_ID
> = g.disposition_id
>
> gives me
> ORA-01445: cannot select ROWID from a join view without a key-preserved
> table
>
> What gives?
>
What version of Oracle ?
I think you have to live with the fact that the implementation
of the ANSI join syntax is not 100% perfect yet. (9.2.0.1 was
particularly bad)
As written, your two queries are not identical, because
of the line:
> and ph.PO_HEADER_ID = pl.PO_HEADER_ID
which effectively turns the join
> and r.po_line_id = pl.PO_LINE_ID(+)
into an inner join
My guess is that the crash is occuring on the
outer join to the inline view in the ANSI version,
you could test by removing the references to
po_headrs_all temporarily.
You might also try to list column names explicitly
in the inline join, and/or in the main select, in case
the internal rewrite is confusing columns.
--
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
| |
| rpmfantasy@gmail.com 2005-08-21, 2:49 am |
| 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.
| |
| Jonathan Lewis 2005-08-21, 5:50 pm |
|
<rpmfantasy@gmail.com> wrote in message
news:1124604948.634828.54870@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
| |
| rpmfantasy@gmail.com 2005-08-22, 5:58 pm |
| Jonathan,
It does look like it is the number of joins. I removed a couple tables,
and left the inline view in there, and it returned me some results.
And changing the complex_view_merging parameter did not help either.
Still returned the same 1445 error. :-(
|
|
|
|
|