View Source

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

h3. 기본 Mechanism
Index Join 의 기본적인 Mechanism 은 Index 끼리 Hash Join 을 수행한다는 것이다.
아래와 같은 실행 계획을 가정해 보자.
{code:SQL}
---------------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------------
{code}
- 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
...

h3. Hints and Parameters

Index Join 은 INDEX_JOIN Hint 를 통해서 제어한다.
{code:SQL}
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 ...
{code}

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

{code:SQL}
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
{code}

h2. 문서에 대하여

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