left join 变 inner join

前面部分请看
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