Optimizing Oracle Optimizer (2009년)
목록
Index Skip Scan 0 0 98,894

by 구루비스터디 Index Skip Scan [2018.07.14]

  1. Index Skip Scan
    1. 기본 Mechanism
    2. Distinct Count 와 Skip Scan
    3. Hint and Parameters


Index Skip Scan

  • Where 절의 특정 조건이 Index 를 사용하는 가장 기본적인 전제 조건은 해당 Index 를 구성하는 Column 의 전체 또는 일부가 조건 절에서 사용되어야 한다는 것이다.
  • 더 정확하게 표현하면 Index Key 를 구성하는 Column 중 선행 하는 Column 이 조건에 있는 경우에만 Index 가 선택될 수 있다.



SYS> drop table t1 purge ;
Table dropped.

SYS> create table t1 ( c1 number, c2 number, c3 number ) ;
Table created.

SYS> create index t1_n1 on t1 ( c1,c2,c3) ;
Index created.

SYS> insert into t1 select level, level, level from dual connect by level <= 10000 ;
10000 rows created.

SYS> @gather t1
PL/SQL procedure successfully completed.

SYS>explain plan for select * from t1 where c1 = 1 ;
Explained.

SYS>@plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1088104427

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    11 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T1_N1 |     1 |    11 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - access("C1"=1)

13 rows selected.

SYS>select /*+ gather_plan_statistics */ * from t1 where c1 = 1 ;
SYS>@stat

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  6qzwtdmdyr11f, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t1 where c1 = 1

Plan hash value: 1088104427

-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| T1_N1 |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=1)

17 rows selected.


  • Leaf - Branch ( Single I/O ) - 이후 다음 데이타 확인(다음 INDEX Leaf Block 의 데이타가 조건에 만족하지 않음을 확인 하기 위해서 한번 더 Buffer 을 읽었다.)을 위해서 다시 동일 블록에 대한 Brach ( Single I/O) 수행 해서 총 3 Buffers



SYS>@check_table_and_indexes.sql

TABLE_NAME                ROW_S     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE ANA
-------------------- ---------- ---------- ------------ ----------- ----------- -----------------
T1                        10000         28            0          11      10,000 20090311 09:13:28

INDEX_NAME                ROW_S     BLEVEL     LEAF_B    DIS_KEY KEYPERLEAF CLUSETERING SAMPLE_SIZE ANA
-------------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------------
T1_N1                     10000          1         29      10000          1          24      10,000 20090311 09:13:29


SYS>explain plan for select * from t1 where c1 = 1 and c2 = 2 and c3 = 3 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 1088104427

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    11 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T1_N1 |     1 |    11 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=1 AND "C2"=2 AND "C3"=3)

13 rows selected.

SYS>explain plan for select * from t1 where c1 = 1 and c3 = 3 ;
Explained.

SYS>@plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1088104427

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    11 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T1_N1 |     1 |    11 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=1 AND "C3"=3)
       filter("C3"=3)
14 rows selected.
-- c1 and c3 조건이지만 Index Range Scan 을 탄다.
-- 반면 다음과 같은 조건은 Index 가 선택될 수 없는 상황으로 인식된다.

SYS>drop index t1_n1 ;
Index dropped.

SYS>create index t1_n1 on t1 ( c1 , c2 ) ;
Index created.

SYS>explain plan for select * from t1 where c2 between 2 and 3 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    33 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |    33 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"<=3 AND "C2">=2)

13 rows selected.


  • Index Key 를 구성하는 선행 Column 인 Column c1 이 조건에 기술되어 있지 않기 때문이다.
  • 이러한 한계를 극복하는 것이 Oracle 9i 에서 소개된 Index Skip Scan 이다.


기본 Mechanism

  • Index Skip Scan 은 Index Key 의 선행 Column 이 조건 절에 없는 경우에도 Index 를 사용할 수 있는 Operation 이다.



SYS> explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 2 and 3 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     3 |    33 | 10012   (1)| 00:02:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     3 |    33 | 10012   (1)| 00:02:01 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 |     3 |       | 10011   (1)| 00:02:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=2 AND "C2"<=3)
       filter("C2"<=3 AND "C2">=2)
  • ==> 두개가 나오는 경우는 먼가 ㅇㅇ?


  • Oracle 은 어떻게 선행 Column 이 없는 상황에서도 Index 를 사용하는가 ?
  • 정답은 In List Operation 과 거의 동일한 방법을 사용한다는 것이다.


  • Coumne c1 의 Distinct 값이 'A','B'라고 가정

