Optimizing Oracle Optimizer (2009년)
목록
Index Full Scan 0 0 94,403

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

  1. Index Full Scan
    1. 기본 Mechanism
    2. Min / Max Problem
    3. Index Range Scan or Full Scan ?
  2. Hints and Parameters
    1. Reminder


Index Full Scan

  • Index Full Scan 은 매우 특이한 Operation 이다.
  • 우선 Hint 로 제어할 수 없다.
  • INDEX Hint 가 있지만, Index Range Scan 과 Index Full Scan 중 어느 것이 선택될지 100% 장담할 수 없다는 의미에서 그렇다.
  • Index Range Scan 이 좁은 범위의 Data 를 추출할 목적으로, Index Fast Full Scan 이 넓은 범위의 Data 를 추출할 목적으로 고안된 것을 생각하면 Index Full Scan 이 존재 목적 자체가 조금 애매해 보인다.


기본 Mechanism

  • Index Full Scan 의 동작 Mechanism 은 Index Range Scan 과 완벽하게 동일하다.
  • Index Range Scan 이 특정 범위를 만족하는 Key 값에서부터 Scan 을 시작한다면, Index Full Scan 은 Leaf Node 의 최초의 값(혹은 최후의 값)에서부터 마지막 값까지 차례대로 읽는다는 차이가 있을 뿐이다.



SYS> explain plan for select /*+ index(t1) */ c1 from t1 ;
SYS> @plan

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


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 40000 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 40000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.


  • 책과 다르게 INDEX FULL SCAN 유도가 안되네 ㅡ_ㅡ;*



