index scan join的使用场景测试

index join scan是一种数据的访问方式，是通过索引访问数据的一种方式，下面我们测试改方式如何实现方式以及实现场景。
后我们分析这种方式的hash特点。
我们创建两个测试表
SQL> create table a as select * from all_objects
Table created.
在对应字段创建索引
SQL> create index idx_a_id on a(object_id);
Index created.
SQL> create index idx_a_name on a(object_name);
Index created.

SQL> explain plan for
2 select /*+ index_join(a) */ a.object_id,object_name from a where a.object_id<21;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3052619984

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 656 | 466 (1)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 16 | 656 | 466 (1)| 00:00:01 |
|* 2 | HASH JOIN | | | | |
|* 3 | INDEX RANGE SCAN | IDX_A_ID | 16 | 656 | 2 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_A_NAME | 16 | 656 | 579 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."OBJECT_ID"<21)
2 - access(ROWID=ROWID)
3 - access("A"."OBJECT_ID"<21)

18 rows selected.
我们从执行计划过程以及谓词过滤信息可以清楚知道其执行过程
首先执行的第三步，通过所以idx_a_id过滤<21的数据，从得到的数据张取得rowid做哈希计算
第四步中索引idx_a_name中的rowid的哈希计算做匹配与第三步的中结果做匹配，从而返回
object_id 和对应的object_name字段给用户。
这个查询有如下特点：
1 数据都在索引中。
2 通过Rowid做哈希计算

注意：本次为了讨论方便我们使用了hint，否则不见得一定走hash scan jion操作。
如果数据量小，很可能是如下执行计划
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2120771631

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 656 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| A | 16 | 656 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_A_ID | 16 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
2 - access("A"."OBJECT_ID"<21)
14 rows selected.