메뉴 건너뛰기

bysql.net

3. 다양한 인덱스 스캔 방식

2011.02.18 22:33

멋진넘 조회 수:33506

03.다양한 인덱스

?

No

인덱스 스캔 방식

사용하는 Hint

비고

1

Index Range Scan

/*+ Index(Table Alias, Index Name) */

2

Index Full Scan

Index( Full Scan) 형태로 표현

3

Index Unique Scan

/*+ Index(Table Alias, Index Name) */

4

Index Skip Scan

/*+ Index_ss(Table Alias, Index Name) */

5

Index Fast Full Scan

/*+ Index_fss(Table Alias, Index Name) */

6

Index Range Scan Descending

/*+ Index_desc(Table Alias, Index Name) */

7

And-Equal

Index Combine

Index Join

/*+ And_Equal(Table Alias, Index Name, ...) */

/*+ Index_Combine(Table Alias, Index Name, ...) */

/*+ Index_Join(Table Alias, Index Name, ...) */

And-Equal (10g에서 폐기)

 

(1)Index Range Scan
   -루트 블럭에서 리프 블럭까지 수직적으로 탐색한 후에 리프 블럭을 필요한 부분만 스캔하는 방식
   -가장 일반적으로 정상적인 형태의 액세스 방식
   -일반적인 실행계획
   CREATE INDEX EMP_DEMPNO_IDX ON EMP (DEPTNO);
  
   SELECT * FROM EMP WHERE DEPTNO = 20;
  
   ----------------------------------------------------------------------------------------------
   | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
   ----------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |                |     5 |   190 |     2   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     5 |   190 |     2   (0)| 00:00:01 |
   |*  2 |   INDEX RANGE SCAN          | EMP_DEMPNO_IDX |     5 |       |     1   (0)| 00:00:01 |
   ----------------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      2 - access("DEPTNO"=20)
     
   -실행계획에 Index Range이 나타난다고 항상 빠른 속도를 보장하는 것은 아님
    ▶스캔하는 범위(Range)를 얼마만큼 줄일 수 있는냐가 관건임
    ▶인덱스 설계와 SQL 튜닝의 핵심원리 중 하나임
    ▶인덱스의 선두 컬럼이 사용되어야 하며 그렇지 못한 상황에서 인덱스를 강제로 사용하기 위해
      힌트를 사용한다면 Index Full Scan 방식으로 처리됨
  
(2)Index Full Scan
   -수직적 탐색없이 인덱스 리프블럭을 처음부터 끝까지 수평적으로 탐색하는 방식
   -데이터 검색을 위한 최적의 인덱스가 없을 경우 차선으로 선택됨
   -일반적인 실행계획
  
   CREATE INDEX EMP_IDX ON EMP (ENAME, SAL);
  
   SELECT * FROM EMP WHERE SAL > 2000 ORDER BY ENAME;
  
   ---------------------------------------------------------------------------------------
   | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
   ---------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |         |    10 |   380 |     2   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |    10 |   380 |     2   (0)| 00:00:01 |
   |*  2 |   INDEX FULL SCAN           | EMP_IDX |    10 |       |     1   (0)| 00:00:01 |
   ---------------------------------------------------------------------------------------
                                                                                         
   Predicate Information (identified by operation id):                                   
   ---------------------------------------------------                                   
      2 - access("SAL">2000)                                                             
          filter("SAL">2000)                                                             
  
   -Index Full Scan의 효율성
    ▶선두 컬럼(ename)이 조건절이 없으면 옵티마이져는 우선적으로 Table Full Scan을 고려함
      ①대용량 테이블이어서 Table Full Scan의 부담이 크다면 인덱스 활용방법을 고려함
      ②인덱스 스캔단계에서 대부분 레코드를 필터링하고 일부에 대해서만 테이블 액세스하는
        방식이 유리한 경우를 고려
      ③이 경우는 옵티마이져가 Index Full Scan 방식을 선택함
      ④위의 예제(실제 실행계획) : Index Full Scan VS Table Full Scan
        - Index Full Scan
          ----------------------------------------------------------------------------
          | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)|
          ----------------------------------------------------------------------------
          |   0 | SELECT STATEMENT            |         |       |       |     2 (100)|
          |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    38 |     2   (0)|
          |*  2 |   INDEX FULL SCAN           | EMP_IDX |     1 |       |     1   (0)|
          ----------------------------------------------------------------------------
        - Table Full Scan
          ----------------------------------------------------------------------------
          | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)|
          ----------------------------------------------------------------------------
          |   0 | SELECT STATEMENT            |         |       |       |     4 (100)|
          |   1 |  SORT ORDER BY              |         |     1 |    38 |     4  (25)|
          |*  2 |   TABLE ACCESS FULL         | EMP     |     1 |    38 |     3   (0)|
          ----------------------------------------------------------------------------
         
   -Index를 활용한 소트 연산 대체
    ▶Index Full Scan 또한 Index Range Scan과 마찬가지로 그 결과집합이 인덱스컬럼 순으로
      정렬되므로 Sort Order By 연산을 생략할 목적으로 사용될 수 있음(위 PLAN 참고)
    ▶FIRST_ROWS HINT를 사용해서 옵티마이져는 소트연산을 생략하고 전체 집합 중 일부만
      빠르게 리턴할 목적으로 사용할 경우에 사용할 수 있음
    ▶단, 처음 의도와는 다르게 처음부터 끝까지 FETCH 한다면 TABLE FULL SCAN 한 것보다
      훨씬 더 많은 I/O를 일으키고 자원을 낭비하는 결과를 가져옴
     