SYS>explain plan for
    select /*+ index(t1) */ * from t1 where c1 = 'A' and c2 between 1 and 10
    union all
    select /*+ index(t1) */ * from t1 where c1 = 'B' and c2 between 1 and 10 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 182274094

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    22 |     6  (50)| 00:00:01 |
|   1 |  UNION-ALL                   |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    11 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    11 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | T1_N1 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C1"=TO_NUMBER('A') AND "C2">=1 AND "C2"<=10)
   5 - access("C1"=TO_NUMBER('B') AND "C2">=1 AND "C2"<=10)

18 rows selected.


  • 하지만 살제로 Index Skip Scan 은 위의 Query 보다는 비효율적이다.
  • Oracle 은 Column c1 의 값이 'A','B' 만 존재한다는 사실을 알 수 없기 때문이다.
  • 따라서 Leaf Block 을 순회하면서 값의 범위를 벗어나면 다시 Branch Block 값을 읽어서 어디로 Skip 해야할지 결정 해야 한다.
  • 이 때문에 Skip Scan 이라는 이름이 부여되었다.
  • 즉, Index Skip Scan 은 Branch Block 을 추가로 읽어야 하기 때문에 그만큼 Overhead 가 발생한다.



SYS> explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 1 and 1 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    11 | 10012   (1)| 00:02:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    11 | 10012   (1)| 00:02:01 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 |     1 |       | 10011   (1)| 00:02:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"=1)
       filter("C2"=1)

15 rows selected.

SYS> explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 1 and 1000 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000 | 11000 | 10014   (1)| 00:02:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1000 | 11000 | 10014   (1)| 00:02:01 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 |  1000 |       | 10011   (1)| 00:02:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=1 AND "C2"<=1000)
       filter("C2"<=1000 AND "C2">=1)

15 rows selected.

-- 이런 책과 다르게 Cost 비용이 차이가 발생 하지 않는다.
SYS>update t1 set c1 = 10001 where mod(c1, 2) = 0 ;
5000 rows updated.

SYS>update t1 set c1 = 10002  where c1 <> 10001 ;
5000 rows updated.

-- 데이타를 c1 값 2종류에 맞추어 균등하게 분배
SYS>select c1, count(c2) from t1 group by c1 ;

        C1  COUNT(C2)
---------- ----------
     10002       5000
     10001       5000

SYS>@gather t1
PL/SQL procedure successfully completed.

-- 다시 시도
SYS>explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 2 and 3 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     3 |    36 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     3 |    36 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 |     3 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=2 AND "C2"<=3)
       filter("C2"<=3 AND "C2">=2)
15 rows selected.

SYS>explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 1 and 1 ;
Explained.

SYS>@plan
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    12 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    12 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"=1)
       filter("C2"=1)

15 rows selected.

SYS>explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 1 and 1000 ;
Explained.

SYS>@plan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1000 | 12000 |  1007   (1)| 00:00:13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1000 | 12000 |  1007   (1)| 00:00:13 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 |  1000 |       |  1002   (1)| 00:00:13 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=1 AND "C2"<=1000)
       filter("C2"<=1000 AND "C2">=1)

15 rows selected.


-- 이제 차이가 난다. 접근해야 할 Leaf Block 수에 따라서 Cost 차이가 발생 한다.
-- 엄밀히 이야기 해서 접근 해야 할 Leaf Block 이 많을 수록 중간 중간 Branch Block 에 대한
-- 확인이 잦아진다. 그 결과다. Cost 3과 1002


Distinct Count 와 Skip Scan


SYS>explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 1 and 10000 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3715569459

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   117K| 10060   (1)| 00:02:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    | 10000 |   117K| 10060   (1)| 00:02:01 |
|*  2 |   INDEX SKIP SCAN           | T1_N1 | 10000 |       | 10012   (1)| 00:02:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=1 AND "C2"<=10000)
       filter("C2">=1 AND "C2"<=10000)

15 rows selected.


이 Query 의 성능은 두 가지 요소에 의해 결정된다.
  • 후행 Column c2 의 탐색 범위가 넓으면 성능에 불리하다.
  • 선행 Column c1 의 Distinct Count 가 높으면 그 Distinct Count 만큼 Branch Block 에 대한 방문이 발생하므로 성능에 불리하다.
  • 따라서, Index Skip Scan 은 선행 Column 의 Distinct Count 가 낮을 때만 사용이 권장되며, Oracle 또한 그런 경우에만 Index Skip Scan 을 선택 한다.



SYS>drop table t1 purge ;
Table dropped.
SYS>create table t1 ( c1 char(2), c2 int, c3 char(100) ) ;
Table created.

