oracle表连接-hashjoin哈希连接 - 数据库编程

ved via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> select /*+ leading(t3) use_merge(t4) */* from t3, t4 where t3.id = t4.t3_id; 10000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3831111046 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10000 | 1250K| | 193 (2)| 00:00:03 | | 1 | MERGE JOIN | | 10000 | 1250K| | 193 (2)| 00:00:03 | | 2 | SORT JOIN | | 1000 | 63000 | | 6 (17)| 00:00:01 | | 3 | TABLE ACCESS FULL| T3 | 1000 | 63000 | | 5 (0)| 00:00:01 | |* 4 | SORT JOIN | | 10000 | 634K| 1592K| 187 (1)| 00:00:03 | | 5 | TABLE ACCESS FULL| T4 | 10000 | 634K| | 29 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("T3"."ID"="T4"."T3_ID") filter("T3"."ID"="T4"."T3_ID") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 119 consistent gets 0 physical reads 0 redo size 344114 bytes sent via SQL*Net to client 7745 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 10000 rows processed

从上面的执行计划可以看出:

?	排序次数	逻辑读	CPU Time
hash join	0	779	00：01
nested loops	0	12605	02：13
merge join	2	119	00：03

可见，oracle 引入的 hash 连接, 能够解决嵌套循环连接中大量随机读的问题, 同时解决了排序合并连接中排序代价过大的问题.

三. hash 连接优化

SQL> alter session set statistics_level=ALL;
SQL> select /*+ leading(t3) use_hash(t4) */* from t3, t4 where t3.id = t4.t3_id and t3.n = 1100;


10 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------


SQL_ID  f57pu4khtptsc, child number 0
-------------------------------------
select /*+ leading(t3) use_hash(t4) */* from t3, t4 where t3.id =
t4.t3_id and t3.n = 1100


Plan hash value: 1396201636


----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.03 |     120 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     10 |     10 |00:00:00.03 |     120 |   737K|   737K|  389K (0)|
|*  2 |   TABLE ACCESS FULL| T3   |      1 |      1 |      1 |00:00:00.01 |      15 |       |       |          |
|   3 |   TABLE ACCESS FULL| T4   |      1 |  10000 |  10000 |00:00:00.01 |     105 |       |       |          |
----------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
--------------------

oracle表连接-hashjoin哈希连接(三)