by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Index Skip Scan




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>가 된다.
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보다는 차선으로 선택해야된다는걸 알수있다.
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;

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.