(3)Index Unique Scan      
   -인덱스를 통해 '=' 조건으로 탐색하는 경우에 작동
   SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM EMP S WHERE EMPNO = 7788;
  
   --------------------------------------------------------------------------------------
   | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
   --------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
   |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
   --------------------------------------------------------------------------------------
    
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      2 - access("EMPNO"=7788)
  
   -인덱스를 'BETWEEN' 조건으로 탐색하는 경우에는 Index Range Scan을 작동
  
   --------------------------------------------------------------------------------------
   | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
   --------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |        |     5 |   190 |     2   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     5 |   190 |     2   (0)| 00:00:01 |
   |*  2 |   INDEX RANGE SCAN          | PK_EMP |     5 |       |     1   (0)| 00:00:01 |
   --------------------------------------------------------------------------------------
  
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      2 - access("EMPNO">=7788)
     
(4)Index Skip Scan
   -Oracle 9i에서 처음 보인 기능
   -인덱스의 선두 컬럼의 Discint Value 개수가 적고 후행 컬럼의 분포도가 좋을 때 유용함
   -Index Skip Scan이 작동하기 위한 조건(교재 참고)
   -In-List Iterator와의 비교
    ▶사용용도는 같지만 동작원리는 분명히 다름(교재 참고)

(5)Index Fast Full Scan
   -Index Fast Full Scan은 Index Full Scan 보다 빠르다. 이유는 인덱스 구조를 무시하고
    인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문이다.
  
   CREATE TABLE BIG_TABLE
   AS
   SELECT ROWNUM AS NO
        , LVL    AS PART
        , OBJECT_NAME
     FROM DBA_OBJECTS
        , (SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL < 11);
       
   CREATE UNIQUE INDEX BIG_TABLE_UI  ON BIG_TABLE(NO);
  
   CREATE        INDEX BIG_TABLE_IDX ON BIG_TABLE(OBJECT_NAME);
  
   -Index Full Scan VS Index Fast Full Scan
   (SYSTEM 계정으로) ALTER SYSTEM FLUSH BUFFER_CACHE;
   SELECT /*+ INDEX(B, BIG_TABLE_IDX) */ COUNT(OBJECT_NAME) FROM SCOTT.BIG_TABLE B;
  
   -----------------------------------------------------------------------------------
   | Id  | Operation        | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
   -----------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT |               |        |       |  3676 (100)|          |
   |   1 |  SORT AGGREGATE  |               |      1 |    66 |            |          |
   |   2 |   INDEX FULL SCAN| BIG_TABLE_IDX |    800K|    50M|  3676   (1)| 00:00:45 |
   -----------------------------------------------------------------------------------

   (SYSTEM 계정으로) ALTER SYSTEM FLUSH BUFFER_CACHE;
   SELECT /*+ INDEX_FFS(B, BIG_TABLE_IDX) */ COUNT(OBJECT_NAME) FROM SCOTT.BIG_TABLE B;

   ----------------------------------------------------------------------------------------
   | Id  | Operation             | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
   ----------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT      |               |        |       |  1001 (100)|          |
   |   1 |  SORT AGGREGATE       |               |      1 |    66 |            |          |
   |   2 |   INDEX FAST FULL SCAN| BIG_TABLE_IDX |    800K|    50M|  1001   (1)| 00:00:13 |
   ----------------------------------------------------------------------------------------
  
   특징 요약 - 표참고

