浅述out join和（+）两者语法区别

SQL> show parameter op

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
filesystemio_options                 string      none
max_commit_propagation_delay         integer     0
object_cache_optimal_size            integer     102400
open_cursors                         integer     300
open_links                           integer     4
open_links_per_instance              integer     4
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      CHOOSE
optimizer_secure_view_merging        boolean     TRUE
plsql_optimize_level                 integer     2
read_only_open_delayed               boolean     FALSE
session_max_open_files               integer     10
SQL> show parameter sample
SQL> show parameter dy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
SQL> alter system set optimizer_dynamic_sampling=0;

System altered.

SQL> conn zhou/zhou
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TTT
STATTABLE
T
TT

SQL> select table_name,num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TTT                                    14
STATTABLE                              45
T                                   57241
TT                                   1010

SQL> exec DBMS_STATS.DELETE_TABLE_STATS( ownname=>user,tabname=>'T');

PL/SQL procedure successfully completed.

SQL>
SQL> exec DBMS_STATS.DELETE_TABLE_STATS( ownname=>user,tabname=>'TT');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TTT                                    14
STATTABLE                              45
T
TT




SQL> explain plan for select t.obj# from t left join tt on t.obj#=tt.obj#;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3717044335

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

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

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         | 57340 |  1455K|    13   (8)| 00:00:01
|

|*  1 |  HASH JOIN RIGHT OUTER|         | 57340 |  1455K|    13   (8)| 00:00:01
|

|   2 |   TABLE ACCESS FULL   | TT      |  1307 | 16991 |     5   (0)| 00:00:01
|

|   3 |   INDEX FAST FULL SCAN| T_INDEX | 57340 |   727K|     7   (0)| 00:00:01
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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


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

   1 - access("T"."OBJ#"="TT"."OBJ#"(+))

15 rows selected.



SQL> explain plan for select t.obj# from t,tt where t.obj#=tt.obj#(+);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3991874491

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  MERGE JOIN OUTER   |      |
|   2 |   SORT JOIN         |      |
|   3 |    TABLE ACCESS FULL| T    |
|*  4 |   SORT JOIN         |      |
|   5 |    TABLE ACCESS FULL| TT   |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------

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

   4 - access("T"."OBJ#"="TT"."OBJ#"(+))
       filter("T"."OBJ#"="TT"."OBJ#"(+))

Note
-----
   - rule based optimizer used (consider using cbo)

22 rows selected.