SYS>create index t1_idx on t1(c1, c2 ) ;
Index created.

-- C1 에 대한 DISTINCT 가 'A','B' 만 갖도록 입력
SYS>insert into t1 select 'A', level, 'dummy' from dual connect by level <= 5000 ;
5000 rows created.

SYS>insert into t1 select 'B', level, 'dummy' from dual connect by level <= 5000 ;
5000 rows created.

SYS>commit ;
Commit complete.

SYS>@gather t1
PL/SQL procedure successfully completed.


SYS>explain plan for select /*+ index_ss(t1) */ * from t1 where c2 between 1 and 50 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3250558649

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   100 | 10700 |   103   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   100 | 10700 |   103   (0)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | T1_IDX |   100 |       |   101   (0)| 00:00:02 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=1 AND "C2"<=50)
       filter("C2"<=50 AND "C2">=1)

15 rows selected.


  • Index Skip Scan 과 Table Full Scan 의 실제 일량(Logical Reads) 를 비교해 보면 다음과 같다.

SYS>set arraysize 15
SYS>show arraysize
arraysize 15
SYS>select /*+ gather_plan_statistics index_ss(t1) */ * from t1 where c2 between 1 and 50 ;
100 rows selected.
SYS>@stat

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  2j36y6ahzk9bm, child number 0
-------------------------------------
select /*+ gather_plan_statistics index_ss(t1) */ * from t1 where c2 between 1 and 50

Plan hash value: 3250558649

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |      1 |    100 |   103   (0)|    100 |00:00:00.01 |      27 |
|*  2 |   INDEX SKIP SCAN           | T1_IDX |      1 |    100 |   101   (0)|    100 |00:00:00.01 |      18 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=1 AND "C2"<=50)
       filter(("C2"<=50 AND "C2">=1))

SYS>select /*+ gather_plan_statistics full(t1) */ * from t1 where c2 between 1 and 50 ;

SYS>@stat

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  cdpp3qhaaa0r6, child number 0
-------------------------------------
select /*+ gather_plan_statistics full(t1) */ * from t1 where c2 between 1 and 50

Plan hash value: 3617692013

-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    100 |    36   (0)|    100 |00:00:00.01 |     168 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("C2"<=50 AND "C2">=1))


  • INDEX SKIP SCAN 이 TABLE FULL SCAN 보다 뛰어나다(Buffers 비교).
  • 그러나, 테스트 진행 시 특별한 힌트가 없다면 FULL TABLE SCAN 으로 실행된다.



SYS>  select /*+ gather_plan_statistics index(t1) */ * from t1 where c1 ='A' and c2 between 1 and 50
      union all
      select /*+ gather_plan_statistics index(t1) */ * from t1 where c1 ='B' and c2 between 1 and 50;

SYS>@stat

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  fpbqdfzbbth7r, child number 0
-------------------------------------
 select /*+ gather_plan_statistics index(t1) */ * from t1 where c1 ='A' and c2 between 1 and 50
union all       select /*+ gather_plan_statistics index(t1) */ * from t1 where c1 ='B' and c2 between
1 and 50

Plan hash value: 3425155646

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                   |        |      1 |        |            |    100 |00:00:00.01 |      20 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |     50 |     3   (0)|     50 |00:00:00.01 |      11 |
|*  3 |    INDEX RANGE SCAN          | T1_IDX |      1 |     50 |     2   (0)|     50 |00:00:00.01 |       6 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |     50 |     3   (0)|     50 |00:00:00.01 |       9 |
|*  5 |    INDEX RANGE SCAN          | T1_IDX |      1 |     50 |     2   (0)|     50 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C1"='A' AND "C2">=1 AND "C2"<=50)

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
   5 - access("C1"='B' AND "C2">=1 AND "C2"<=50)


24 rows selected.


  • Index Skip Scan 은 Branch Block 에 대한 추가적인 I/O 가 발생하기 때문에 Index Range Scan 보다는 항상 비효율적이다.
  • 가끔 Buffers 가 안보이는 경우가 있다. 이 경우엔 힌트절의 오타로.. 힌트절의 gather_plan_statistics 가 무시된 경우이다.



SYS>truncate table t1 ;
Table truncated.


SYS>insert into t1 select s.value , rownum , 'dummy'
    from
    ( select rpad((level-1)||'',2,'0') as value from dual connect by level <= 100 ) s,
    ( select level from dual connect by level <= 50 ) t ;
4900 rows created.

