34.读书笔记收获不止Oracle之表连接场景对比

34.读书笔记收获不止Oracle之表连接场景对比

每个连接方式都有自己的使用场景和本身的限制。

试验中可以使用HINT，use_nl,use_hash,use_merge 来实现表的不同连接方式。

1.  哈希连接限制

SQL>alter session set statistics_level=all;

SQL>explain plan for

select /*+leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id<>t2.t1_idand t1.n=19;

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1967407726

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

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

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

|   0| SELECT STATEMENT   |      |99999 |    11M|   276  (1)| 00:00:01 |

|   1|  NESTED LOOPS     |          | 99999 |    11M|  276   (1)| 00:00:01 |

|*  2|   TABLE ACCESS FULL| T1   |         1|    57 |            3   (0)| 00:00:01 |

|*  3|   TABLE ACCESS FULL| T2   | 99999 | 6445K|   273   (1)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified byoperation id):

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

   2- filter("T1"."N"=19)

   3- filter("T1"."ID"<>"T2"."T1_ID")

16 rows selected.

明明HINT是走 HASH连接，最后走的却是NL。说明HASH连接不支持 <>连接。

此外HASH连接不支持大于和小于的写法，也不支持LIKE的连接方式。

2.  排序合并限制

SQL>explain plan for

select /*+leading(t1) use_merge(t2)*/ * from t1,t2 wheret1.id<>t2.t1_id and t1.n=19;

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1967407726

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

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

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

|   0 | SELECT STATEMENT   |              | 99999 |   11M|   276   (1)| 00:00:01 |

|   1 |  NESTED LOOPS             |          | 99999 |   11M|   276   (1)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| T1   |                1|    57 |            3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| T2   | 99999 | 6445K|   273   (1)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

   2 -filter("T1"."N"=19)

   3 -filter("T1"."ID"<>"T2"."T1_ID")

16 rows selected.

发现走的也是NL。

排序合并连接不支持<>的连接条件，不支持LIKE的连接条件，但是比起HASH 连接，支持>之类的连接条件。

3.  嵌套循环连接限制

嵌套循环支持所有SQL连接条件写法，没有任何限制。

哈希连接和排序连接不支持的，都自动走嵌套循环连接了。