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

------------------------------- 1 - access("T3"."ID"="T4"."T3_ID") 2 - filter("T3"."N"=1100) 在表 T3 的谓词条件(n)上增加索引

SQL> create index t3_n on t3(n);


Index created.


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: 2452410886


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


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


   1 - access("T3"."ID"="T4"."T3_ID")
   3 - access("T3"."N"=1100)

从执行计划中可以看出 buffers 从 120 下降为 108，可见谓词条件上的索引能够减少 hash 连接的逻辑读

接下来，看看在等值连接条件下，小表(小的结果集)为驱动表，hash 连接和 nested loop 嵌套循环连接

SQL> select * from t3, t4 where t3.id = t4.t3_id and t3.n = 1100;


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


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


SQL_ID  c204pd6srpjfq, child number 0
-------------------------------------
select * from t3, t4 where t3.id = t4.t3_id and t3.n = 1100


Plan hash value: 2039660043


---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |     10 |00:00:00.01 |      29 |
|   1 |  NESTED LOOPS                |          |      1 |        |     10 |00:00:00.01 |      29 |
|   2 |   NESTED LOOPS               |          |      1 |     10 |     10 |00:00:00.01 |      19 |
|*  3 |    TABLE ACCESS FULL         | T3       |      1 |      1 |      1 |00:00:00.01 |      16 |
|*  4 |    INDEX RANGE SCAN          | T4_T3_ID |      1 |     10 |     10 |00:00:00.01 |       3 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T4       |     10 |     10 |     10 |00:00:00.01 |      10 |
-------------------------------------------------------------------------------------

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