--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 40000 |     6   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN  |T1_N1`| 10000 | 40000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------



Index Full Scan 의 동작 방식은
  • 항상 첫 번째 Leaf Block 에서 Scan 을 시작한다는 것을 제외하면 Index Range Scan 과 완전히 동일하다.
  • 모든 Key 값을 정렬된 순서로 읽고자 할 경우가 Index Full Scan 의 유일한 존재 목적이라고 할 수 있다.
  • 그런 목적이 아니라면 Single Block I/O, 즉 Random Access I/O 를 통해 모든 Leaf Block 을 읽어 들이는 비효율적인 일을 할 필요가 있겠는가?



SYS>drop table t1 purge ;
Table dropped.

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

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

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

SYS>commit ;
Commit complete.

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

-- Index Hint 를 부여했음에도 불구하고 Table Full Scan 이 선택된다.
SYS>explain plan for select /* index(t1) */ c1 from t1 ;
Explained.

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 40000 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 40000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.

-- Order By 구문을 이용해 정렬된 결과를 얻고자 할 경우에도  역시 Table Full Scan 이 선택된다.
SYS>explain plan for select /* index(t1) */ c1 from t1 order by c1 ;

Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2148421099

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10000 | 40000 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      | 10000 | 40000 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 | 40000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 rows selected.


왜 그런가 ?
  • Index 의 기본적인 한계 때문이다.
  • Index 의 Key 는 NULL 값을 포함하지 못한다.
  • 즉 NULL 값은 Indexing 되지 않는다.
  • 따라서 위의 두 SQL 문장에 대해서 Index Scan을 사용할 수 없다.


  • Index 만 타게 되면 Null 값이 제외 되므로, 인덱스를 타서는 원하는 결과는 가져올 수 없다.
  • Table 이 NULL 값을 포함하고 있을 수 있기 때문이다.
  • 따라서 다음과 같이 NOT NULL 조건을 부여함으로써 Oracle 에서 "Data 에 NULL 이 없으므로 안심하고 index 를 사용하라" 고 알려줄 수 있다.
  • 다음과 같이 NOT NULL 조건이 부여된 경우 정상적으로 Index Full Scan 이 선택된다.
  • ( 또는 where 절에 c1 is not null 조건을 부여해도 같은 효과가 있다.).



SYS>alter table t1 modify c1 not null ;
Table altered.

SYS>explain plan for select /* index(t1) */ c1 from t1 ;
Explained.

SYS>@plan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 587075276
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 10000 | 40000 |     6   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| T1_N1 | 10000 | 40000 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------
8 rows selected.

-- Order by 구문으로 원하는 Index Full Scan 을 사용한다.

SYS>explain plan for select c1 from t1  order by c1 ;
Explained.

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

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       | 10000 | 40000 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY        |       | 10000 | 40000 |     8  (25)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| T1_N1 | 10000 | 40000 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------
9 rows selected.


  • 페이지 178 페이지


Min / Max Problem

  • Index Full Scan 의 가장 큰 용도 중 하나가 Order by 나 Min/Max 를 대체 하는 것이다.



SYS>explain plan for select /*+ index(t1) */ max(c1) from t1 ;
Explained.

SYS>@plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 1426435604

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |       |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T1_N1 | 10000 | 40000 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

9 rows selected.


  • INDEX FULL SCAN ( MIN/MAX) 라는 특이한 이름의 Operation 이 사용된 것을 알 수 있다.
  • 말 그대로 Index Full Scan 을 하되 Min/Max 값만 가져오겠다는 의미이다.
  • 최초(혹은 최후)의 Leaf Block 까지만 읽고 Scan 을 중단하는 매우 효과적인 Scan 방식이다.
  • Oracle 이 예상하는 Cost가 "2"에 불고한 것에 유의하자.



SYS>explain plan for select /*+ index(t1) */ max(c1) from t1 where c1 > 1 ;
Explained.

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

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |       |     1 |     4 |            |          |
|   2 |   FIRST ROW                  |       |  9999 | 39996 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T1_N1 |  9999 | 39996 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("C1">1)

15 rows selected.


  • INDEX RANGE SCAN (MIN/MAX) Operation 과 FIRST ROW Operation 의 조합으로 최소의 I/O만이 수행되도록 동작한다.
  • 만일 Min Function 과 Max Function 을 같이 사용하면 어떻게 될까 ?



SYS>explain plan for select /*+ index(t1) */ min(c1), max(c1) from t1 ;
Explained.

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

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     4 |    20   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| T1_N1 | 10000 | 40000 |    20   (0)| 00:00:01 |
--------------------------------------------------------------------------

9 rows selected.


  • 불행하게도 Index Full Scan이 사용되며 Cost 가 20 이다.
  • 즉, Index 의 Leaf Block 전체를 순서대로 읽겠다는 의미이다.
  • 이 문제를 해결하려면 다음과 같이 Query 를 수정해야 한다.



SYS>explain plan for select /*+ index(t1) */ min(c1) from t1
    union all
    select /*+ index(t1) */ max(c1) from t1 ;
Explained.

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

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |     8 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL                  |       |       |       |            |          |
|   2 |   SORT AGGREGATE            |       |     1 |     4 |            |          |
|   3 |    INDEX FULL SCAN (MIN/MAX)| T1_N1 | 10000 | 40000 |     2   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE            |       |     1 |     4 |            |          |
|   5 |    INDEX FULL SCAN (MIN/MAX)| T1_N1 | 10000 | 40000 |     2   (0)| 00:00:01 |

SYS>@check_table_and_indexes.sql
TABLE_NAME                ROW_S     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE ANA
-------------------- ---------- ---------- ------------ ----------- ----------- -----------------
T1                        10000         20            0           7      10,000 20090308 17:11:54

INDEX_NAME                ROW_S     BLEVEL     LEAF_B    DIS_KEY KEYPERLEAF CLUSETERING SAMPLE_SIZE ANA
-------------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------------
T1_N1                     10000          1         19      10000          1          18      10,000 20090308 17:11:54


Index Range Scan or Full Scan ?


SYS>explain plan for select count(*) from t1 where c1 like '%'||:b1||'%' ;
Explained.

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

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     4 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T1_N1 |   500 |  2000 |     7  (15)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_CHAR("C1") LIKE '%'||:B1||'%')
14 rows selected.


  • 앞 뒤로 '%'가 붙은 Like 조건에서 Index Range Scan 이 사용되다니 ? 상식적으로 불가능하다.
  • 일반적으로 인덱스 사용을 못한다.
  • 정답은 Index Range Scan 은 실제로는 Index Full Scan 과 동일하다는 것이다.



SYS>create table t1 ( c1 varchar2(10), c2 char(10));
Table created.

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

SYS>commit ;
Commit complete.

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

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

SYS>select * from t1 where rownum < 10 ;

C1         C2
---------- ----------
1          a
2          a
3          a
4          a
5          a
6          a
7          a
8          a
9          a

9 rows selected.

-- Table 및 Index 정보 체크
SYS>@check_table_and_indexes.sql

TABLE_NAME                ROW_S     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE ANA
-------------------- ---------- ---------- ------------ ----------- ----------- -----------------
T1                        10000         31            0          15      10,000 20090310 21:05:58

INDEX_NAME                ROW_S     BLEVEL     LEAF_B    DIS_KEY KEYPERLEAF CLUSETERING SAMPLE_SIZE ANA
-------------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------------
T1_N1                     10000          1         23      10000          1        1885      10,000 20090310 21:05:58

C_NAME               DATA_TYPE   D_L NULL NUM_DISTINCT  DEN    NUM_NULLS NUM_BUC ANA                SAMPLE_SIZE USER_STATS HIS
-------------------- ---------- ---- ---- ------------ ---- ------------ ------- ----------------- ------------ ---------- ---------------
C1                   VARCHAR2     10 Y          10,000    0            0       1 20090310 21:05:58       10,000 NO         NONE
C2                   CHAR         10 Y               1    1            0       1 20090310 21:05:58       10,000 NO         NONE

SYS>var b1 varchar2(1) ;
SYS>-- when value = 1
SYS>exec :b1 := '1' ;
PL/SQL procedure successfully completed.

SYS>select /*+ gather_plan_statistics */ count(*) from t1 where c1 like '%'||:b1||'%';

  COUNT(*)
----------
      3440

SYS>@stat

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  824h9cx550a8q, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 like '%'||:b1||'%'

Plan hash value: 73337487

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

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

   2 - access("C1" LIKE '%'||:B1||'%')
       filter("C1" LIKE '%'||:B1||'%')

19 rows selected.

-- BIND 변수 사용시
-- INDEX RANGE SCAN / COST - 3 , BUFFER - 24

SYS>select /*+ gather_plan_statistics index(t1) */ count(*) from t1 where c1 like '%1%';

  COUNT(*)
----------
      3440

SYS>@stat

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  685w3zrj87tzc, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t1) */ count(*) from t1 where c1 like '%1%'

Plan hash value: 232612676

-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE  |       |      1 |      1 |            |      1 |00:00:00.01 |      24 |
|*  2 |   INDEX FULL SCAN| T1_N1 |      1 |    500 |    24   (0)|   3440 |00:00:00.01 |      24 |
-------------------------------------------------------------------------------------------------

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

   2 - filter("C1" LIKE '%1%')


18 rows selected.

-- LITERAL 변수 사용시
-- INDEX FULL SCAN / COST - 24 , BUFFER - 24
-- BIND 변수 사용시
-- INDEX RANGE SCAN / COST - 3 , BUFFER - 24


SYS>-- when value = x ( non-existent value )
SYS>exec :b1 := 'x'

PL/SQL procedure successfully completed.

SYS>select /*+ gather_plan_statistics */ count(*) from t1 where c1 like '%'||:b1||'%';

  COUNT(*)
----------
         0

SYS>@stat

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID  824h9cx550a8q, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 like '%'||:b1||'%'

Plan hash value: 73337487

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

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

   2 - access("C1" LIKE '%'||:B1||'%')
       filter("C1" LIKE '%'||:B1||'%')


19 rows selected.

-- 실제 존재하지 않는 값을 Bind 변수로 사용
-- INDEX RANGE SCAN  / Cost - 3, Buffers - 24
-- LITERAL 변수 사용시
-- INDEX FULL SCAN / COST - 24 , BUFFER - 24
-- BIND 변수 사용시
-- INDEX RANGE SCAN / COST - 3 , BUFFER - 24

SYS>select /*+ gather_plan_statistics index(t1) */ count(*) from t1 where c1 like '%x%';

  COUNT(*)
----------
         0

SYS>@stat

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  1phpx84ugxfm6, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t1) */ count(*) from t1 where c1 like '%x%'

Plan hash value: 232612676

-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE  |       |      1 |      1 |            |      1 |00:00:00.01 |      24 |
|*  2 |   INDEX FULL SCAN| T1_N1 |      1 |    500 |    24   (0)|      0 |00:00:00.01 |      24 |
-------------------------------------------------------------------------------------------------

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

   2 - filter("C1" LIKE '%x%')


18 rows selected.

-- 실제 존재하는 값을 Literal 변수로
-- INDEX FULL SCAN  / Cost - 24, Buffers - 24
-- 실제 존재하지 않는 값을 Bind 변수로 사용
-- INDEX RANGE SCAN  / Cost - 3, Buffers - 24
-- LITERAL 변수 사용시
-- INDEX FULL SCAN / COST - 24 , BUFFER - 24
-- BIND 변수 사용시
-- INDEX RANGE SCAN / COST - 3 , BUFFER - 24


  • 만일 탐색해야할 Index Leaf Block 수가 많다면,
  • Index Full Scan 이 아닌 Index Fast Full Scan 을 사용하는 것이 좋을 것이다.
  • INDEX FAST FULL SCAN / COST - 8 , BUFFER - 28



SYS>select /*+ gather_plan_statistics index_ffs(t1) */ count(*) from t1 where c1 like '%x%' ;

  COUNT(*)
----------
         0

SYS>@stat

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID  fvfds3ugz2296, child number 0
-------------------------------------
select /*+ gather_plan_statistics index_ffs(t1) */ count(*) from t1 where c1 like '%x%'

Plan hash value: 3675732849

------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE       |       |      1 |      1 |            |      1 |00:00:00.01 |      28 |
|*  2 |   INDEX FAST FULL SCAN| T1_N1 |      1 |    500 |     7   (0)|      0 |00:00:00.01 |      28 |
------------------------------------------------------------------------------------------------------

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

   2 - filter("C1" LIKE '%x%')


18 rows selected.


Hints and Parameters


Reminder
  • Index Range Scan 과 Index Full Scan 사이으 선택은 Optimizer 의 고유 권한이다.
  • Index Full Scan 을 직접 제어 할 수 있는 Hint 는 없다.
  • 단. Oracle 10.2.0.3 부터는 INDEX_RS, INDEX_RS_ASC, INDEX_RS_DESC Hint 사용이 가능하다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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