Index Full Scan (by jongmali) [2009.04.30]
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 페이지
h3.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'을 참고하였습니다.*