Oracle 11g R2 full outer join Optimization execution Plan (iii) Native_full_outer_join tips

Although the previous article introduced the Native_full_outer_join and no_native_full_outer_join two hint, but in fact Native_full_outer_join did not play any role, Because Oracle's optimization of the full outer join makes the new execution plan less costly than the original execution plan, Oracle chooses the execution plan by default, so it does not see the effect of the native_full_outer_join hint.

Sql> SET Autot on

Sql> SELECT T1.id, t2.id

2 from T1 full OUTER JOIN T2

3 on t1.id = T2.id;

ID ID

--------------------

2 2

3 3

4 4

5 5

6 6

7 7

8 8

9

10

1

0

11 rows have been selected.

Execution plan

----------------------------------------------------------

Plan Hash value:53297166

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------

| 0 |          SELECT STATEMENT |     |   9 |     234 | 9 (12) | 00:00:01 |

|  1 | VIEW |     Vw_foj_0 |   9 |     234 | 9 (12) | 00:00:01 |

|* 2 |          HASH JOIN Full outer|     |   9 |     234 | 9 (12) | 00:00:01 |

This column more highlights: http://www.bianceng.cn/database/Oracle/

|    3 | TABLE ACCESS Full |     T1 |   9 |     117 | 4 (0) | 00:00:01 |

|    4 | TABLE ACCESS Full |     T2 |   9 |     117 | 4 (0) | 00:00:01 |

----------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("T1".) ID "=" T2 "." ID ")

Note

-----

-Dynamic sampling used for this statement (level=2)

Statistical information

----------------------------------------------------------

0 Recursive calls

0 db Block gets

Consistent gets

0 physical Reads

0 Redo Size

733 Bytes sent via sql*net to client

520 bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

Rows processed

Attempt to execute in Rbo case:

Sql> ALTER session SET Optimizer_mode = CHOOSE;

The session has changed.

Sql> SELECT T1.id, t2.id

2 from T1 full OUTER JOIN T2

3 on t1.id = T2.id;

ID ID

--------------------

2 2