Index Full Scan

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/display/CORE/Index+Full+Scan?
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

[ 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년 2월 14일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.

문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/display/CORE/Index+Full+Scan?
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.