浅述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.