by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




Index Range Scan

  1. 정의 : 인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식
  2. B*Tree 인덱스의 가장 일반적이고 정상적인 액세스 방식
  3. 중요 point : 인덱스를 스캔하는 범위(range)를 얼마만큼 줄일 수 있느냐와 테이블로 액세스하는 횟수를 얼마만큼 줄일 수 있느냐
  4. 인덱스를 구성하는 선두 컬럼이 조건절에 사용되어야 index range scan 가능
  5. Index range scan과정을 거쳐 생성된 결과집합은 인덱스 컬럼 순으로 정렬 : sort order by 연산을 생략하거나 min/max 값을 빠르게 추출 가능
  6. Test
    set autotrace traceonly explain
    select * from emp where deptno=20;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2468466201
    
    ----------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                |     5 |   160 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     5 |   160 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("DEPTNO"=20)
    

Index Full Scan

  1. 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
  2. 최적의 인덱스가 없을 때 차선으로 선택
  3. 효용성 : 인덱스 선두 컬럼이 조건절에 없으면 Table Full Scan을 고려하나, Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있을 경우 Index Full Scan 선택
  4. 인덱스를 이용한 sort 연산 대체 : first_rows
    1. 전체 집합 중 처음 일부만을 빠르게 리턴해야하므로 옵티마이저는 전략적으로 Index Full Scan 수행
    2. 만약 결과 집합이 많을 경우 데이터 읽기를 멈추지 않고 끝까지 fetch한다면 인덱스 스캔이 테이블 스캔보다 불리 : 많은 I/O를 일으키면서 서버 자원을 낭비 초래
  5. Test
    create index emp_idx on emp(ename,sal);
    
    인덱스가 생성되었습니다.
    
    
    1. 인덱스 첫 번째 컬럼이 조건절에서 사용되지 않았으나 ename으로 정렬해야 하므로 emp_idx 인덱스 사용하는 것이 Table Full Scan보다 유리
      1) INDEX FULL SCAN 선택
    set autotrace traceonly explain
    select * from emp where sal > 2000 order by ename; 
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 737262432
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |    10 |   320 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |    10 |   320 |     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)
    
      2) TABLE ACCESS FULL 선택 : order by ename이 빠짐
    set autotrace traceonly explain
    select * from emp where sal > 2000;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    10 |   320 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMP  |    10 |   320 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("SAL">2000)
    
    
    2. 인덱스를 이용한 소트 연산 대체
      1) 책에서는 first_rows 힌트의 사용으로인해 옵티마이저는 소트연산을 생략함으로써 전체 집합 중 처음 일부만을 빠르게 리턴할 목적으로 
         Index Full Scan방식을 선택 했다고 하나, 실제 테스트에서는 힌트의 사용 유무와 상관없이 Index Full Scan방식이 선택됨
         ※ 각 단계별로 "alter system flush buffer_cache; & alter system flush shared_pool;"를 수행 시에도 아래와 동일한 결과가 나옴
    
    set autotrace traceonly explain
    select /*+ first_rows */ * from emp where sal > 1000 order by ename;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 737262432
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |    13 |   416 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |    13 |   416 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX FULL SCAN           | EMP_IDX |    13 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("SAL">1000)
           filter("SAL">1000)
    
    
    set autotrace traceonly explain
    select * from emp where sal > 1000 order by ename;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 737262432
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |    13 |   416 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |    13 |   416 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX FULL SCAN           | EMP_IDX |    13 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("SAL">1000)
           filter("SAL">1000)
    
    
    set autotrace traceonly explain
    select * from emp where sal > 1000;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    13 |   416 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMP  |    13 |   416 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("SAL">1000)
       
       
    set autotrace traceonly explain
    select /*+ first_rows */ * from emp where sal > 1000;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |    13 |   416 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMP  |    13 |   416 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("SAL">1000)
    