SYS> insert into t1
    select s.value , rownum, 'dummy'
    from
    ( select rpad((level-1)||'',2,'0') as value from dual connect by level <= 100) s,
    ( select level from dual connect by level <= 50 ) t ;
5000 rows created.

SYS> select c1 , count(c2) from t1 group by c1
     order by c1;

-- ----------
C1  COUNT(C2)
-- ----------
00        100
10        200
...
97        100
98        100
99        100

91 rows selected.
SYS>@gather t1

PL/SQL procedure successfully completed.

SYS>explain plan for select * from t1 where c2 between 1 and 50 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 | 10700 |    36   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 | 10700 |    36   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

   1 - filter("C2"<=50 AND "C2">=1)

13 rows selected.

SYS>select /*+ gather_plan_statistics index_ss(t1) */ * from t1 where c2 between 1 and 50 ;
...
00         50 dummy
100 rows selected.

SYS>@stat

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  2j36y6ahzk9bm, child number 0
-------------------------------------
select /*+ gather_plan_statistics index_ss(t1) */ * from t1 where c2 between 1 and 50

Plan hash value: 3250558649

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |      1 |    100 |   193   (0)|    100 |00:00:00.01 |     155 |
|*  2 |   INDEX SKIP SCAN           | T1_IDX |      1 |    100 |    92   (0)|    100 |00:00:00.01 |      55 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2">=1 AND "C2"<=50)
       filter(("C2"<=50 AND "C2">=1))
19 rows selected.

SYS>select /*+ gather_plan_statistics full(t1) */ * from t1 where c2 between 1 and 50 ;
SYS>@stat
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  cdpp3qhaaa0r6, child number 0
-------------------------------------
select /*+ gather_plan_statistics full(t1) */ * from t1 where c2 between 1 and 50

Plan hash value: 3617692013

-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    100 |    36   (0)|    100 |00:00:00.01 |     168 |
-------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("C2"<=50 AND "C2">=1))


---


  • Index Skip Scan 이 선택된 실행 계획의 일량이 27 에서 157[155]로 크게 증가했다는 것을 알수 있다.
  • 선행 칼럼의 Distinct 가 높아짐에 따라서
  • 이 수치가 Table Full Scan 의 191 보다는 작지만 더 이상 효율적이라고 말히기는 힘들것이다.


  • 선행 Column 의 Distinct Count 가 높기 때문에 Index Range Scan 또한 효율적이지 않을 수 있다.
  • 다음과 같이 148 Block 의 Logical Reads 를 보인다. 하지만 여전히 Index Skip Scan 보다 효율적이다.

SYS>select /*+ gather_plan_statistics index(t1) */ * from t1 where c1 between '00' and '99' and c2 between 1 and 50 ;
SYS>@stat

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  84r8p6nbcspmr, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t1) */ * from t1 where c1 between '00' and '99' and c2 between 1 and 50

Plan hash value: 546753835

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |      1 |    100 |   142   (0)|    100 |00:00:00.01 |     148 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |      1 |    100 |    41   (0)|    100 |00:00:00.01 |      48 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">='00' AND "C2">=1 AND "C1"<='99' AND "C2"<=50)
       filter(("C2"<=50 AND "C2">=1))

20 rows selected.
-- 결론적으로 Index Skip Scan 은 최선이 아니고, 차선의 선택이다.Index Range Scan을 수행하는 잘 수행된 SQL 문장이 최선이다.


Hint 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;


  • Index Skip Scan 의 동작 여부는 _OPTIMIZER_SKIP_SCAN_ENABLED Parameter 를 이용해서 제어할 수 있다.



SYS>@check_hidden_parameter.sql
Enter value for input_parameter: _optimizer_skip_scan_enabled
old  15: a.ksppinm LIKE '&input_parameter'
new  15: a.ksppinm LIKE '_optimizer_skip_scan_enabled'

Parameter                                                    Session Value                  Instance Value
------------------------------------------------------------ ------------------------------ ------------------------------
_optimizer_skip_scan_enabled                                 TRUE                           TRUE


index Skip Scan이 되지 않게 하기위한 파라미터임.
  • -> Index Skip Scan이 필요한 경우에는 10g부터 지원되는 opt_param() 힌트를 사용하여 SQL Level에서 Index Skip Scan이 되게끔 바꿔주면 됨.
  • ( /*+ opt_param('_OPTIMIZER_SKIP_SCAN_ENABLED','FALSE') */ )
  • -> Index Skip Scan이 False일 경우 Skip Scan이 되지 않지만, Index Column인 경우에 Index Filter가 되기때문에 성능상 많은 영향은 없음)
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3864

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입