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 과 비슷한 일을 하지만
효율적인 경우가 많다.

B*Tree Index Combination VS 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

문서에 대하여

• 최초작성일 : 2009년 2월 14일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*