Index Unique Scan

  1. 수직적 탐색만으로 데이터를 찾는 스캔 방식
  2. 작동 시점 : unique 인덱스를 통해 "=" 조건으로 탐색하는 경우
  3. 중복되지 않은 unique한 값을 "="조건으로 검색할 경우 해당 데이터 한 건을 찾는 순간 더 이상 탐색 하지 않음
    between, like, 부등호 조건 : index range scan으로 처리

    unique 결합인덱스에 대해 일부 컬럼만으로 검색 : index range scan으로 처리
    ex) 주문상품 PK 인덱스 = 주문일자 + 고객 ID + 상품ID
    주문일자와 고객 ID로만 검색하는 경우

  4. Test
    set autotrace traceonly explain
    select empno,ename from emp where empno=7788;
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2949544139
    
    --------------------------------------------------------------------------------------
    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |        |     1 |     8 |     1   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     8 |     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)
    

Index Skip Scan

  1. 조건절에 빠진 인덱스 선두 컬럼의 distinct value 개수가 적고 후행 컬럼의 distinct value 개수가 많을 때 유용
  2. Root 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스하는 방식
  3. 첫 번째 리프 블록을 항상 방문하고 마지막 리프 블록도 항상 방문
  4. 버퍼 pinning을 이용한 skip 원리
    1. 상위블록 방문 방법 : 브랜치 블록 버퍼를 pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정 반복
    2. 브랜치 블록들 간에도 서로 연결할 수 있는 주소정보를 갖지 않기 때문에 하나의 브랜치 블록을 모두 처리하고 나면 다시 그 상위 노드를 재방문하는 식으로 진행
    3. Root 또는 브랜치 블록을 재방문하더라도 pinning한 상태이므로 추가적인 블록 I/O는 발생하지 않음
  5. 작동 조건
    1. 최선두 컬럼은 입력하고 중간 컬럼에 대한 조건절이 누락된 경우
    2. Distinct Value가 적은 두 개의 선두컬럼이 모두 누락된 경우
    3. 선두컬럼이 부등호, between, like 같은 범위 검색 조건일 때
  6. 관련 힌트 : index_ss, no_index_ss
  7. In-List Iterator 와 비교
    1. In-list Iterator : 조건절 in-list에 제공된 값의 종류만큼 인덱스 탐색을 반복 수행
    2. 제공하는 값이 적다면 빠른 결과 집합을 얻을 수 있음
  8. Test
    index skip scan과 in-list iterator의 동작 원리
    
    set autotrace traceonly explain
    select * from emp where sal between 2000 and 4000;
           
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3956160932
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     9 |   288 |     3   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| EMP  |     9 |   288 |     3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("SAL">=2000 AND "SAL"<=4000)
       
    
    set autotrace traceonly explain
    select /*+ index_ss(emp emp_idx) */ * from emp where sal between 2000 and 4000;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 302813946
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |         |     9 |   288 |    15   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     9 |   288 |    15   (0)| 00:00:01 |
    |*  2 |   INDEX SKIP SCAN           | EMP_IDX |     7 |       |    14   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("SAL">=2000 AND "SAL"<=4000)
           filter("SAL">=2000 AND "SAL"<=4000)
           
    
    set autotrace traceonly explain      
    select * from emp where sal between 2000 and 4000 and job in ('MANAGER','ANALYST');
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1754328461
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |     4 |   128 |     2   (0)| 00:00:01 |
    |   1 |  INLIST ITERATOR             |             |       |       |            |          |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     4 |   128 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX |     6 |       |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("SAL">=2000 AND "SAL"<=4000)
       3 - access("JOB"='ANALYST' OR "JOB"='MANAGER')
    