Index Full Scan

Index Fast Full Scan

1.인덱스 구조를 따라 스캔

1.세그먼트 전체를 스캔

2.결과집합 순서보장

2.결과집합 순서보장 안 됨

3.Single Block I/O

3.MultiBlock I/O

4.병렬스캔 불가(파티션 돼 있지 않다면)

4.병렬스캔 가능

5.인덱스에 포함되지 않은 컬럼 조회 시에도 사용가능

5.인덱스에 포함된 컬럼으로만 조회할 때 사용 가능


   
   -튜닝사례(교재와는 다름)   
   (튜닝 전)
   SELECT * FROM BIG_TABLE WHERE OBJECT_NAME LIKE '%PK_EMP%';
  
   --------------------------------------------------------------------------------
   | Id  | Operation         | Name      | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
   --------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT  |           |        |       |  1048 (100)|          |
   |*  1 |  TABLE ACCESS FULL| BIG_TABLE |     42 |  3864 |  1048   (1)| 00:00:13 |
   --------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      1 - filter(("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%PK_EMP%'))
  
   (튜닝 후) - 책과는 좀 다름. 책이 오타가 있음
   SELECT /*+ ORDERED USE_BL(B) NO_MERGE(B) ROWID(B) */ B.*
     FROM (
          SELECT /*+ INDEX_FFS(A, BIG_TABLE_IDX) */ ROWID RID
            FROM BIG_TABLE A
           WHERE OBJECT_NAME LIKE '%PK_EMP%'
          ) A
        , BIG_TABLE B
    WHERE B.ROWID = A.RID;    

   ----------------------------------------------------------------------------------------------
   | Id  | Operation                   | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
   ----------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |               |        |       |  1044 (100)|          |
   |   1 |  NESTED LOOPS               |               |     42 |  4872 |  1044   (1)| 00:00:13 |
   |   2 |   VIEW                      |               |     42 |   504 |  1001   (1)| 00:00:13 |
   |*  3 |    INDEX FAST FULL SCAN     | BIG_TABLE_IDX |     42 |  3276 |  1001   (1)| 00:00:13 |
   |   4 |   TABLE ACCESS BY USER ROWID| BIG_TABLE     |      1 |   104 |     1   (0)| 00:00:01 |
   ----------------------------------------------------------------------------------------------

(6)Index Range Scan Descending
   -Index Range Scan 방식과 동일한 스캔방식

(7)And-Equal / Index Combine / Index Join
   -오라클에서 제공하는 두 개 이상 인덱스를 함께 사용하는 방법들
   ▶And-Equal
     8i에서 도입 / 10g에서 폐기됨 - 11g에서 실제 안됨
   
   SELECT /*+ AND_EQUAL(E, EMP_DEPTNO_IDX, EMP_JOB_IDX) */ *
     FROM EMP E
    WHERE DEPTNO = 30
      AND JOB    = 'SALESMAN';
     
   -------------------------------------------------------------------------------------------
   | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |             |     1 |    38 |     2   (0)| 00:00:01 |
   |*  1 |  TABLE ACCESS BY INDEX ROWID| EMP         |     1 |    38 |     2   (0)| 00:00:01 |
   |*  2 |   INDEX RANGE SCAN          | EMP_JOB_IDX |     3 |       |     1   (0)| 00:00:01 |
   -------------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      1 - filter("DEPTNO"=30)
      2 - access("JOB"='SALESMAN') 
  
   ▶Index Combine
   SELECT /*+ INDEX_COMBINE(E, EMP_DEPTNO_IDX, EMP_JOB_IDX) */ *
     FROM EMP E
    WHERE DEPTNO = 30
      AND JOB    = 'SALESMAN';
     
   ---------------------------------------------------------------------------------------------------
   | Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
   ---------------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT                 |                |     1 |    38 |     3   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID     | EMP            |     1 |    38 |     3   (0)| 00:00:01 |
   |   2 |   BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          |
   |   3 |    BITMAP AND                    |                |       |       |            |          |
   |   4 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
   |*  5 |      INDEX RANGE SCAN            | EMP_JOB_IDX    |       |       |     1   (0)| 00:00:01 |
   |   6 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          |
   |*  7 |      INDEX RANGE SCAN            | EMP_DEMPNO_IDX |       |       |     1   (0)| 00:00:01 |
   ---------------------------------------------------------------------------------------------------
  
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      5 - access("JOB"='SALESMAN')
      7 - access("DEPTNO"=30)

     ①Index Combine은 And-Equal과 마찬가지로 분포도가 좋지 않은 두개 이상의 인덱스를 결합하여
       테이블 Random 를 줄이는 데 목적이 있다.
     ②조건절이 '='이어야 할 필요가 없고, Non-Unique 인덱스일 필요가 없다. 게다가 Bitmap 인덱스를
       이용하므로 조건절이 OR로 결합된 경우에도 유용하다.
   ▶Index Join
     ①테이블간 조인에 사용되는 것과 무관하게 한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스
       없이 결과 집합을 만들 때 사용하는 인덱스 스캔방식이다.
     ②Hash Join 메카니즘을 사용한다.
     ③쿼리에 사용된 컬럼들이 인덱스에 모두 포함될 때만 작동한다. 모두 포함될 필요는 없고
       둘 중 어느 한쪽에 포함되기만 하면 된다.
     ④index$_join$_xxx 의 형태로 PLAN에 표기된다.

   SELECT /*+ INDEX_JOIN(E, EMP_DEPTNO_IDX, EMP_JOB_IDX) */ DEPTNO, JOB
     FROM EMP E
    WHERE DEPTNO = 30
      AND JOB    = 'SALESMAN';
           
   ---------------------------------------------------------------------------------------
   | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
   ---------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT   |                  |     1 |    11 |     3  (34)| 00:00:01 |
   |*  1 |  VIEW              | index$_join$_001 |     1 |    11 |     3  (34)| 00:00:01 |
   |*  2 |   HASH JOIN        |                  |       |       |            |          |
   |*  3 |    INDEX RANGE SCAN| EMP_JOB_IDX      |     1 |    11 |     1   (0)| 00:00:01 |
   |*  4 |    INDEX RANGE SCAN| EMP_DEMPNO_IDX   |     1 |    11 |     1   (0)| 00:00:01 |
   ---------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
      2 - access(ROWID=ROWID)
      3 - access("JOB"='SALESMAN')
      4 - access("DEPTNO"=30)      

 

 

 


  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 멋진
  • 최초작성일: 2011년 2월 20일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.15 149196
59 3. 인덱스 파티셔닝 darkbeom 2011.06.19 53386
» 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.18 33506
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.29 30475
56 2. 파티션 Pruning 실천하자 2011.06.21 25672
55 3. 뷰 Merging 실천하자 2011.05.15 23037
54 3. 해시 조인 file darkbeom 2011.03.20 21206
53 2. 서브쿼리 Unnesting darkbeom 2011.05.15 19292
52 4. 통계정보 Ⅰ darkbeom 2011.04.26 17818
51 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.08 16596
50 7. Sort Area 크기 조정 실천하자 2011.06.13 14702
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.23 14343
48 1. 인덱스 구조 [1] file 실천하자 2011.02.16 13932
47 4. 조인 순서의 중요성 운영자 2011.03.27 13918
46 1. 기본 개념 멋진넘 2011.06.28 13063
45 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.03 12955
44 9. 비트맵 인덱스 file 실천하자 2011.03.05 12068
43 1. 옵티마이저 file 실천하자 2011.04.18 10930
42 6. 히스토그램 실천하자 2011.04.24 10669
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.13 8589