Analysis and Handling of Oracle full outer join bug

Full (outer) join is the syntax used to fully connect two tables. That is to say, if you want to associate Table A with table B, you can obtain records that exist in Table A but not in Table B, or that exist in Table B but not in table.

The on clause is used to determine whether the record exists.

The following is an example:

SQL>
2 A as (select 1 a, 2 B from dual ),
3 B as (select 2 a, 3 B from dual)
4 select * from A full join B
5 on A. a = B.
6/
 
A B
----------------------------------------
1 2
2 3
 

After understanding the above basic principles, we should know that theoretically, the order of table A and table B in the from clause is irrelevant, that is, the result is not affected. However, in fact, this problem occurs. The following describes the situation:

Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 12791 | 349K | 82 (3) | 00:00:01 |
| 1 | VIEW | 12791 | 349K | 82 (3) | 00:00:01 |
| 2 | UNION-ALL |
| * 3 | FILTER |
| * 4 | hash join right outer | 12790 | 1124K | 41 (3) | 00:00:01 |
| 5 | table access full | JXC_RISHARESUM | 1735 | 78075 | 7 (0) | 00:00:01 |
| 6 | table access full | JXC_ALLTRADEDAY | 12790 | 562K | 33 (0) | 00:00:01 |
| * 7 | hash join anti | 1 | 76 | 41 (3) | 00:00:01 |
| * 8 | table access full | JXC_RISHARESUM | 1 | 45 | 7 (0) | 00:00:01 |
| 9 | table access full | JXC_ALLTRADEDAY | 12790 | 387K | 33 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------------

 

From the execution plan above, the fourth step uses the hash join rimo-outer connection method. By changing the placement order of the two tables, the following execution plan is obtained:

 

Bytes -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------
| 0 | select statement | 1876 | 52528 | 82 (3) | 00:00:01 |
| 1 | VIEW | 1876 | 52528 | 82 (3) | 00:00:01 |
| 2 | UNION-ALL |
| * 3 | FILTER |
| * 4 | hash join outer | 1874 | 164K | 41 (3) | 00:00:01 |
| 5 | table access full | JXC_RISHARESUM | 1735 | 78075 | 7 (0) | 00:00:01 |
| 6 | table access full | JXC_ALLTRADEDAY | 12790 | 562K | 33 (0) | 00:00:01 |
| * 7 | hash join anti | 2 | 152 | 41 (3) | 00:00:01 |
| * 8 | table access full | JXC_ALLTRADEDAY | 2 | 90 | 33 (0) | 00:00:01 |
| 9 | table access full | JXC_RISHARESUM | 1735 | 53785 | 7 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------

 

Note that the fourth step in the execution plan is changed to the hash join outer mode. This is what we expected. So what caused this change? View their predicate connection logic:

Hash join right outer:

3-filter (NVL ("T1". "C_SHARENO", "T2". "C_SHARENO") = '20140901 ')
4-access ("T2". "D_TRADEDATE" = "T1". "D_TRADEDATE" (+) AND
"T2". "D_REGDATE" = "T1". "D_REGDATE" (+) AND "T2". "C_FUNDCODE" = "T1". "C_FUNDCODE" (+) AND
"T2". "C_FUNDACCO" = "T1". "C_FUNDACCO" (+ ))
7-access ("T2". "C_FUNDACCO" = "T1". "C_FUNDACCO" AND
"T2". "C_FUNDCODE" = "T1". "C_FUNDCODE" AND "T2". "D_REGDATE" = "T1". "D_REGDATE" AND
"T2". "D_TRADEDATE" = "T1". "D_TRADEDATE ")
8-filter (NVL ("T1". "C_SHARENO", NULL) = '123 ')

 

Hash join outer:

3-filter (NVL ("T1". "C_SHARENO", "T2". "C_SHARENO") = '20140901 ')
4-access ("T2". "D_TRADEDATE" (+) = "T1". "D_TRADEDATE" AND
"T2". "D_REGDATE" (+) = "T1". "D_REGDATE" AND "T2". "C_FUNDCODE" (+) = "T1". "C_FUNDCODE"
AND "T2". "C_FUNDACCO" (+) = "T1". "C_FUNDACCO ")
7-access ("T2". "C_FUNDACCO" = "T1". "C_FUNDACCO" AND
"T2". "C_FUNDCODE" = "T1". "C_FUNDCODE" AND "T2". "D_REGDATE" = "T1". "D_REGDATE" AND
"T2". "D_TRADEDATE" = "T1". "D_TRADEDATE ")
8-filter ("T2". "C_SHARENO" = '000000 ')

No obvious difference was found. However, the results are different.

Restore to the original table connection sequence, analyze the two tables, and then view the execution plan:

SQL> call dbms_stats.gather_table_stats (user, 'jxc _ alltradeday ');

The call is complete.

SQL> call dbms_stats.gather_table_stats (user, 'jxc _ RISHARESUM ');

.....

Bytes -----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------
| 0 | select statement | 1738 | 48664 | 82 (3) | 00:00:01 |
| 1 | VIEW | 1738 | 48664 | 82 (3) | 00:00:01 |
| 2 | UNION-ALL |
| * 3 | FILTER |
| * 4 | hash join outer | 1735 | 191K | 41 (3) | 00:00:01 |
| 5 | table access full | JXC_RISHARESUM | 1735 | 98895 | 7 (0) | 00:00:01 |
| 6 | table access full | JXC_ALLTRADEDAY | 12775 | 698K | 33 (0) | 00:00:01 |
| * 7 | hash join anti | 3 | 276 | 41 (3) | 00:00:01 |
| * 8 | table access full | JXC_ALLTRADEDAY | 3 | 168 | 33 (0) | 00:00:01 |
| 9 | table access full | JXC_RISHARESUM | 1735 | 62460 | 7 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

3-filter (NVL ("T1". "C_SHARENO", "T2". "C_SHARENO") = '20140901 ')
4-access ("T2". "D_TRADEDATE" (+) = "T1". "D_TRADEDATE" AND
"T2". "D_REGDATE" (+) = "T1". "D_REGDATE" AND "T2". "C_FUNDCODE" (+) = "T1". "C_FUNDCODE"
AND "T2". "C_FUNDACCO" (+) = "T1". "C_FUNDACCO ")
7-access ("T2". "C_FUNDACCO" = "T1". "C_FUNDACCO" AND
"T2". "C_FUNDCODE" = "T1". "C_FUNDCODE" AND "T2". "D_REGDATE" = "T1". "D_REGDATE" AND
"T2". "D_TRADEDATE" = "T1". "D_TRADEDATE ")
8-filter ("T2". "C_SHARENO" = '000000 ')

 

It can be found that the execution plan of the original connection method is corrected and changed to hash join outer connection.