Index Fast Full Scan

  1. 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 multiblock read 방식으로 스캔
    • multiblock read 방식 : 디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과 발휘
  2. 물리적으로 디스크에 저장된 순서대로 인덱스 블록을 읽어 들임
  3. 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리 가능(병렬 쿼리 시 direct path read 방식 사용하므로 I/O속도가 더 빨라짐)
  4. 단점
    1. 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과집합이 인덱스 키 순서대로 정렬되지 않음
    2. 쿼리에 사용되는 모든 컬럼이 인덱스 컬럼에 포함 되어 있을 때만 사용 가능
  5. 관련 힌트 : index_ffs, no_index_ffs
  6. Index Full Scan vs. Index Fast Full Scan
    Index Full Scan Index Fast Full Scan
    1. 인덱스 구조를 따라 스캔
    2. 결과 집합 순서 보장
    3. Single Block I/O
    4. 병렬스캔 불가(파티션 되어 있지 않을 경우)
    5. 인덱스에 포함되지 않은 컬럼 조회 시에도 사용 가능
    1. 세그먼트 전체를 스캔
    2. 결과집합 순서 보장 안 됨
    3. multiblock I/O
    4. 병렬스캔 가능
    5. 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능
  7. Test
    SQL> conn sys/loveora as sysdba
    Connected.
    SQL> show parameter multiblock
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count        integer     16
    SQL> conn scott/loveora
    Connected.
    SQL> create index big_table_idx on big_table(object_name);
    
    Index created.
    
    alter system flush buffer_cache;
    alter system flush shared_pool;
    
    
    1. Index Full scan
    
    ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
    select /*+ index(b big_table_idx) */ count(object_name) from big_table b;
    
    COUNT(OBJECT_NAME)
    ------------------
              10000000
              
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.01       0.02          0          0          0           0
    Execute      2      0.00       0.00          0          0          0           0
    Fetch        4     21.27      33.84     101164     101166          0           2
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        8     21.29      33.87     101164     101166          0           2
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 54  (SCOTT)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=50583 pr=50582 pw=0 time=17442436 us)
    10000000   INDEX FULL SCAN BIG_TABLE_IDX (cr=50583 pr=50582 pw=0 time=370001596 us)(object id 53503)
    
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          1   SORT (AGGREGATE)
    10000000    INDEX   MODE: ANALYZED (FULL SCAN) OF 'BIG_TABLE_IDX' (INDEX)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       4        0.00          0.00
      db file sequential read                    101164        0.00          5.52
      SQL*Net message from client                     4        0.00          0.00
    
      1) INDEX FULL SCAN 시 db file sequential read는 Disk I/O와 동일 하게 일어남
      2) db file sequential read : single block read 방식으로 I/O 할 때 발생되는 대기 이벤트
    
    
    2. Index Fast Full Scan
    
    select /*+ index_ffs(b big_table_idx) */ count(object_name) from big_table b;
    COUNT(OBJECT_NAME)
    ------------------
              10000000
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      5.73       9.52      50817      50846          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      5.74       9.52      50817      50846          0           1
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 54  (SCOTT)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
          1  SORT AGGREGATE (cr=50846 pr=50817 pw=0 time=9525459 us)
    10000000   INDEX FAST FULL SCAN BIG_TABLE_IDX (cr=50846 pr=50817 pw=0 time=380001430 us)(object id 53503)
    
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          1   SORT (AGGREGATE)
    10000000    INDEX   MODE: ANALYZED (FAST FULL SCAN) OF 'BIG_TABLE_IDX' 
                   (INDEX)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       2        0.00          0.00
      db file scattered read                       3204        0.00          0.73
      db file sequential read                         1        0.00          0.00
      SQL*Net message from client                     2        0.00          0.00
    
    
      1) INDEX FAST FULL SCAN 시 db file scattered read는 3204회, Disk I/O는 50817 발생
      2) 한번 디스크 I/O Call이 발생할 때마다 평균 15.86개의 블록을 퍼올려 읽음(db_file_multiblock_read_count = 16)
      3) db file scattered read : multi block read 방식으로 I/O 할 때 발생되는 대기 이벤트
      
    alter session set sql_trace=false;
    

Index Range Scan Descending

  1. Index Range Scan과 동일한 스캔 방식이나 내림차순으로 정렬된 결과집합을 얻는다 점이 다름
  2. max값 구할 때 유용
  3. 관련 힌트 : index_desc

