SQL的外連線對內部表的限制

SQL在進行外連線操作時，where子句中不能對內部表的欄位進行限制，否則外連線會失效。

Oracle會將外連線直接轉換為內連線。

 

測試步驟如下：

1.建立測試表

SQL> create table t

  2  (a  varchar2(10),

  3   b  varchar2(10));

 

Table created

 

SQL>

SQL> create table s

  2  (a  varchar2(10),

  3   b  varchar2(10));

 

Table created

 

2．檢查外連線的執行計劃（假設連線條件為t.a=s.a）

SQL> explain plan for

  2  select t.a,s.*

  3    from t left outer join s on (t.a=s.a) ;

 

Explained

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

 

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------Plan hash value: 269431714

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    21 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     1 |    21 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T    |     1 |     7 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| S    |     1 |    14 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("T"."A"="S"."A"(+))

 

 

 

3.當對外部表進行條件限制時，注意執行計劃還是外連線的

SQL> explain plan for

  2  select t.a,s.*

  3    from t left outer join s on (t.a=s.a)

  4   where t.a='aa';

 

Explained

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 269431714

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    21 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     1 |    21 |     5  (20)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     7 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| S    |     1 |    14 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("T"."A"="S"."A"(+))

   2 - filter("T"."A"='aa')

   3 - filter("S"."A"(+)='aa')

 

 

4.當where子句中出現內部表的欄位條件限制時，注意執行計劃變化，已經不再是外連線，而是內連線了

SQL> explain plan for

  2  select t.a,s.*

  3    from t left outer join s on (t.a=s.a)

  4   where s.a='aa';

 

Explained

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 59042520

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    21 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    21 |     5  (20)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     7 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| S    |     1 |    14 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("T"."A"="S"."A")

   2 - filter("T"."A"='aa')

   3 - filter("S"."A"='aa')

Note

-----

 

PLAN_TABLE_OUTPUT

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

   - dynamic sampling used for this statement (level=2)

 

21 rows selected

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10009036/viewspace-1061284/，如需轉載，請註明出處，否則將追究法律責任。