[Oracle] 인덱스 탐색 방식

• INDEX UNIQUE SCAN
• INDEX RANGE SCAN
• INDEX SKIP SCAN
• INDEX FAST FULL SCAN
• INDEX FULL SCAN

 

테이블 생성

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER(6),
    FIRST_NAME VARCHAR2(20),
    LAST_NAME VARCHAR2(25) NOT NULL,
    EMAIL VARCHAR2(25) NOT NULL,
    PHONE_NUMBER VARCHAR2(20),
    HIRE_DATE DATE NOT NULL,
    JOB_ID VARCHAR2(10) NOT NULL,
    SALARY NUMBER(8,2),
    COMMISSION_PCT NUMBER(6),
    MANAGER_ID NUMBER(6),
    DEPARTMENT_ID NUMBER(4)
);
CREATE TABLE DEPARTMENTS (
    DEPARTMENT_ID NUMBER(4),
    DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
    MANAGER_ID NUMBER(6),
    LOCATION_ID NUMBER(4)
);

인덱스 생성

CREATE UNIQUE INDEX PK_EMPLOYEES ON EMPLOYEES(EMPLOYEE_ID);
ALTER TABLE EMPLOYEES ADD (
    CONSTRAINT PK_EMPLOYEES
    PRIMARY KEY (EMPLOYEE_ID)
);
CREATE UNIQUE INDEX PK_DEPARTMENTS ON DEPARTMENTS(DEPARTMENT_ID);
ALTER TABLE DEPARTMENTS ADD (
    CONSTRAINT PK_DEPARTMENTS
    PRIMARY KEY (DEPARTMENT_ID)
);

 

데이터 생성

INSERT INTO EMPLOYEES
SELECT LEVEL AS EMPLOYEE_ID,
      DBMS_RANDOM.STRING('U', 7) AS FIRST_NAME,
      DBMS_RANDOM.STRING('U', 10) AS LAST_NAME,
      DBMS_RANDOM.STRING('L', 10) || '@' || DBMS_RANDOM.STRING('L', 5) || '.' || DBMS_RANDOM.STRING('L', 3) AS EMAIL,
      ROUND(DBMS_RANDOM.VALUE(100,999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999)) || '-' || ROUND(DBMS_RANDOM.VALUE(1000,9999)) AS PHONT_NUMBER,
      SYSDATE AS HIRE_DATE,
      DBMS_RANDOM.STRING('U', 5) AS JOB_ID,
      TRUNC(DBMS_RANDOM.VALUE(0,999999),2) AS SALARY, 
      TRUNC(DBMS_RANDOM.VALUE(),2) AS COMMISSION_PCT,
      ROUND(DBMS_RANDOM.VALUE(0,999999)) AS MANAGER_ID,
      ROUND(DBMS_RANDOM.VALUE(0,9999)) AS DEPARTMENT_ID
FROM DUAL
CONNECT BY LEVEL < 1000000;

INSERT INTO DEPARTMENTS
SELECT LEVEL AS DEPARTMENT_ID,
      DBMS_RANDOM.STRING('U', 7) AS DEPARTMENT_NAME,
      ROUND(DBMS_RANDOM.VALUE(0,999999)) AS MANAGER_ID,
      ROUND(DBMS_RANDOM.VALUE(0,9999)) AS LOCATION_ID
FROM DUAL
CONNECT BY LEVEL < 10000;

 

실행계획 확인

EXPLAIN PLAN FOR  -- SQL 문 앞에 붙이기
...

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

---

1. INDEX UNIQUE SCAN

INDEX UNIQUE SCAN은

Unique Index를 동등 조건 '=' 으로 탐색하는 경우 사용한다.

수직적 탐색만으로 데이터를 찾으며 데이터 한 건을 찾는 순간 더 이상 탐색을 하지 않고, 하나의 건만 결과로 반환한다.

 

※ Primary Key 또는 Unique Index가 있는 경우 사용할 수 있다.

EXPLAIN PLAN FOR
SELECT EMPLOYEE_ID,
      FIRST_NAME,
      LAST_NAME,
      SALARY
FROM EMPLOYEES     
WHERE EMPLOYEE_ID = 100;

실행 계획 확인

