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 이다.
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
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 의 성능은 두 가지 요소에 의해 결정된다.
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))
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 문장이 최선이다.
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가 되기때문에 성능상 많은 영향은 없음)