h2.Index Join
Index Join?
B*Tree Index COmbination이 Oracle 9i에서소개되기 전에 하나의 Table에 대해
여러개의 Index를 사용할 수 있는 유일한 방법이다.
Index Join의 가장 큰 장점은 하나의 Table에 대해 여러 개의 Index를 사용할수 있다는것이다.
하지만, 오라클의 의도와 달리 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과 비슷한 일을 하지만 효율적인 경우가 많다.
h5.B*Tree Index Combination vs. Index Join
B*Tree Index Combination 과 Index Join의 성능 차이를 비교해본다.
SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> create table t1(c1 int,c2 int, c3 int);
테이블이 생성되었습니다.
SQL> insert into t1
2 select mod(level,10)+1, 10-mod(level,10)-1, level
3 from dual
4 connect by level <= 10000;
10000 개의 행이 만들어졌습니다.
SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.
SQL> create index t1_n2 on t1(c2);
인덱스가 생성되었습니다.
SQL> create index t1_n3 on t1(c3);
인덱스가 생성되었습니다.
SQL> explain plan
2 for
3 select /*+ gather_plan_statistics
4 index_combine(t1 t1(c1) t1(c2)) */ count(c3)
5 from t1
6 where c1=1
7 and c2=1;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 100 | 900 | 10 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP AND | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 6 | INDEX RANGE SCAN | T1_N1 | | | 2 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 8 | INDEX RANGE SCAN | T1_N2 | | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
SQL> select /*+ gather_plan_statistics
2 index_combine(t1 t1(c1) t1(c2)) */
3 count(c3)
4 from t1
5 where c1=1
6 and c2=1;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 100 | 0 |00:00:00.01 | 7 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 1 | | 0 |00:00:00.01 | 7 |
| 4 | BITMAP AND | | 1 | | 0 |00:00:00.01 | 7 |
| 5 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | T1_N1 | 1 | | 1000 |00:00:00.01 | 3 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 4 |
|* 8 | INDEX RANGE SCAN | T1_N2 | 1 | | 1000 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------
SQL> explain plan
2 for
3 select /*+ gather_plan_statistics
4 index_join(t1 t1(c1) t1(c2)) */ count(c3)
5 from t1
6 where c1=1
7 and c2=1;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 35 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | VIEW | index$_join$_001 | 100 | 900 | 35 (3)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
|* 4 | HASH JOIN | | | | | |
|* 5 | INDEX RANGE SCAN | T1_N1 | 100 | 900 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_N2 | 100 | 900 | 2 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN| T1_N3 | 100 | 900 | 28 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
SQL> select /*+ gather_plan_statistics
2 index_join(t1 t1(c1) t1(c2)) */
3 count(c3)
4 from t1
5 where c1=1
6 and c2=1;
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 100 | 0 |00:00:00.01 | 7 | | | |
|* 3 | HASH JOIN | | 1 | | 0 |00:00:00.01 | 7 | 921K| 921K|1/0/0 |
|* 4 | HASH JOIN | | 1 | | 0 |00:00:00.01 | 7 | 870K| 870K| 1/0/0|
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | 100 | 1000 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | T1_N2 | 1 | 100 | 1000 |00:00:00.01 | 4 | | | |
| 7 | INDEX FAST FULL SCAN| T1_N3 | 0 | 100 | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------
Index Join이 수행된경우는 Cost는 Index Combiantion 에 비해 높지만 일량은 동일하다.
Index Join의 경우 Hash Join으로 의한 PGA 사용량이 보고되는것에 주목하자, 처리할 Data의 양이
많을수록 성능에 안좋은 영향을 미칠수있다.
And Predicate에 의해 Table 방문 회수가 줄어 들지 않은 경우, 즉 단일 Index에 대한 Scan이 유리한
경우에는 B*Tree Index COmbination과 Index Join의 성능차이를 알아보자..
SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> create table t1
2 (c1 int,c2 int, c3 int);
테이블이 생성되었습니다.
SQL> insert into t1
2 select mod(level,10)+1,mod(level,10)+1,level
3 from dual
4 connect by level <= 10000;
10000 개의 행이 만들어졌습니다.
SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.
SQL> create index t1_n2 on t1(c2);
인덱스가 생성되었습니다.
SQL> create index t1_n3 on t1(c3);
인덱스가 생성되었습니다.
SQL> exec dbms_stats.gather_table_stats('bshman','t1');
PL/SQL 처리가 정상적으로 완료되었습니다.
-- B*Tree Index Combination 수행된 Cost일량
SQL> select /*+ gather_plan_statistics
2 index_combine(t1 t1(c1) t1(c2)) */
3 count(c3)
4 from t1
5 where c1=1
6 and c2=1;
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 27 |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 100 | 1000 |00:00:00.02 | 27 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 1 | | 1000 |00:00:00.02 | 6 |
| 4 | BITMAP AND | | 1 | | 1 |00:00:00.02 | 6 |
| 5 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | T1_N1 | 1 | | 1000 |00:00:00.01 | 3 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 3 |
|* 8 | INDEX RANGE SCAN | T1_N2 | 1 | | 1000 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 100 | 900 | 10 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 4 | BITMAP AND | | | | | |
| 5 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 6 | INDEX RANGE SCAN | T1_N1 | | | 2 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 8 | INDEX RANGE SCAN | T1_N2 | | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
-- Index Join이 선택된 경우의 Cost일량
SQL> select /*+ gather_plan_statistics
2 index_join(t1 t1(c1) t1(c2)) */
3 count(c3)
4 from t1
5 where c1=1
6 and c2=1;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem |/1/M |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 32 | | | |
|* 2 | VIEW | index$_join$_001 | 1 | 100 | 1000 |00:00:00.04 | 32 | | |
|* 3 | HASH JOIN | | 1 | | 1000 |00:00:00.04 | 32 | 842K| 842K| 1/0/0|
|* 4 | HASH JOIN | | 1 | | 1000 |00:00:00.01 | 6 | 870K| 870K| 1/0/0|
|* 5 | INDEX RANGE SCAN | T1_N1 | 1 | 100 | 1000 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | T1_N2 | 1 | 100 | 1000 |00:00:00.01 | 3 | | | |
| 7 | INDEX FAST FULL SCAN| T1_N3 | 1 | 100 | 10000 |00:00:00.03 | 26 | | | |
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 35 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | VIEW | index$_join$_001 | 100 | 900 | 35 (3)| 00:00:01 |
|* 3 | HASH JOIN | | | | | |
|* 4 | HASH JOIN | | | | | |
|* 5 | INDEX RANGE SCAN | T1_N1 | 100 | 900 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_N2 | 100 | 900 | 2 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN| T1_N3 | 100 | 900 | 28 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Index Join이 수행된 경우 일량이 더 늘어난다. 이유는 Index t1_n3 에 대한 Fast Full Scan이 발생하기 때문이다.
만일 Index t1_n3에 대한 크기가 커진다면 일량은 더욱더 커질것이다.
하지만,의도적으로 많은 양의 Data를 읽어야 한다면 Index Join이 유리한경우도 존재한다.
이유는 Table에 대한 방문 자체가 없기 때문에 Table에 대한 Random Access가 줄어들수 있기 때문이다.
h5.Hints And Parameters
Index join Scan은 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) t1(c2)) */ c1 from t1;
Index Join의 동작여부는 _Index_Join_Enabled Parameter를 이용해서 제어할수있다.