Plan hash value: 3456876893
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |    1 |    52 |    0  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    52 |    0  (0)| 00:00:01 |
|*  2 |  INDEX UNIQUE SCAN        | PK_EMPLOYEES |    1 |      |    0  (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("EMPLOYEE_ID"=100)

 

UNIQUE INDEX로 설정 해줘야함.

각 경우에 따라 보자. (EMAIL은 UNIQUE한 값이다.)

 

 

1. UNIQUE INDEX 설정 안한 경우

SELECT EMPLOYEE_ID,
      FIRST_NAME,
      LAST_NAME,
      SALARY,
      DEPARTMENT_ID
FROM EMPLOYEES A     
WHERE EMAIL = 'hnjalnjwif@fqtfs.bno';

Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |          |  156 | 12324 |  3853  (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |  156 | 12324 |  3853  (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("EMAIL"='hnjalnjwif@fqtfs.bno')
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

→ TABLE FULL SCAN

 

2. 그냥 INDEX로 설정한 경우

-- CREATE INDEX INDEX2_EMPLOYEES ON EMPLOYEES(EMAIL);

SELECT EMPLOYEE_ID,
      FIRST_NAME,
      LAST_NAME,
      SALARY,
      DEPARTMENT_ID
FROM EMPLOYEES A     
WHERE EMAIL = 'hnjalnjwif@fqtfs.bno';

Plan hash value: 2428944127
------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                  |    1 |    79 |    5  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |    1 |    79 |    5  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | INDEX2_EMPLOYEES |    1 |      |    3  (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("EMAIL"='hnjalnjwif@fqtfs.bno')
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

→ INDEX RANGE SCAN

 

3. UNIQUE INDEX로 설정한 경우

-- CREATE UNIQUE INDEX INDEX2_EMPLOYEES ON EMPLOYEES(EMAIL);

SELECT /*+ INDEX(A INDEX2_EMPLOYEES)*/ EMPLOYEE_ID,
      FIRST_NAME,
      LAST_NAME,
      SALARY,
      DEPARTMENT_ID
FROM EMPLOYEES A     
WHERE EMAIL = 'hnjalnjwif@fqtfs.bno'; 
Plan hash value: 261111229
------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                  |    1 |    79 |    3  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |    1 |    79 |    3  (0)| 00:00:01 |
|*  2 |  INDEX UNIQUE SCAN        | INDEX2_EMPLOYEES |    1 |      |    2  (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("EMAIL"='hnjalnjwif@fqtfs.bno')

→ INDEX UNIQUE SCAN

→ UNIQUE INDEX로 지정해줘야 INDEX UNIQUE SCAN이 가능한 것을 알 수 있다.

 

---

2. INDEX RANGE SCAN

INDEX를 수직 탐색 후 필요한 범위까지만 탐색

 

 

쿼리 실행

EXPLAIN PLAN FOR
SELECT *
FROM DEPARTMENTS
WHERE DEPARTMENT_ID < 80;

실행 계획 확인

Plan hash value: 4245464409

----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                |    79 |  4424 |    1  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS    |    79 |  4424 |    1  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | PK_DEPARTMENTS |    79 |      |    1  (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("DEPARTMENT_ID"<80)

Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

 

---

3. INDEX FULL SCAN

첫번째 리프블록부터 인덱스 전체를 탐색하는 방법

 

TABLE FULL SCAN의 부담이 크거나

테이블 전체를 탐색하는데 정렬 작업을 생략하기 위해

INDEX를 사용하는 것이 유리하다.

 

 

쿼리 실행

EXPLAIN PLAN FOR
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID IS NOT NULL
ORDER BY EMPLOYEE_ID;

실행 계획 확인

Plan hash value: 4141328573
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |  928K|  117M|  2009  (1)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |  928K|  117M|  2009  (1)| 00:00:01 |
|  2 |  INDEX FULL SCAN          | PK_EMPLOYEES |  928K|      |  2009  (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

 

INDEX FULL SCAN이 사용되는 경우

• COUNT : 테이블 액세스 없이 인덱스만으로도 COUNT 값을 받아올 수 있다.
• MIN, MAX : 인덱스의 시작/끝 한 번만 확인하므로 한 개의 블록만 읽어 결과를 얻을 수 있다.

테이블엔 데이터 전체가 저장되어 있고 , 인덱스에 저장된 테이터는 일부 칼럼이다.

인덱스가 차지하는 데이터의 공간이 테이블보다 훨씬 적기 때문에

테이블 전체보다 인덱스 전체를 SCAN하는 COST가 적다.

 

※ ORDER BY 를 빼면 ? ( 정렬작업이 필요하지 않으면 ? )

TABLE FULL SCAN이 MULTI BLOCK I/O로 데이터를 읽어오기 때문에,

SINGLE BLOCK I/O로 읽어오는 INDEX FULL SCAN보다 더 효율적으로 처리할 수도 있다.

(경우에 따라 다름)

같은 쿼리에 ORDER BY를 제외하고 실행해본다.

EXPLAIN PLAN FOR
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID IS NOT NULL ;
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |          |  928K|  117M|  3856  (1)| 00:00:01 |
|  1 |  TABLE ACCESS FULL| EMPLOYEES |  928K|  117M|  3856  (1)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

→ TABLE FULL SCAN 인 실행 계획을 확인할 수 있다.

 

---

4. INDEX FAST FULL SCAN

INDEX FAST FULL SCAN은 INDEX FULL SCAN 보다 빠르다

--> INDEX FAST FULL SCAN은 MULTI BLOCK I/O가 가능하기 때문

 

• INDEX FULL SCAN : 데이터의 논리적인 순서로 결과를 얻는다
• INDEX FAST FULL SCAN : 데이터의 물리적 저장 구조의 순서로 받아온다. (정렬되지 않은 상태)

---

INDEX FULL SCAN vs INDEX FAST FULL SCAN

앞서 INDEX FULL SCAN 부분에서,

COUNT를 사용할 때 INDEX FULL SCAN을 사용한다고 했는데,

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM EMPLOYEES;

이 쿼리에 대한 실행 계획을 확인해보니 INDEX FAST FULL SCAN 이었다.

 

 

같은 조건의 데이터를 100건만 생성하여 새 테이블 EMPLOYEES_2 를 만들어 수행해주었다.

• EMPLOYEES --> 데이터 100만건
• EMPLOYEES_2 --> 데이터 100건

 

• EMPLOYEES 에서 COUNT 함수를 사용했을 때의 실행 계획

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM EMPLOYEES;

Plan hash value: 611563155
------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |              |    1 |  546  (1)| 00:00:01 |
|  1 |  SORT AGGREGATE      |              |    1 |            |          |
|  2 |  INDEX FAST FULL SCAN| PK_EMPLOYEES |  928K|  546  (1)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

• EMPLOYEES_2 에서 COUNT 함수를 사용했을 때의 실행 계획

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM EMPLOYEES_2;

Plan hash value: 551730614
---------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT |                |    1 |    1  (0)| 00:00:01 |
|  1 |  SORT AGGREGATE  |                |    1 |            |          |
|  2 |  INDEX FULL SCAN| PK_EMPLOYEES_2 |    99 |    1  (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
  - dynamic statistics used: dynamic sampling (level=2)

같은 조건이지만

데이터가 100건일 때는 INDEX FULL SCAN,

데이터가 100만건일 때는 INDEX FAST FULL SCAN을 하는 것을 확인할 수 있었다.

→ 데이터가 많을 때는 옵티마이저가 INDEX FAST FULL SCAN이 더 COST가 낮은 것으로 판단했다.

→ INDEX FULL SCAN을 사용하는데 DISK I/O 가 많이 발생할 때 사용하면 효과적이다.

    순차적으로 데이터를 읽어오기 위해 발생했던 대기 이벤트를 줄일 수 있다. (DISK I/O가 급격히 감소하는건 아님)

 

 

 

INDEX FULL SCAN 과 INDEX FAST FULL SCAN 의 TRACE 분석

 

10046 TRACE 결과값 (level 12)

• INDEX FAST FULL SCAN

SELECT COUNT(*)
FROM EMPLOYEES
call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      0.00          0          4          0          0
Execute      1      0.00      0.00          0          0          0          0
Fetch        2      0.03      0.03          0      1933          0          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04      0.04          0      1937          0          1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        1          1          1  SORT AGGREGATE (cr=1933 pr=0 pw=0 time=37036 us starts=1)
  1000000    1000000    1000000  INDEX FAST FULL SCAN PK_EMPLOYEES (cr=1933 pr=0 pw=0 time=43885 us starts=1 cost=546 size=0 card=928256)(object id 29495)
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
  SQL*Net message from client                    2        9.07          9.07

 

• INDEX FULL SCAN

SELECT /*+ INDEX(A) */ COUNT(*)
FROM EMPLOYEES A
call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      0.00          0          4          0          0
Execute      1      0.00      0.00          0          0          0          0
Fetch        2      0.03      0.03          0      1877          0          1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03      0.03          0      1881          0          1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        1          1          1  SORT AGGREGATE (cr=1877 pr=0 pw=0 time=33932 us starts=1)
  1000000    1000000    1000000  INDEX FULL SCAN PK_EMPLOYEES (cr=1877 pr=0 pw=0 time=44809 us starts=1 cost=2009 size=0 card=928256)(object id 29495)
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
  SQL*Net message from client                    2        9.53          9.53

→ INDEX FULL SCAN과 비교하여 INDEX FAST FULL SCAN의 대기 이벤트 시간이 소폭 감소했다.