1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2011년 상반기 대용량데이터베이스 스터디
  4. Index Full Scan

Index Full Scan (by bshman) [2011.04.02]

h2.Index Full Scan 이란?

1) 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
2) 최적의 인덱스가 없을 때 차선으로 선택
3) 인덱스 선두 컬럼이 조건절에 없으면 Table Full Scan을 고려하나, Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게
얻을 수 있을 경우 Index Full Scan 선택



 *버전확인*
SQL> SELECT * FROM V$VERSION;

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- 테이블생성
SQL> CREATE TABLE T1(C1 INT,C2 INT);

테이블이 생성되었습니다.

-- 인덱스생성
SQL> CREATE INDEX T1_N1 T1(C1);
 
인덱스가 생성되었습니다.

-- 데이터생성

SQL> INSERT INTO T1
  2  SELECT LEVEL,LEVEL
  3  FROM DUAL
  4  CONNECT BY LEVEL <= 10000;

10000 개의 행이 만들어졌습니다.

-- 플랜확인
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(T1) */ C1
  3  FROM T1;

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

INDEX FULL SCAN을 타지않는다...

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(T1) */ C1
  3  FROM T1
  4  WHERE C1 IS NOT NULL;

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       | 10000 |   126K|    21   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | T1_N1 | 10000 |   126K|    21   (0)| 00:00:01 |
--------------------------------------------------------------------------

INDEX FULL SCAN을 타는걸 확인할수있다.
 
※ 두개의 차이는 조건절의 WHERE C1 IS NOT NULL 이 추가되었다.
   NULL은 INDEXING 되지 않기에 힌트를 먹지않은것이다.
   하단의 플랜결과는 오라클에게 조건절을 주어지므로써, NULL이 존재하지않으니 
   힌트를 먹어라 라는 조건을 부여한걸 확인할수있다.
  <테이블 생성시 DEFAULT를 NOT NULL로 정하면 같은 하단의결과를 확인할수있다.>

h3.MIN/MAX Problem

INDEX FULL SCAN의 가장 큰 용도중 하나가 ORDER BY 나 MIN/MAX를 대체하는것이다.



SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(T1) */
  3  MAX(C1)
  4  FROM T1;

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

위와같은 플랜은 말그대로 INDEX FULL SCAN을 하되 , MIN/MAX값만 가져오겠다라는 뜻이다.
최초 혹은 최후의 Leaf Block만 읽고 SCAN을 중단하는 매우 효과적은 스캔방식이다.

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(T1) */
  3  MAX(C1)
  4  FROM T1
  5  WHERE C1 > 1;

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

위의 내용으로 INDEX RANGE SCAN 에서도 동일하게 구성될걸 확인할수있다.

SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(T1) */
  3  MIN(C1),MAX(C1)
  4  FROM T1 WHERE C1 IS NOT NULL;

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |    21   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |    13 |            |          |
|*  2 |   INDEX FULL SCAN| T1_N1 | 10000 |   126K|    21   (0)| 00:00:01 |
--------------------------------------------------------------------------
MIN/MAX를 Function을 같이 사용하여 확인결과 COST를보면
MIN/MAX FUNCTION의 장점이 사라진걸확인할수있다.

-- 해결책
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(T1) */
  3  MIN(C1)
  4  FROM T1
  5  UNION ALL
  6  SELECT /*+ INDEX(T1) */
  7  MAX(C1)
  8  FROM T1;

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

위와같이 MIN/MAX FUNCTION 을 UNION ALL을 사용해 분리해냄으로써 최적의 SCAN방식인
INDEX FULL SCAN(MIN/MAX)를 선택한걸 확인할 수 있다.

h3.INDEX RANGE SCAN OR FULL SCAN?


SQL> EXPLAIN PLAN FOR
  2  SELECT
  3  COUNT(*)
  4  FROM T1
  5  WHERE C1 LIKE '%'||:B1||'%';

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   500 |  6500 |     7  (15)| 00:00:01 |
---------------------------------------------------------------------------
-- 어라 책에서는 이렇게나오지않는데 .... 확인해보았다
이런 왜 위에작업들이 cost들이 다른가했는데 통계정보를 수집하지않았떤것이다.. ㅠㅠ
근데 위에 쿼리는 동일하게 계속 RANGE SCAN 을 보여주지않았다.

<통계작업후>
SQL> exec dbms_stats.gather_table_stats('bshman', 't1', method_opt=>'for all col
umns size 1', cascade=>true);

SQL> explain plan for
  2  select /* gather_plan_statistics */
  3  count(*)
  4  from t1
  5  where c1 like '%'||:b1||'%';

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


<바인드변수선언>
exec :b1 := '1';
PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> explain plan for
  2  select /*+ gather_plan_statistics */
  3  count(*)
  4  from t1
  5  where c1 like '%'||:b1||'%';

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |  5000 | 30000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> explain plan for
  2  select /*+ gather_plan_statistics index(t1) */
  3  count(*)
  4  from t1
  5  where c1 like '%1%';

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     6 |   243   (3)| 00:00:03 |
|   1 |  SORT AGGREGATE  |       |     1 |     6 |            |          |
|*  2 |   INDEX FULL SCAN| T1_N1 |  5000 | 30000 |   243   (3)| 00:00:03 |
--------------------------------------------------------------------------
이와같은 현상은 바인드변수를 사용시에 발생한다.
아래와같이 바인드변수를 컬럼에 존재하지않은 같은 입력하여도 위와같은 동일한 현상이 발생된다.


만일 탐색해야 할 INDEX LEAF BLOCK의 수가 많다면 INDEX FULL SCAN이 아닌 
INDEX FAST FULL SCAN을 사용하는것이 좋을것이다.

explain plan for
select /*+ gather_plan_statistics index_ffs(t1) */
count(*)
from t1
where c1 like '%x%';

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     6 |    57   (8)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |     6 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T1_N1 |  5000 | 30000 |    57   (8)| 00:00:01 |
-------------------------------------------------------------------------------

HINT AND PARAMETERS
-- INDEX FULL SCAN 과 INDEX RANGE SCAN은 OPTIMIZER의 고유 권한이다.
ORACLE 10.2.0.3 부터 INDEX_RS,INDEX_RS_ASC,INDEX_RS_DESC HINT를 사용할수있다.

  • INDEX_RS : INDEX_RANGE_SCAN을 수행할수있도록 하는 HINT
  • INDEX_RS_ASC : INDEX_RS을 와 거의 동일
  • INDEX_RS_DESC : INDEX_RS를 역순으로 읽어들이는 방식
  1. HOME
  2. [종료]대용량 데이터베이스 스터디
  3. 2011년 상반기 대용량데이터베이스 스터디
  4. Index Full Scan