h2.Index Skip Scan
Index Skip Scan란?
1) Index Skip Scan은 9i에서 소개되었다.
2) 선행되는 Column이 조건절에 없는경우 Index를 사용할 수 있는 Operation이다.
3) Root 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스하는 방식
4) 조건절에 빠진 인덱스 선두 컬럼의 distinct value 개수가 적고 후행 컬럼의 distinct value 개수가 많을 때 유용
-- 플랜
SQL> explain plan for
2 select *
3 from t1
4 where c2 between 1 and 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T1_IDX | 1 | 26 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------
위의 내용은 데이터가 없는 상태에서 작업을 하니 나온결과다.
하단의내용은 데이터를 입력후 나온결과다.
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 70 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 10 | 70 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select /*+ index_ss(t1) */*
3 from t1
4 where c2 between 1 and 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 70 | 29 (4)| 00:00:01 |
|* 1 | INDEX FULL SCAN | T1_IDX | 10 | 70 | 29 (4)| 00:00:01 |
---------------------------------------------------------------------------
아먼가.. 힌트를 주어도 먹지않는다..
_optimizer_skip_scan_enabled 가 false로 되어 있는 경우가 있다.
이 경우에는 table 의 데이터가 skip scan 을 할 수 있는 환경이어도 이용되지 않는다.
skip scan을 할 수 있게 만들기 위해선 세가지 방법이 있다.
1) alter system
- SQL> alter system set "_optimizer_skip_scan_enabled"=true scope=spfile;
2) alter session
- SQL> alter session set "_optimizer_skip_scan_enabled"=true;
3) opt_param hint
- SQL> select /*+ opt_param('_optimizer_skip_scan_enabled','TRUE') */
2 c1
3 from t1
4 where c1 = 100;
※ alter system 의 경우는 시스템 restart 가 필요하다.
그래서 나두사용해보았다.
SQL> alter session set "_optimizer_skip_scan_enabled"=true;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 70 | 10014 (1)| 00:02:01 |
|* 1 | INDEX SKIP SCAN | T1_IDX | 10 | 70 | 10014 (1)| 00:02:01 |
---------------------------------------------------------------------------
true로 바꿔주니 정상적으로 나오는군요...저어마어마한 코스트는 먼가-_-;;
아래는 Row 수를 1에서 1000까지 증가시키면서 Index Skip Scan의 cost가 어떻게변화하는지 관찰한것이다.
-- Row : 1 ~ 1
SQL> explain plan for
2 select /*+ index_ss(t1) */ *
3 from t1
4 where c2 between 1 and 1;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 101 (0)| 00:00:02 |
|* 1 | INDEX SKIP SCAN | T1_IDX | 1 | 26 | 101 (0)| 00:00:02 |
---------------------------------------------------------------------------
-- Row : 1 ~ 10
SQL> explain plan for
2 select /*+ index_ss(t1) */ *
3 from t1
4 where c2 between 1 and 10;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 260 | 101 (0)| 00:00:02 |
|* 1 | INDEX SKIP SCAN | T1_IDX | 10 | 260 | 101 (0)| 00:00:02 |
---------------------------------------------------------------------------
Cost가 변한다고했는데 변하지않았다.
그래서 통계정보를 수집도 다시도전해보았다.
SQL> exec dbms_stats.gather_table_stats('bshman','t1',method_opt=>'for all colum
ns size 1',cascade=>true);
-- 통계정보수집후 Row : 1~1
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 10014 (1)| 00:02:01 |
|* 1 | INDEX SKIP SCAN | T1_IDX | 1 | 7 | 10014 (1)| 00:02:01 |
---------------------------------------------------------------------------
-- 통계정보수집후 Row : 1~10
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 70 | 10014 (1)| 00:02:01 |
|* 1 | INDEX SKIP SCAN | T1_IDX | 10 | 70 | 10014 (1)| 00:02:01 |
---------------------------------------------------------------------------
-- 인덱스정보
SQL> select index_name,leaf_blocks from user_indexes;
INDEX_NAME LEAF_BLOCKS
------------------------------ -----------
T1_IDX 27
먼가이상하다.5시간동안 지우고 다시하고 지우고 다시하였는데 답이안나왔다 ㅜㅜ
분명 Skip Scan 은 Table Full 보다 빠르다고 했는데 이건 반대다...
정상으로 테스트해보신분은 코맨트좀 -_-;;
<책의결과>
위의 결과에서 본거와 같이 Skip Scan은 범위가 넓으면 넓을수록 상당히 불리하다는걸 알수있다.
이유는 하나의 Row를 Fetch 할 때마다 매번 Leaf Block을 방문한다고 생각해보아라 어마어마하다.
따라서 Index Skip Scan이 Cost는 Row 수 + <Small Value>가 된다.
h5.Distinct Count와 Skip Scan
Index Skip Scan은 선행 Column의 Distinct가 낮을때만 사용이 권장된다.
Oracle 또한 그런 경우에만 Index Skip Scan을 선택한다.
아래의 예제로 확인해보자.
-- 테이블생성
SQL> create table t1(c1 char(2), c2 int ,c3 char(10));
테이블이 생성되었습니다.
-- 인덱스 생성
SQL> create index t1_idx on t1(c1,c2);
인덱스가 생성되었습니다.
-- 자료생성_1
SQL> insert into t1
2 select 'A',level,'dummy'
3 from dual
4 connect by level <= 5000;
5000 개의 행이 만들어졌습니다.
-- 자료생성_2
SQL> insert into t1
2 select 'B',level,'dummy'
3 from dual
4 connect by level <= 5000;
5000 개의 행이 만들어졌습니다.
--적용
SQL> commit;
커밋이 완료되었습니다.
-- 통계정보생성
SQL> exec dbms_stats.gather_table_stats('bshman','t1',method_opt=>'for all colum
ns size 1',cascade=>true);
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 플랜
SQL> explain plan for
2 select /*+ index_ss(t1) */ *
3 from t1
4 where c2 between 1 and 50;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1700 | 102 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1700 | 102 (0)| 00:00:02 |
|* 2 | INDEX SKIP SCAN | T1_IDX | 100 | | 101 (0)| 00:00:02 |
--------------------------------------------------------------------------------------
Index Skip Scan과 Table Full Scan의 실제 일량(Logical Reads)를 비교해 보면 아래와 같다.
-- 설정
SQL> set arraysize 15;
-- 플랜
SQL> explain plan for
2 select *
3 from t1
4 where c2 between 1 and 50;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1700 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 1700 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
( 어라책에서는 Skip Scan을 선택한다고 나왔는데 풀을 태웠다.)
그래서 강제로 태워본다.
SQL> explain plan for
2 select /*+ gather_plan_statistics index_ss(t1) */ *
3 from t1
4 where c2 between 1 and 50;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1700 | 102 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1700 | 102 (0)| 00:00:02 |
|* 2 | INDEX SKIP SCAN | T1_IDX | 100 | | 101 (0)| 00:00:02 |
--------------------------------------------------------------------------------------
SQL> explain plan for
2 select /*+ gather_plan_statistics full(t1) */ *
3 from t1
4 where c2 between 1 and 50;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1700 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 1700 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------
-- 책대로 해본다.
SQL> select /*+ gather_plan_statistics index_ss(t1) */ *
2 from t1
3 where c2 between 1 and 50;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Tme | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 100 |00:00:0.01 | 28 |
|* 2 | INDEX SKIP SCAN | T1_IDX | 1 | 100 | 100 |00:00:0.01 | 18 |
-----------------------------------------------------------------------------------------------
SQL> select /*+ gather_plan_statistics full(t1) */ *
2 from t1
3 where c2 between 1 and 50;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 45 |
------------------------------------------------------------------------------------
위의 내용으로 알수있는건 Cost가 좋은 Table Full 스캔을 옵티마이저가 태운다는걸 알수있다.
반면 아래와 같이 Index Range Scan을 사용하는 경우 가장 성능이 뛰어나다.
-- 플랜
SQL> select /*+ gather_plan_statistics index(t1) */ *
2 from t1
3 where c1 = 'A' and c2 between 1 and 50
4 union all
5 select /*+ gather_plan_statistics index(t1) */ *
6 from t1
7 where c1 = 'B' and c2 between 1 and 50;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 1 | UNION-ALL | | 2 | | 200 |00:0000.01 | 214 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 50 | 100 |00:00:00.01 | 106 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 2 | 50 | 100 |00:00:00.01 | 54 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 50 | 100 |00:00:00.01 | 108 |
|* 5 | INDEX RANGE SCAN | T1_IDX | 2 | 50 | 100 |00:00:00.01 | 54 |
-------------------------------------------------------------------------------------------------
Index Skip Scan은 Branch Block에 대한 추가적인 I/O가 발생하기 때문에 Index Range Scan보다는 항상 비효율적이다.
선행 컬럼의 Distinct Count가 어느정도 높은경우 어떤변화가 생기는지 확인해본다.
-- 자료생성
SQL> insert into t1
2 select s.value,rownum,'dummy'
3 from
4 (select rpad((level-1)||'',2,'0') as value from dual
5 connect by level <= 100) s,
6 (select level from dual connect by level <= 50) t;
5000 개의 행이 만들어졌습니다.
-- 플랜
SQL> explain plan for
2 select *
3 from t1
4 where c2 between 1 and 50;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 850 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 50 | 850 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
<Index Skip Scan과 Table Full Scan의 실제 일량(Logical Reads)를 비교해 보면 아래와 같다.>
SQL> select /*+ gather_plan_statistics index_ss(t1) */ *
2 from t1
3 where c2 between 1 and 50;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50 | 50 |00:00:00.01 | 98 |
|* 2 | INDEX SKIP SCAN | T1_IDX | 1 | 50 | 50 |00:00:00.01 | 72 |
------------------------------------------------------------------------------------------------
SQL> select /*+ gather_plan_statistics full(t1) */ *
2 from t1
3 where c2 between 1 and 50;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 1 | 50 | 50 |00:00:00.01 | 48 |
------------------------------------------------------------------------------------
위의 2개의 플랜결과와같이 Table Full이 Logical Read 좋기 때문에 옵티마이저 선택이 옳다는걸 확인할수있다.
그렇다면 Index Range Scan은?
SQL> select /*+ gather_plan_statistics index(t1) */ *
2 from t1
3 where c1 between '00' and '99'
4 and c2 between 1 and 50;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 50 | 50 |00:00:00.01 | 73 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 1 | 50 | 50 |00:00:00.01 | 47 |
------------------------------------------------------------------------------------------------
위의 결과로 확인결과 Skip Scan보다는 일량이 좋다는걸 확인할수있다.
잘수행되는 Index Range Scan보다는 차선으로 선택해야된다는걸 알수있다.
h5.Hints And Parameters
Index Skip Scan은 Index_SS Hint 로 제어한다.
select /* index_ss(t1) */ c1 from t1;
select /* index_ss(t1 t1_n1) */ c1 from t1;
select /* index_ss(t1 t1(c1)) */ c1 from t1;
select /* index_ss_asc(t1 t1(c1)) */ c1 from t1;
select /* index_ss_desc(t1 t1(c1)) */ c1 from t1;