December 30, 2010 (Modified January 1, 2011)
When I read pages 101-103 of the book “Pro Oracle SQL” a couple of days ago, I was reminded of a couple of things. This section of the book describes full outer joins, showing the ANSI syntax and Oracle syntax to perform a full outer join. If you read my earlier blog article on the topic of ANSI SQL, you probably saw in the article that there are a couple of bug reports listed in Metalink (MOS) that describe problems with ANSI joins, including a couple that are specific to Oracle Database versions below 11.2.0.2. As far as I can tell (at least with Oracle Database 11.2.0.1), ANSI full outer joins are the only ANSI formatted join syntax that is not converted into Oracle specific join syntax. The second thing that it reminded me of is a search of the phrase pig outer join that seemed to hit my blog at least once a week. There is one article on my blog that matches that description, and that article includes the following example of a full outer join using Oracle syntax:
SELECT T1.ANIMAL, T2.ANIMAL FROM TABLE_1 T1, TABLE_2 T2 WHERE T1.ANIMAL(+)=T2.ANIMAL UNION SELECT T1.ANIMAL, T2.ANIMAL FROM TABLE_1 T1, TABLE_2 T2 WHERE T1.ANIMAL=T2.ANIMAL(+); T1.ANIMAL T2.ANIMAL COW COW PIG PIG (null) DOG ZEBRA (null) SHARK (null) ROOSTER (null) LION (null)
The above example works well as long as multiple rows do not have the same value in the ANIMAL column. The example on page 102 of the book looks similar to the one above, just without all of the common farm animals. 🙂
But there was one other item that caught my eye. I could not reproduce the execution plan found in the book using the ANSI full outer join syntax on Oracle Database 11.2.0.1. Interesting… was the wrong execution plan copied into the book, or is there another explanation? Let’s put together a test script to help see what is happening:
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2000M SCOPE=MEMORY;
CREATE TABLE
T1
AS
SELECT
ROWNUM C1,
LPAD(TO_CHAR(ROWNUM),10,'0') C2,
LPAD('A',200,'A') PADDING
FROM
DUAL
CONNECT BY
LEVEL<=100000;
CREATE TABLE
T2
AS
SELECT
ROWNUM C1,
LPAD(TO_CHAR(ROWNUM),10,'0') C2,
LPAD('A',400,'A') PADDING
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
CREATE INDEX IND_T1_C1 ON T1(C1);
CREATE INDEX IND_T2_C1 ON T2(C1);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL)
Wait 60 seconds, just to make certain that the 2,000M PGA_AGGREGATE_TARGET has had a chance to take effect, then continue with the script. We will write the full outer join SQL statement four different ways, with the third method using the UNION clause as it was used in the book:
SET AUTOTRACE TRACEONLY STATISTICS SET ARRAYSIZE 1000 ALTER SESSION SET STATISTICS_LEVEL='ALL'; SELECT /* FIND_ME 1 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.C1 IS NULL; SELECT /* FIND_ME 2 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.ROWID IS NULL; SELECT /* FIND_ME 3 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1(+)=T2.C1; SELECT /* FIND_ME 4 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C1; SET AUTOTRACE OFF SET LINESIZE 170 SET PAGESIZE 2000 SET TRIMSPOOL ON SPOOL TEST_OUTER_PLANS.TXT SELECT /*+ LEADING(S) */ T.PLAN_TABLE_OUTPUT FROM (SELECT SQL_ID, CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /* FIND_ME%') S, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST +COST +BYTES')) T; SPOOL OFF
When the above script is executed, the execution plans for the first half of the test script will be written to a file named TEST_OUTER_PLANS.TXT. Now on to the second half of the script were duplicate rows are introduced into table T1, some rows are deleted, and other rows are modified:
INSERT INTO
T1
SELECT
ROWNUM C1,
LPAD(TO_CHAR(ROWNUM),10,'0') C2,
LPAD('A',200,'A') PADDING
FROM
DUAL
CONNECT BY
LEVEL<=50000;
UPDATE
T1
SET
C1=NULL
WHERE
C1 BETWEEN 90000 AND 100000;
UPDATE
T1
SET
C1=C1+200000
WHERE
C1 BETWEEN 1 AND 1000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL,NO_INVALIDATE=>FALSE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL,NO_INVALIDATE=>FALSE)
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;
SET AUTOTRACE TRACEONLY STATISTICS
SELECT /* FIND_ME 1 */
T1.C1,
T2.C1,
T1.C2
FROM
T1,
T2
WHERE
T1.C1=T2.C1(+)
UNION ALL
SELECT
T1.C1,
T2.C1,
T1.C2
FROM
T1,
T2
WHERE
T1.C1(+)=T2.C1
AND T1.C1 IS NULL;
SELECT /* FIND_ME 2 */
T1.C1,
T2.C1,
T1.C2
FROM
T1,
T2
WHERE
T1.C1=T2.C1(+)
UNION ALL
SELECT
T1.C1,
T2.C1,
T1.C2
FROM
T1,
T2
WHERE
T1.C1(+)=T2.C1
AND T1.ROWID IS NULL;
SELECT /* FIND_ME 3 */
T1.C1,
T2.C1,
T1.C2
FROM
T1,
T2
WHERE
T1.C1=T2.C1(+)
UNION
SELECT
T1.C1,
T2.C1,
T1.C2
FROM
T1,
T2
WHERE
T1.C1(+)=T2.C1;
SELECT /* FIND_ME 4 */
T1.C1,
T2.C1,
T1.C2
FROM
T1 FULL OUTER JOIN T2
ON T1.C1=T2.C1;
SET AUTOTRACE OFF
SET LINESIZE 170
SET PAGESIZE 2000
SET TRIMSPOOL ON
SPOOL TEST_OUTER_PLANS2.TXT
SELECT /*+ LEADING(S) */
T.PLAN_TABLE_OUTPUT
FROM
(SELECT
SQL_ID,
CHILD_NUMBER
FROM
V$SQL
WHERE
SQL_TEXT LIKE 'SELECT /* FIND_ME%') S,
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST +COST +BYTES')) T;
SPOOL OFF
The execution plans for the second half of the script are written to a file named TEST_OUTER_PLANS2.TXT. So, what does the output of the plans look like on Oracle Database 10.2.0.5 Standard Edition? The execution plans for the first half of the script:
SQL_ID cfjjpmdc0ghsx, child number 0
-------------------------------------
SELECT /* FIND_ME 1 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2
FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.C1 IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 998 (100)| 100K|00:00:00.13 | 12483 | | | |
| 1 | UNION-ALL | | 1 | | | | 100K|00:00:00.13 | 12483 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| 273 (2)| 100K|00:00:00.04 | 3460 | 3748K| 1935K| 5608K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.01 | 3231 | | | |
|* 5 | FILTER | | 1 | | | | 0 |00:00:00.10 | 9023 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 1 | 21 | 725 (1)| 100K|00:00:00.04 | 9023 | 3748K| 1935K| 5609K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.01 | 3131 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1"."C1" IS NULL)
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID 1qtvpf3mnb3nx, child number 0
-------------------------------------
SELECT /* FIND_ME 2 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2
FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.ROWID IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 998 (100)| 100K|00:00:00.13 | 12483 | | | |
| 1 | UNION-ALL | | 1 | | | | 100K|00:00:00.13 | 12483 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| 273 (2)| 100K|00:00:00.04 | 3460 | 3748K| 1935K| 5608K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.01 | 3231 | | | |
|* 5 | FILTER | | 1 | | | | 0 |00:00:00.10 | 9023 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 100K| 3222K| 725 (1)| 100K|00:00:00.04 | 9023 | 3748K| 1935K| 5609K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 100K| 2734K| 252 (1)| 100K|00:00:00.01 | 3131 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1".ROWID IS NULL)
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID 4v4r3dau0barp, child number 0
-------------------------------------
SELECT /* FIND_ME 3 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2
WHERE T1.C1(+)=T2.C1
Plan hash value: 2719845061
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2078 (100)| 100K|00:00:00.31 | 12383 | | | |
| 1 | SORT UNIQUE | | 1 | 200K| 4101K| 5913K| 2078 (62)| 100K|00:00:00.31 | 12383 | 5510K| 1269K| 4897K (0)|
| 2 | UNION-ALL | | 1 | | | | | 200K|00:00:00.04 | 12383 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| | 273 (2)| 100K|00:00:00.04 | 3360 | 3748K| 1935K| 5609K (0)|
| 4 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| | 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| | 252 (1)| 100K|00:00:00.01 | 3131 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 100K| 2050K| | 725 (1)| 100K|00:00:00.04 | 9023 | 3748K| 1935K| 5609K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| | 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| | 252 (1)| 100K|00:00:00.01 | 3131 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID gs96s7s3ff9sp, child number 0
-------------------------------------
SELECT /* FIND_ME 4 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C1
Plan hash value: 2431666783
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 766 (100)| 100K|00:00:00.11 | 9581 | | | |
| 1 | VIEW | | 1 | 100K| 3222K| 766 (2)| 100K|00:00:00.11 | 9581 | | | |
| 2 | UNION-ALL | | 1 | | | | 100K|00:00:00.11 | 9581 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| 273 (2)| 100K|00:00:00.04 | 3460 | 3748K| 1935K| 5608K (0)|
| 4 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.01 | 3231 | | | |
|* 6 | HASH JOIN ANTI | | 1 | 1 | 10 | 493 (2)| 0 |00:00:00.08 | 6121 | 3748K| 1935K| 5546K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | INDEX FAST FULL SCAN| IND_T1_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
Notice in the above that the ANSI full outer join syntax SQL statement completed faster than the other methods, almost 3 times faster than the method using UNION, but only slightly faster than the method using UNION ALL. Take a close look at the Predicate Information section of the execution plan for the second SQL statement – notice that there is no filter predicate on plan line 8 (keep an eye on the plan for the second SQL statement later). The execution plan for the ANSI full outer join is very similar to what is shown in the book, assuming that tables T1 and T2 did not have indexes.
The execution plans for the second half of the script on Oracle Database 10.2.0.5 Standard Edition:
SQL_ID cfjjpmdc0ghsx, child number 0
-------------------------------------
SELECT /* FIND_ME 1 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2
FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.C1 IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1261 (100)| 161K|00:00:00.20 | 15757 | | | |
| 1 | UNION-ALL | | 1 | | | | 161K|00:00:00.20 | 15757 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| 404 (2)| 150K|00:00:00.04 | 5122 | 3748K| 1935K| 5610K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.01 | 4893 | | | |
|* 5 | FILTER | | 1 | | | | 11001 |00:00:00.12 | 10635 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 9334 | 191K| 856 (1)| 149K|00:00:00.04 | 10635 | 3748K| 1935K| 5611K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.01 | 4743 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1"."C1" IS NULL)
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID 1qtvpf3mnb3nx, child number 0
-------------------------------------
SELECT /* FIND_ME 2 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2
FROM T1, T2 WHERE T1.C1(+)=T2.C1 AND T1.ROWID IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1261 (100)| 161K|00:00:00.20 | 15757 | | | |
| 1 | UNION-ALL | | 1 | | | | 161K|00:00:00.20 | 15757 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| 404 (2)| 150K|00:00:00.04 | 5122 | 3748K| 1935K| 5610K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.01 | 4893 | | | |
|* 5 | FILTER | | 1 | | | | 11001 |00:00:00.12 | 10635 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 139K| 4511K| 857 (1)| 149K|00:00:00.04 | 10635 | 3748K| 1935K| 5611K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 139K| 3828K| 383 (1)| 139K|00:00:00.01 | 4743 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1".ROWID IS NULL)
6 - access("T1"."C1"="T2"."C1")
8 - filter("T1"."C1" IS NOT NULL)
-
SQL_ID 4v4r3dau0barp, child number 0
-------------------------------------
SELECT /* FIND_ME 3 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE T1.C1=T2.C1(+) UNION SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2
WHERE T1.C1(+)=T2.C1
Plan hash value: 2719845061
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2823 (100)| 111K|00:00:00.49 | 15607 | | | |
| 1 | SORT UNIQUE | | 1 | 289K| 5947K| 8544K| 2823 (58)| 111K|00:00:00.49 | 15607 | 6998K| 1589K| 6220K (0)|
| 2 | UNION-ALL | | 1 | | | | | 299K|00:00:00.04 | 15607 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| | 404 (2)| 150K|00:00:00.04 | 4972 | 3748K| 1935K| 5611K (0)|
| 4 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| | 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| | 383 (1)| 150K|00:00:00.01 | 4743 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 139K| 2871K| | 857 (1)| 149K|00:00:00.04 | 10635 | 3748K| 1935K| 5580K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| | 472 (1)| 100K|00:00:00.01 | 5892 | | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 139K| 2187K| | 383 (1)| 139K|00:00:00.01 | 4743 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
8 - filter("T1"."C1" IS NOT NULL)
-
SQL_ID gs96s7s3ff9sp, child number 0
-------------------------------------
SELECT /* FIND_ME 4 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C1
Plan hash value: 2431666783
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 905 (100)| 161K|00:00:00.20 | 11402 | | | |
| 1 | VIEW | | 1 | 160K| 5156K| 905 (2)| 161K|00:00:00.20 | 11402 | | | |
| 2 | UNION-ALL | | 1 | | | | 161K|00:00:00.20 | 11402 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| 404 (2)| 150K|00:00:00.04 | 5122 | 3748K| 1935K| 5610K (0)|
| 4 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 229 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.01 | 4893 | | | |
|* 6 | HASH JOIN ANTI | | 1 | 10001 | 97K| 501 (2)| 11001 |00:00:00.09 | 6280 | 3748K| 1935K| 5547K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
|* 8 | INDEX FAST FULL SCAN| IND_T1_C1 | 1 | 139K| 683K| 27 (4)| 139K|00:00:00.01 | 388 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
8 - filter("T1"."C1" IS NOT NULL)
So, why did I see a different execution plan on Oracle Database 11.2.0.1? Let’s take a look at a 10053 trace file from 10.2.0.5:
SET AUTOTRACE TRACEONLY STATISTICS ALTER SESSION SET TRACEFILE_IDENTIFIER='ANSI_TRANSFORM_FULL'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /* FIND_ME 5 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C1; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
Inside the trace file I found this (slightly reformatted to improve readability):
******* UNPARSED QUERY IS *******
SELECT
"from$_subquery$_003"."C1" "C1",
"from$_subquery$_003"."C1" "C1",
"from$_subquery$_003"."C2" "C2"
FROM
((SELECT
"T1"."C1" "QCSJ_C000000000300000",
"T1"."C2" "QCSJ_C000000000300002",
"T2"."C1" "C1",
"T2"."C2" "C2"
FROM
"TESTUSER"."T1" "T1",
"TESTUSER"."T2" "T2"
WHERE
"T1"."C1"="T2"."C1"(+))
UNION ALL
(SELECT
NULL,
NULL,
"T2"."C1" "C1",
"T2"."C2" "C2"
FROM
"TESTUSER"."T1" "T1",
"TESTUSER"."T2" "T2"
WHERE
"T1"."C1"="T2"."C1"
AND "T1"."C1" IS NOT NULL)) "from$_subquery$_003"
kkoqbc-subheap (delete addr=000000001602C530, in-use=11064, alloc=21776)
kkoqbc-end
: call(in-use=83648, alloc=131128), compile(in-use=123584, alloc=166568)
apadrv-end: call(in-use=83648, alloc=131128), compile(in-use=125480, alloc=166568)
sql_id=7jhmvz6nutvpv.
Current SQL statement for this session:
SELECT /* FIND_ME 5 */
T1.C1,
T2.C1,
T1.C2
FROM
T1 FULL OUTER JOIN T2
ON T1.C1=T2.C1
============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 905 | |
| 1 | VIEW | | 156K | 5156K | 905 | 00:00:08 |
| 2 | UNION-ALL | | | | | |
| 3 | HASH JOIN RIGHT OUTER | | 146K | 3076K | 404 | 00:00:04 |
| 4 | INDEX FAST FULL SCAN | IND_T2_C1| 98K | 488K | 20 | 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 146K | 2344K | 383 | 00:00:04 |
| 6 | HASH JOIN ANTI | | 10K | 98K | 501 | 00:00:05 |
| 7 | TABLE ACCESS FULL | T2 | 98K | 488K | 472 | 00:00:04 |
| 8 | INDEX FAST FULL SCAN | IND_T1_C1| 137K | 684K | 27 | 00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
8 - filter("T1"."C1" IS NOT NULL)
I guess that query transformation explains the execution plan – it looks like Oracle Database 10.2.0.5 will transform an ANSI full outer join into Oracle specific syntax. Let’s repeat the test on Oracle Database 11.2.0.1 to see what happens:
SQL_ID cfjjpmdc0ghsx, child number 0
-------------------------------------
SELECT /* FIND_ME 1 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE
T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2 FROM T1,
T2 WHERE T1.C1(+)=T2.C1 AND T1.C1 IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 998 (100)| 100K|00:00:00.21 | 12484 | | | |
| 1 | UNION-ALL | | 1 | | | | 100K|00:00:00.21 | 12484 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| 273 (2)| 100K|00:00:00.09 | 3461 | 3749K| 1936K| 5799K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 230 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.02 | 3231 | | | |
|* 5 | FILTER | | 1 | | | | 0 |00:00:00.09 | 9023 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 1 | 21 | 725 (1)| 100K|00:00:00.09 | 9023 | 3749K| 1936K| 5800K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.01 | 3131 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1"."C1" IS NULL)
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID 1qtvpf3mnb3nx, child number 0
-------------------------------------
SELECT /* FIND_ME 2 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE
T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2 FROM T1,
T2 WHERE T1.C1(+)=T2.C1 AND T1.ROWID IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 998 (100)| 100K|00:00:00.21 | 12484 | | | |
| 1 | UNION-ALL | | 1 | | | | 100K|00:00:00.21 | 12484 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| 273 (2)| 100K|00:00:00.09 | 3461 | 3749K| 1936K| 5799K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 230 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.02 | 3231 | | | |
|* 5 | FILTER | | 1 | | | | 0 |00:00:00.09 | 9023 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 100K| 3222K| 725 (1)| 100K|00:00:00.09 | 9023 | 3749K| 1936K| 5800K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 100K| 2734K| 252 (1)| 100K|00:00:00.01 | 3131 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1".ROWID IS NULL)
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID 4v4r3dau0barp, child number 0
-------------------------------------
SELECT /* FIND_ME 3 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE
T1.C1=T2.C1(+) UNION SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2
WHERE T1.C1(+)=T2.C1
Plan hash value: 2719845061
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2078 (100)| 100K|00:00:00.30 | 12384 | | | |
| 1 | SORT UNIQUE | | 1 | 200K| 4101K| 5913K| 2078 (62)| 100K|00:00:00.30 | 12384 | 6360K| 1340K| 5653K (0)|
| 2 | UNION-ALL | | 1 | | | | | 200K|00:00:00.21 | 12384 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K| 2050K| | 273 (2)| 100K|00:00:00.08 | 3361 | 3749K| 1936K| 5800K (0)|
| 4 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| | 20 (5)| 100K|00:00:00.01 | 230 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| | 252 (1)| 100K|00:00:00.01 | 3131 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 100K| 2050K| | 725 (1)| 100K|00:00:00.09 | 9023 | 3749K| 1936K| 5743K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| | 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| | 252 (1)| 100K|00:00:00.01 | 3131 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID gs96s7s3ff9sp, child number 0
-------------------------------------
SELECT /* FIND_ME 4 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER
JOIN T2 ON T1.C1=T2.C1
Plan hash value: 3807180574
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 725 (100)| 100K|00:00:00.11 | 9123 | | | |
| 1 | VIEW | VW_FOJ_0 | 1 | 100K| 3222K| 725 (1)| 100K|00:00:00.11 | 9123 | | | |
|* 2 | HASH JOIN FULL OUTER| | 1 | 100K| 2050K| 725 (1)| 100K|00:00:00.10 | 9123 | 3749K| 1936K| 5799K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 1562K| 252 (1)| 100K|00:00:00.02 | 3231 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
Notice the difference in the ANSI full outer join execution plan. Now the second half of the output:
SQL_ID cfjjpmdc0ghsx, child number 0
-------------------------------------
SELECT /* FIND_ME 1 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE
T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2 FROM T1,
T2 WHERE T1.C1(+)=T2.C1 AND T1.C1 IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1261 (100)| 161K|00:00:00.28 | 15732 | | | |
| 1 | UNION-ALL | | 1 | | | | 161K|00:00:00.28 | 15732 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| 404 (2)| 150K|00:00:00.12 | 5110 | 3749K| 1936K| 5671K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 230 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.03 | 4880 | | | |
|* 5 | FILTER | | 1 | | | | 11001 |00:00:00.12 | 10622 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 9334 | 191K| 856 (1)| 149K|00:00:00.11 | 10622 | 3749K| 1936K| 5672K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 8 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.02 | 4730 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1"."C1" IS NULL)
6 - access("T1"."C1"="T2"."C1")
-
SQL_ID 1qtvpf3mnb3nx, child number 0
-------------------------------------
SELECT /* FIND_ME 2 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE
T1.C1=T2.C1(+) UNION ALL SELECT T1.C1, T2.C1, T1.C2 FROM T1,
T2 WHERE T1.C1(+)=T2.C1 AND T1.ROWID IS NULL
Plan hash value: 2463665226
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1261 (100)| 161K|00:00:00.27 | 15732 | | | |
| 1 | UNION-ALL | | 1 | | | | 161K|00:00:00.27 | 15732 | | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| 404 (2)| 150K|00:00:00.12 | 5110 | 3749K| 1936K| 5671K (0)|
| 3 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| 20 (5)| 100K|00:00:00.01 | 230 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.02 | 4880 | | | |
|* 5 | FILTER | | 1 | | | | 11001 |00:00:00.12 | 10622 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 139K| 4511K| 857 (1)| 149K|00:00:00.11 | 10622 | 3749K| 1936K| 5735K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 139K| 3828K| 383 (1)| 139K|00:00:00.02 | 4730 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
5 - filter("T1".ROWID IS NULL)
6 - access("T1"."C1"="T2"."C1")
8 - filter("T1"."C1" IS NOT NULL)
-
SQL_ID 4v4r3dau0barp, child number 0
-------------------------------------
SELECT /* FIND_ME 3 */ T1.C1, T2.C1, T1.C2 FROM T1, T2 WHERE
T1.C1=T2.C1(+) UNION SELECT T1.C1, T2.C1, T1.C2 FROM T1, T2
WHERE T1.C1(+)=T2.C1
Plan hash value: 2719845061
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2823 (100)| 111K|00:00:00.47 | 15582 | | | |
| 1 | SORT UNIQUE | | 1 | 289K| 5947K| 8544K| 2823 (58)| 111K|00:00:00.47 | 15582 | 7990K| 1681K| 7102K (0)|
| 2 | UNION-ALL | | 1 | | | | | 299K|00:00:00.28 | 15582 | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 1 | 150K| 3076K| | 404 (2)| 150K|00:00:00.11 | 4960 | 3749K| 1936K| 5671K (0)|
| 4 | INDEX FAST FULL SCAN| IND_T2_C1 | 1 | 100K| 488K| | 20 (5)| 100K|00:00:00.01 | 230 | | | |
| 5 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| | 383 (1)| 150K|00:00:00.02 | 4730 | | | |
|* 6 | HASH JOIN OUTER | | 1 | 139K| 2871K| | 857 (1)| 149K|00:00:00.11 | 10622 | 3749K| 1936K| 5640K (0)|
| 7 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| | 472 (1)| 100K|00:00:00.01 | 5892 | | | |
|* 8 | TABLE ACCESS FULL | T1 | 1 | 139K| 2187K| | 383 (1)| 139K|00:00:00.02 | 4730 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
6 - access("T1"."C1"="T2"."C1")
8 - filter("T1"."C1" IS NOT NULL)
-
SQL_ID gs96s7s3ff9sp, child number 0
-------------------------------------
SELECT /* FIND_ME 4 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER
JOIN T2 ON T1.C1=T2.C1
Plan hash value: 3807180574
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 856 (100)| 161K|00:00:00.14 | 10772 | | | |
| 1 | VIEW | VW_FOJ_0 | 1 | 150K| 4833K| 856 (1)| 161K|00:00:00.14 | 10772 | | | |
|* 2 | HASH JOIN FULL OUTER| | 1 | 150K| 3076K| 856 (1)| 161K|00:00:00.13 | 10772 | 3749K| 1936K| 5671K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 100K| 488K| 472 (1)| 100K|00:00:00.01 | 5892 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 150K| 2343K| 383 (1)| 150K|00:00:00.02 | 4880 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
Again, notice that the execution plan for the ANSI full outer join is different. Let’s take a look at a 10053 trace for the SQL statement:
SET AUTOTRACE TRACEONLY STATISTICS ALTER SESSION SET TRACEFILE_IDENTIFIER='ANSI_TRANSFORM_FULL'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /* FIND_ME 5 */ T1.C1, T2.C1, T1.C2 FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C1; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
Let’s take a look in the 10053 trace file:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT
"VW_FOJ_0"."QCSJ_C000000000300000_0" "C1",
"VW_FOJ_0"."QCSJ_C000000000300001_2" "C1",
"VW_FOJ_0"."QCSJ_C000000000300002_1" "C2"
FROM
(SELECT
"T1"."C1" "QCSJ_C000000000300000_0",
"T1"."C2" "QCSJ_C000000000300002_1",
"T2"."C1" "QCSJ_C000000000300001_2"
FROM
"TESTUSER"."T2" "T2" FULL OUTER JOIN "TESTUSER"."T1" "T1"
ON "T1"."C1"="T2"."C1") "VW_FOJ_0"
...
Starting SQL statement dump
user_id=194 user_name=TESTUSER module=SQL*Plus action=
sql_id=7jhmvz6nutvpv plan_hash_value=-487786722 problem_type=3
----- Current SQL Statement for this session (sql_id=7jhmvz6nutvpv) -----
SELECT /* FIND_ME 5 */
T1.C1,
T2.C1,
T1.C2
FROM
T1 FULL OUTER JOIN T2
ON T1.C1=T2.C1
sql_text_length=98
sql=SELECT /* FIND_ME 5 */
T1.C1,
T2.C1,
T1.C2
FROM
T1 FULL OUTER JOIN T2
ON T1.C1=T2.C1
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 856 | |
| 1 | VIEW | VW_FOJ_0| 146K | 4834K | 856 | 00:00:07 |
| 2 | HASH JOIN FULL OUTER | | 146K | 3076K | 856 | 00:00:07 |
| 3 | TABLE ACCESS FULL | T2 | 98K | 488K | 472 | 00:00:04 |
| 4 | TABLE ACCESS FULL | T1 | 146K | 2344K | 383 | 00:00:04 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."C1"="T2"."C1")
I guess that explains why the execution plan looks a bit different from the version produced by 10.2.0.5.
Anyone care to try this test case on 9i R1, 9i R2, 10g R1, or 11.2.0.2? I am curious to know if we will trip over one of those ANSI bugs that are building a fort in my front lawn (bang head on desk until this makes sense… ants, see! 🙂 ).
——————–
Edit: December 30, 2010 Speaking of bugs, a quick look through Metalink:
- Doc ID 9395765.8, Bug 9395765 “ORA-907 / wrong plan from query with ANSI FULL OUTER join”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 8432019.8, Bug 8432019 “Wrong Results With Full Outer Join, Database Link and Merged Inline View”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 8538448.8, Bug 8538448 “OERI [qcsFixQbcnlo2] from FULL OUTER JOIN with UNION ALL”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 8947490.8, Bug 8947490 “OERI[15160] using SQL tuning advisor for full outer joined UNION”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9337832.8, Bug 9337832 “Wrong results from ANSI outer joins”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9882805.8, Bug 9882805 “Wrong results with NATIVE FULL OUTER JOIN and NVL”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 7144891.8, Bug 7144891 “OERI [qctopn1] from nested FULL OUTER JOIN”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 6471020.8, Bug 6471020 “ORA-1790 or ORA-904 from FULL JOIN”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 8782089.8, Bug 8782089 “OERI [15160] on ANSI join”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9050716.8, Bug 9050716 “Dumps on kkqstcrf with ANSI joins and Join Elimination”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9149005.8, Bug 9149005 “ANSI left join query with domain index fails with OERI [qxopq_get_rowid:1]”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9256994.8, Bug 9256994 “ORA-1792 using ANSI joins”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9360157.8, Bug 9360157 “Wrong results using ORA_ROWSCN pseudocolumn with ANSI join”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9371323.8, Bug 9371323 “OERI [rwoirw: check ret val] on INSERT ALL with ANSI join”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9387924.8, Bug 9387924 “OERI [qxopq_get_rowid:1] from ANSI join and operator”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9713012.8, Bug 9713012 “Wrong results from push predicate on ANSI SQL”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 9189070.8, Bug 9189070 “Dump (kkoiqb) parsing ANSI SQL with disjunctive predicate in “ON” clause”, affects Oracle Database release versions below 11.2.0.2
- Doc ID 4459936.8, Bug 4459936 “FGA does not work correctly with ANSI outer joins”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 4871892.8, Bug 4871892 “Wrong results from “left outer join” with TABLE() expressions”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 5183871.8, Bug 5183871 “ORA-904 using ORA_ROWSCN in ANSI SQL”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7429090.8, Bug 7429090 “Poor plan generated for a OUTER join query involving a LATERAL view”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7442232.8, Bug 7442232 “WRONG RESULTS (missing rows) WITH NATIVE FULL OUTER JOIN”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7001990.8, Bug 7001990 “Wrong result (null column) on ansi outer join query and index access”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 8547175.8, Bug 8547175 “FGA does not work correctly with ANSI joins”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7420504.8, Bug 7420504 “Wrong Results with ANSI queries and fix for bug 7345484”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7375360.8, Bug 7375360 “Repeat execution of uncorrelated subquery in ANSI join”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7345484.8, Bug 7345484 “Left outer join with filters are slow”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 7318276.8, Bug 7318276 “False ORA-918 with fix for bug 5368296 installed”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 5368296.8, Bug 5368296 “ANSI join SQL may not report ORA-918 for ambiguous column”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 5358967.8, Bug 5358967 “ORA-2019 on CREATE VIEW after CURRENT_SCHEMA is changed”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 5031632.8, Bug 5031632 “Wrong results from NATURAL JOIN”, affects Oracle Database release versions below 10.2.0.5
- Doc ID 8971835.8, Bug 8971835 “Assorted Dumps / ORA-932 / Wrong results when accessing over 1000 columns”, affects Oracle Database release versions below 10.2.0.5
I will suggest that the hidden parameter _OPTIMIZER_NATIVE_FULL_OUTER_JOIN likely controls whether or not an ANSI full outer join is transformed into Oracle specific syntax. The script in this blog article shows that the default value of this parameter is FORCE on Oracle Database 11.2.0.1.
—–
Edit: Jan 1, 2011:
One more ANSI bug that affects 11.2.0.1 and below – thanks to Coskan for reporting the bug (and for beta testing the ANSI functionality in the production release of 11.2.0.1 🙂 ):
- Doc ID 9287401.8, Bug 9287401 “Full outer join does not push down predicates”, affects Oracle Database release versions below 11.2.0.2
Charles Hooper's Oracle Notes 
Just an addition to the mos bug list we hit bug 9287401 FULL OUTER JOIN DOES NOT LEVERAGE PREDICATE PUSHING OR JOIN CONVERSION.
Thank you for mentioning bug 9287401 – I completely missed that bug.