Index Joins的一點測試！

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