原创 转载请注明出处

HASH 连接的一个特列
同一张表的不同索引可以做HASH连接来代替FULL TABLE SCAN，这种索引连接也只能勇HASH JION的方式实现，
并且查询较少的字段的时候，可以勇到。所以如果看到一个表却出现的连接，大家也不要吃惊了。列子如下
create table T4
(
  ID    NUMBER not null,
  T3_ID NUMBER not null,
  N     NUMBER,
  PAD   VARCHAR2(4000)
);
alter table T4
  add constraint T4_PK primary key (ID);
create index T4_N on T4 (N);

SQL> explain plan for
  2 
  2  SELECT /*+ index_join(t4 t4_n t4_pk) */ id, n
  3  FROM t4
  4  WHERE id BETWEEN 10 AND 20
  5  AND n < 100;
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1741362252
--------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |     1 |    26 |     8  (38)|
|*  1 |  VIEW                  | index$_join$_001 |     1 |    26 |     8  (38)|
|*  2 |   HASH JOIN            |                  |       |       |            |
|*  3 |    INDEX FAST FULL SCAN| T4_PK            |     1 |    26 |     4  (25)|
|*  4 |    INDEX FAST FULL SCAN| T4_N             |     1 |    26 |     4  (25)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">=10 AND "ID"<=20 AND "N"<100)
   2 - access(ROWID=ROWID)
   3 - filter("ID"<=20 AND "ID">=10)
   4 - filter("N"<100)
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
 
23 rows selected
 
 可以看到一张表出现了HASH JION