原创 转载请注明出处
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
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" 2 - access(ROWID=ROWID)
3 - filter("ID"=10)
4 - filter("N"
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
23 rows selected
可以看到一张表出现了HASH JION
