前面部分请看
http://blog.csdn.net/jgmydsai/article/details/12055521#sql
建一新表
CREATE TABLE t3 AS SELECT * FROM l;
我们来看下 下面语句的plan,这是一个群友提现的现象
SQL> EXPLAIN PLAN FOR select * from l left join r on l.v=r.v inner join t3 on r.v=t3.v;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3567199480
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 188 | 9 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 188 | 9 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 4 | 144 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| L | 4 | 44 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| R | 4 | 100 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T3 | 4 | 44 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("R"."V"="T3"."V")
2 - access("L"."V"="R"."V")
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected
由plan可以看到语句由left join变成了inner join,或许有人没看明白,我们来把语句变换下
以上语句相当于
SELECT *
FROM (SELECT l.v AS lv, l.str AS lstr, r.v AS rv, r.str AS rstr
FROM l
LEFT JOIN r
ON l.v = r.v) r
INNER JOIN t3 ON r.rv = t3.v;
该语句相当于在r.v上增加了过滤条件,这时r.v不可能再为空。
所以语句就变为了inner join