-------
7d64k5stnc3sk 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash
cknub2x1sx8tn 0 select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2
2jhn0mg57v1tz 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%use_hash
036fyatp73h9n 0 select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id
SQL> select * from table(dbms_xplan.display_cursor('cknub2x1sx8tn',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cknub2x1sx8tn, child number 0
-------------------------------------
select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id and 1=2
Plan hash value: 487071653
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OM
--------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 |
|* 2 | HASH JOIN | | 0 | 100 | 0 |00:00:00.01 | 7
| 3 | TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 |
| 4 | TABLE ACCESS FULL| T2 | 0 | 100K| 0 |00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - access("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
25 rows selected
通过上面的两个执行计划可以得出结论:HASH连接中，驱动表和被驱动表都只会被访问1次或者0次。
|