index和index之间是如何进行hash join的，因为index中包括了rowid，所以通过rowid可以连接。

Index Joins

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation. The index join is available only with the CBO.

Index Join Hints

You can specify an index join with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_JOIN hint. For more information on the INDEX_JOIN hint, see "INDEX_JOIN".

[@more@]

SQL> select index_name,table_name,column_name from user_ind_columns
2 where table_name='T';

INDEX_NAME TABLE_NAME COLUMN_NAME
---------- ---------- --------------------
IDX1_TT T OBJECT_NAME
IDX_T T OBJECT_ID

已用时间: 00: 00: 00.00
SQL>

SQL> select /*+ index_join(t idx_t idx1_tt) */ object_id , object_name from t wh
ere object_name like 'USER_%';
已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 40406431

--------------------------------------------------------------------------------

-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

Time |

--------------------------------------------------------------------------------

-----------

| 0 | SELECT STATEMENT | | 120K| 9277K| 4740 (1)|

00:00:57 |

|* 1 | VIEW | index$_join$_001 | 120K| 9277K| 4740 (1)|

00:00:57 |

|* 2 | HASH JOIN | | | | |

|

|* 3 | INDEX RANGE SCAN | IDX1_TT | 120K| 9277K| 1447 (1)|

00:00:18 |

| 4 | INDEX FAST FULL SCAN| IDX_T | 120K| 9277K| 33 (0)|

00:00:01 |

--------------------------------------------------------------------------------

-----------

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

1 - filter("OBJECT_NAME" LIKE 'USER_%')
2 - access(ROWID=ROWID)
3 - access("OBJECT_NAME" LIKE 'USER_%')

Note
-----
- dynamic sampling used for this statement