Index Join

• Index Join 은 B*Tree Index Combination 이 Oracle 9i 에서 소개되기 전 ( 정확하게 말하면 _B_TREE_BITMAP_PLAN Parameter 의 값이 False 에서 True로 바뀌기 전)에 하나의 TAble 에 대해 여러 개의 Index 를 사용할 수 있는 유일한 방법이다.
• Index Join 은 다음과 같은 실행 계획으로 표현된다.

기본 Mechanism

• Index Join 의 기본적인 Mechanism 은 Index 끼리 Hash Join 을 수행한다는 것이다.
• 아래와 같은 실행 계획을 가정해 보자.

---------------------------------------------------------------------------------
| Id  | Operation                                           | Name              |
---------------------------------------------------------------------------------
|  1  | SORT AGGREGATE                                      |                   |
|  2  |  VIEW                                               | index$_join$_001  |
|  3  |    HASH JOIN                                        |                   |
|  4  |       HASH JOIN                                     |                   |
|  5  |        INDEX RANGE SCAN                             | T_BC1_I2          |
|  6  |        INDEX RANGE SCAN                             | T_BC1_I3          |
|  7  |     INDEX FAST FULL SCAN                            | T_BC1_I1          |
---------------------------------------------------------------------------------

• INDEX T_BC1_I2 와 INDEX T_BC1_I3 를 INDEX RANGE SCAN(5,6단계)으로 읽어서 HASH JOIN 을 이용해 Join 한다.(4 단계)
• 4 단계의 결과를 INDEX T_BC1_I1 을 INDEX FAST FULL SCAN(7단계)으로 읽으면서 HASH JOIN 한다. 그 결과는 INDEX$_JOIN$_001 이라는 Temporary Object 에 저장된다.

• Index Join 의 가장 큰 장점은 하나의 Table 에 대해 여러 개의 Index 를 사용할 수 있다는 것이다.
• 하지만 Oracle 의 의도와는 달리 Index Join 은 그다지 선호되지 않는 Scan 방식이다.

그 이유를 정리하면 다음과 같다.

• 무엇보다도 최적의 Index 를 설계했다면 대부분의 경우 하나의 Index 에 대한 Scan만으로 (가령 Index Range Scan) 원하는 성능을 이끌어 낼 수 있다.
• Index Join 은 Fetch 해야할 모든 Column 들이 적어도 하나의 Index 에 포함되어 있어야 수행 가능하다.
• Index Join 은 Hash Join 을 사용하기 때문에 Memory 의 추가적인 사용을 유발할 수 있다.
• 넓은 범위의 Index Scan 이 필요하다면 Physical I/O가 부가적으로 발생할 수 있다.
• 만일 넓은 범위의 Scan 이 필요하다면 하나의 Index 에 대한 Fast Full Scan 이 오히려 유리할 것이다.
• 좁은 범위의 Scan 만으로 원하는 Data 를 가져올 수 있다면 불필요하게 여러 개의 Index 를 읽을 필요가 없다.

• B*Tree Index Combination 기능이 추가됨으로써 Index Join 은 더욱 그 존재 의미를 잃게 되었다.
• B*Tree Index Combination 은 Index Join 과 비슷한 일을 하지만 효율적인 경우가 많다.

Hints and Parameters

• Index Join 은 INDEX_JOIN Hint 를 통해서 제어한다.

select /*+ index_join(t1) */ c1 from t1 ..
select /*+ index_join(t1 t1_n1 t1_n2) */ c1 from t1 ...
select /*+ index_join(t1 t1(c1) c1(c2) */ c1 from t1 ...

• Index Join 의 동작 여부는 _INDEX_JOIN_ENABLED Parameter 를 이용해서 제어할 수 있다.

SQL> @check_hidden_parameter.sql
Enter value for input_parameter: _index_join_enabled
old  15: a.ksppinm LIKE '&input_parameter'
new  15: a.ksppinm LIKE '_index_join_enabled'

Parameter                                                    Session Value                  Instance Value
------------------------------------------------------------ ------------------------------ ------------------------------
_index_join_enabled                                          TRUE                           TRUE