03.다양한 인덱스

?

(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 |
   ----------------------------------------------------------------------------------------
  
   특징 요약 - 표참고

   ----------------------------------------------------------------------------------------------
   | 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
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^