Re: full outer join:can somebody tell me why I receive these results?!
Date: Thu, 21 Feb 2008 19:26:41 -0800 (PST)
Message-ID: <e839c1a1-3f9f-49f4-a59e-33a18c8ffcea@o10g2000hsf.googlegroups.com>
On Feb 21, 4:42 pm, unebo..._at_hotmail.com wrote:
> Thanks a lot for answering this quick!!!
>
> First the plans on 9i without the hint:
>
> for those two queries
> select *
> from (select row_number() over(order by col1) as n1
> , col1
> from t1
> )
> full outer join
> (select row_number() over(order by col2) as n2
> , col2
> from t2
> )
> on n1=n2
> ;
>
> select *
> from (select rownum as n1
> , col1
> from (select *
> from t1
> order by col1
> )
> )
> full outer join
> (select rownum as n2
> , col2
> from (select *
> from t2
> order by col2
> )
> )
> on n2=n1
> ;
>
> I have those plans:
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 17
> VIEW 2 K 64 K 17
> UNION-ALL
> HASH JOIN OUTER 1 K 52 K 12
> VIEW 409 6 K 6
> WINDOW SORT 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T1 409 1 K 2
> VIEW 409 6 K 6
> WINDOW SORT 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> HASH JOIN ANTI 405 2 K 5
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> TABLE ACCESS FULL PROVENCHER.T1 409 1 K 2
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 17
> VIEW 2 K 64 K 17
> UNION-ALL
> HASH JOIN OUTER 1 K 52 K 12
> VIEW 409 6 K 6
> COUNT
> VIEW 409 1 K 6
> SORT ORDER BY 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T1 409 1 K 2
> VIEW 409 6 K 6
> COUNT
> VIEW 409 1 K 6
> SORT ORDER BY 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> HASH JOIN ANTI 405 11 K 5
> VIEW 409 6 K 2
> COUNT
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> VIEW 409 5 K 2
> COUNT
> TABLE ACCESS FULL PROVENCHER.T1 409 2
>
> Then I've changed both select,
> I added what you asked, so they look like this:
>
> select /*+ NO_QUERY_TRANSFORMATION */ *
> from (select row_number() over(order by col1) as n1
> , col1
> from t1
> )
> full outer join
> (select row_number() over(order by col2) as n2
> , col2
> from t2
> )
> on n1=n2
> ;
>
> select /*+ NO_QUERY_TRANSFORMATION */ *
> from (select rownum as n1
> , col1
> from (select *
> from t1
> order by col1
> )
> )
> full outer join
> (select rownum as n2
> , col2
> from (select *
> from t2
> order by col2
> )
> )
> on n2=n1
> ;
>
> first explain plan:
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 17
> VIEW 2 K 64 K 17
> UNION-ALL
> HASH JOIN OUTER 1 K 52 K 12
> VIEW 409 6 K 6
> WINDOW SORT 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T1 409 1 K 2
> VIEW 409 6 K 6
> WINDOW SORT 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> HASH JOIN ANTI 405 2 K 5
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> TABLE ACCESS FULL PROVENCHER.T1 409 1 K 2
>
> second explain plan:
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 17
> VIEW 2 K 64 K 17
> UNION-ALL
> HASH JOIN OUTER 1 K 52 K 12
> VIEW 409 6 K 6
> COUNT
> VIEW 409 1 K 6
> SORT ORDER BY 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T1 409 1 K 2
> VIEW 409 6 K 6
> COUNT
> VIEW 409 1 K 6
> SORT ORDER BY 409 1 K 6
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> HASH JOIN ANTI 405 11 K 5
> VIEW 409 6 K 2
> COUNT
> TABLE ACCESS FULL PROVENCHER.T2 409 1 K 2
> VIEW 409 5 K 2
> COUNT
> TABLE ACCESS FULL PROVENCHER.T1 409 2
>
> I have to humbly admit that although I understand reading explain
> plans a bit, i'm by far not expert enough to understand the current
> issue
> only by reading these plans with and without the hint...
>
> any further help will be appreciated...
Strategy A produces the expected results on Oracle 11.1.0.6, not that it helps your situation. You did not mention the exact release of Oracle 9i that you are using (exact release version will include 3 decimals). A search of Metalink finds a large number of bug reports for ANSI style joins that produce wrong results, some of those bugs were still present in Oracle 10.2.0.2 and were fixed in the Oracle 10.2.0.3 patchset.
For instance Metalink Note:2338661.8 (Bug 2338661) indicates that there is the potential for ANSI join problems with views.
What appears to be happening based on the explain plan is that the
views are being collapsed when Oracle tries to resolve the second half
of the plan, where it should determine the rows that are not common
among the two tables. Something similar to this is happening:
SELECT
N1,
COL1,
N2,
COL2
FROM
(SELECT
N1,
COL1
FROM
(SELECT
ROWNUM N1,
COL1
FROM
T1)) T1,
(SELECT
N2,
COL2
FROM
(SELECT
ROWNUM N2,
COL2
FROM
T2)) T2
WHERE
N1=N2
UNION ALL
SELECT
NULL,
NULL,
ROWNUM N2,
COL2
FROM
T2
WHERE
(ROWNUM,COL2) NOT IN
(SELECT
ROWNUM,
COL1
FROM
T1);
N1 C N2 C
---------- - ---------- -
1 a 1 e
2 c 2 b
3 b 3 a
1 e
2 b
3 a
4 d
You might need to use standard Oracle style outer joins { (+) }, rather than using ANSI style joins. Check Metalink for patches.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Feb 21 2008 - 21:26:41 CST