Re: full outer join:can somebody tell me why I receive these results?!

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... Received on Thu Feb 21 2008 - 15:42:52 CST