And-Equal, Index Combine, Index Join : 두 개 이상 인덱스를 함께 사용할 수 있는 방법

  1. And-Equal
    1. 8i도입되어 10g 부터 폐기 됨
    2. 인덱스 스캔량이 아무리 많더라도 두 인덱스를 결합하고 나서의 테이블 액세스량이 소량일 때 효과 있음
    3. 조건 : 단일 컬럼의 non-unique index & 인덱스 컬럼에 대한 조건절이 "="이어야 함

    4. Test
      set autotrace traceonly explain
      select /*+ and_equal(e emp_deptno_idx emp_job_idx) */ * from emp e where deptno=30 and job='SALESMAN';
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2839249562
      
      ----------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |                |     1 |    32 |     3   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    32 |     3   (0)| 00:00:01 |
      |   2 |   AND-EQUAL                 |                |       |       |            |          |
      |*  3 |    INDEX RANGE SCAN         | EMP_JOB_IDX    |     3 |       |     1   (0)| 00:00:01 |
      |*  4 |    INDEX RANGE SCAN         | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("JOB"='SALESMAN' AND "DEPTNO"=30)
         3 - access("JOB"='SALESMAN')
         4 - access("DEPTNO"=30)
      
  2. Index Combine : 비트맵 인덱스 이용
    1. 목적 : 데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 random 액세스량 줄이기
    2. 조건절이 "='일 필요가 없고, non-unique index일 필요가 없음
    3. "_b_tree_bitmap_plans=true"일 때만 작동. 9i 이후는 true가 기본값임

    4. Test
      set autotrace traceonly explain
      select /*+ index_combine(e emp_deptno_idx emp_job_idx) */ * from emp e where deptno=30 and job='SALESMAN';
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2413831903
      
      ---------------------------------------------------------------------------------------------------
      | Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                 |                |     1 |    32 |     4   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID     | EMP            |     1 |    32 |     4   (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_DEPTNO_IDX |       |       |     1   (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - access("JOB"='SALESMAN')
         7 - access("DEPTNO"=30)
         
       1단계 : 일반 B*Tree 인덱스를 스캔하면서 각 조건에 만족하는 레코드의 rowid 목록 얻기(INDEX RANGE SCAN)
       2단계 : 1단계에서 얻은 rowid목록을 가지고 비트맵 인덱스 구조 만들기(BITMAP CONVERSION FROM ROWIDS)
       3단계 : 비트맵 인덱스에 대한 bit-wise operation 수행(BITMAP AND)
       4단계 : bit-wise operation 수행 결과가 true인 비트 값들을 rowid 값으로 환산해 최종적으로 방문할 테이블 rowid 목록 얻기(BITMAP CONVERSION TO ROWIDS)
       5단계 : rowid를 이용해 테이블 액세스(TABLE ACCESS BY INDEX ROWID)
      
  3. Index Join
    1. 한 테이블에 속한 여러 인덱스를 이용해 테이블 액세스 없이 결과집합을 만들 때 사용하는 인덱스 스캔 방식
    2. Hash join 매카니즘 사용
    3. 쿼리에 사용된 컬럼들이 인덱스에 모두 포함될 때만 작동(둘 중 어느 한쪽에 포함 되기만 하면 됨)
    4. Test
      set autotrace traceonly explain
      select /*+ index_join(e emp_deptno_idx emp_job_idx) */ * from emp e where deptno=30 and job='SALESMAN';
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2211876416
      
      -------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |             |     1 |    32 |     2   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS BY INDEX ROWID| EMP         |     1 |    32 |     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')
      
      set autotrace traceonly explain
      select /*+ index_join(e emp_deptno_idx emp_job_idx) */ deptno,job from emp e where deptno=30 and job='SALESMAN';
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3557895725
      
      ---------------------------------------------------------------------------------------
      | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |                  |     1 |     9 |     3  (34)| 00:00:01 |
      |*  1 |  VIEW              | index$_join$_001 |     1 |     9 |     3  (34)| 00:00:01 |
      |*  2 |   HASH JOIN        |                  |       |       |            |          |
      |*  3 |    INDEX RANGE SCAN| EMP_JOB_IDX      |     1 |     9 |     1   (0)| 00:00:01 |
      |*  4 |    INDEX RANGE SCAN| EMP_DEPTNO_IDX   |     1 |     9 |     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)
         
       1단계 : 크기가 비교적 작은 쪽 인덱스(emp_job_idx)에서 키 값과 rowid를 얻어 PGA 메모리에 해시 맵 생성
              (해시 키 = rowid 가 사용)
       2단계 : 다른 쪽 인덱스(emp_dept_idx)를 스캔하면서 먼저 생성한 해시 맵에 같은 rowid 값을 갖는 레코드가 있는지 탐색
       3단계 : rowid끼리 조인에 성공한 레코드만 결과집합에 포함 : 이 rowid가 가리키는 테이블은 각 인덱스 컬럼에 대한 검색 조건을 모두 만족한다
      

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.