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 连接，支持>之类的连接条件。
