[아이티윌 오라클 DBA 과정 91기] 260203 TIL

2026. 2. 3. 18:13ㆍCourses/아이티윌 오라클 DBA 과정

I/O

Random I/O

레코드 간 논리적, 물리적인 순서를 따르지 않고 한 건을 읽기 위해 한 블록씩 접근
Random I/O 성능 향상
- buffer pinning
- prefetch

Sequential I/O

레코드 간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
한번 액세스로 블록 내 모든 레코드를 읽어야 함
테이블 Full Scan
인덱스 Range Scan

인덱스 튜닝

조합 인덱스 필요성

last_name = 'King' and first_name = 'Steven'

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name
        from user_indexes ix, user_ind_columns ic
        where ix.index_name = ic.index_name
        and ix.table_name = 'EMP';  2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
EMP_IDX                        UNIQUE    EMPLOYEE_ID
EMP_DEPT_IDX                   NONUNIQUE DEPARTMENT_ID
EMP_NAME_IDX                   NONUNIQUE LAST_NAME

HR@ora19c> set autotrace traceonly explai
HR@ora19c> select * from hr.emp where last_name = 'King' and first_name = 'Steven';

Execution Plan
----------------------------------------------------------
Plan hash value: 2092603121

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    69 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   1 - filter("FIRST_NAME"='Steven')
   2 - access("LAST_NAME"='King')

emp_name_idx는 last_name에만 인덱스가 걸려있기 때문에 last_name = 'King' 조건에 대해서만 인덱스를 통해 찾을 수 있고 fist_name = ‘Steven’ 조건에 대해서는 테이블에 가서 필터링해야 함 → 불필요한 I/O 발생

EMP_LNAME_IDX, EMP_FNAME_IDX 인덱스 생성

HR@ora19c> set autotrace off
HR@ora19c> drop index hr.emp_name_idx;

Index dropped.

HR@ora19c> create index hr.emp_lname_idx on hr.emp(last_name);

Index created.

HR@ora19c> create index hr.emp_fnmae_idx on hr.emp(first_name);

Index created.

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name
        from user_indexes ix, user_ind_columns ic
        where ix.index_name = ic.index_name
        and ix.table_name = 'EMP';  2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
EMP_IDX                        UNIQUE    EMPLOYEE_ID
EMP_DEPT_IDX                   NONUNIQUE DEPARTMENT_ID
EMP_LNAME_IDX                  NONUNIQUE LAST_NAME
EMP_FNAME_IDX                  NONUNIQUE FIRST_NAME

last_name = 'King' and first_name = 'Steven'

HR@ora19c> select * from hr.emp where last_name = 'King' and first_name = 'Steven';

Execution Plan
----------------------------------------------------------
Plan hash value: 1251419985

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    69 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP           |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_LNAME_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - filter("FIRST_NAME"='Steven')
   2 - access("LAST_NAME"='King')

각 조건에 해당하는 인덱스가 있지만 EMP_LNAME_IDX와 EMP_FNAME_IDX 중 더 효율적이라고 판단되는 EMP_LNAME_ID만 사용

last_name = 'King'

HR@ora19c> select * from hr.emp where last_name = 'King';

Execution Plan
----------------------------------------------------------
Plan hash value: 1251419985

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP           |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_LNAME_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("LAST_NAME"='King')

last_name = 'King' 조건으로만 조회하므로 EMP_LNAME_IDX 사용

first_name = 'Steven'

HR@ora19c> select * from hr.emp where first_name = 'Steven';

Execution Plan
----------------------------------------------------------
Plan hash value: 1605181375

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP           |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_FNAME_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("FIRST_NAME"='Steven')

first_name = 'Steven' 조건으로만 조회하므로 EMP_FNAME_IDX 사용

Index Merge Scan(`and_eaual` 힌트)

HR@ora19c> select /*+ and_equal(e emp_lname_idx emp_fname_idx) */ * from hr.emp e where last_name = 'King' and first_name = 'Steven';

Execution Plan
----------------------------------------------------------
\Plan hash value: 3583215703

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    69 |     2   (0)| 00:00:01 |
|   2 |   AND-EQUAL                 |               |       |       |            |          |
|*  3 |    INDEX RANGE SCAN         | EMP_FNAME_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_LNAME_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - filter("LAST_NAME"='King' AND "FIRST_NAME"='Steven')
   3 - access("FIRST_NAME"='Steven')
   4 - access("LAST_NAME"='King')

and_equal 힌트를 통해 EMP_LNAME_IDX와 EMP_FNAME_IDX를 모두 사용
1. EMP_FNAME_IDX를 통해 first_name = 'Steven' 조건을 만족하는 rowid를 찾음(Id 3)
2. EMP_LNAME_IDX를 통해 last_name = 'King' 조건을 만족하는 rowid를 찾음(Id 4)
AND-EQUAL 연산을 통해 두 조건을 모두 만족하는(3, 4번 연산에서 찾은 rowid가 서로 일치하는) rowid를 찾음 ⇒ Index Merge Scan (Id 2)

# 아래 쿼리 같이 수행
select id3.rowid
from (select e.rowid from hr.emp e where last_name = 'King') id3,
        (select e.rowid from hr.emp e where first_name = 'Steven') id4
where id3.rowid = id4.rowid;

ROWID
------------------
AAAScmAAHAAAAHbAAA

찾은 rowid로 테이블을 찾아가 데이터 읽음(Id 1)

select *
from hr.emp
where rowid in (
    select id3.rowid
    from (select e.rowid from hr.emp e where last_name = 'King') id3,
            (select e.rowid from hr.emp e where first_name = 'Steven') id4
    where id3.rowid = id4.rowid
);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES      24200                                      90

조합 인덱스

Where 조건절에 자주 함게 사용되는 컬럼들을 하나의 인덱스로 생성

조합 인덱스 생성

HR@ora19c> set autotrace off
HR@ora19c> create index hr.emp_name_idx on hr.emp(last_name, first_name);

Index created.

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name
        from user_indexes ix, user_ind_columns ic
        where ix.index_name = ic.index_name
        and ix.table_name = 'EMP';  2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
EMP_IDX                        UNIQUE    EMPLOYEE_ID
EMP_DEPT_IDX                   NONUNIQUE DEPARTMENT_ID
EMP_LNAME_IDX                  NONUNIQUE LAST_NAME
EMP_FNAME_IDX                  NONUNIQUE FIRST_NAME
EMP_NAME_IDX                   NONUNIQUE LAST_NAME
EMP_NAME_IDX                   NONUNIQUE FIRST_NAME

6 rows selected.

EMP_NAME_IDX는 아래와 같은 형태

select last_name, first_name, rowid from hr.emp;

LAST_NAME                 FIRST_NAME           ROWID
------------------------- -------------------- ------------------
...
King                      Janette              AAAScmAAHAAAAHbAA4
King                      Steven               AAAScmAAHAAAAHbAAA
Kochhar                   Neena                AAAScmAAHAAAAHbAAB
Kumar                     Sundita              AAAScmAAHAAAAHbABJ
...

107 rows selected.

last_name = 'King' and first_name = 'Steven'

HR@ora19c> set autotrace traceonly explain
HR@ora19c> select * from hr.emp e where last_name = 'King' and first_name = 'Steven';

Execution Plan
----------------------------------------------------------
Plan hash value: 2092603121

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("LAST_NAME"='King' AND "FIRST_NAME"='Steven')

LAST_NAME='King' AND FIRST_NAME='Steven' 조건을 만족하는 rowid를 EMP_NAME_IDX를 통해 찾음(Id 2)

select rowid from hr.emp where last_name = 'King' and first_name = 'Steven';

ROWID
------------------
AAAScmAAHAAAAHbAAA

찾은 rowid로 테이블을 찾아가 데이터 읽어 active set 결과에 추가(Id 1)

select *
from hr.emp
where rowid in (select rowid from hr.emp where last_name = 'King' and first_name = 'Steven');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100 Steven               King                      SKING                     515.123.4567         17-JUN-03 AD_PRES      24200                                      90

2번 연산으로 다시 와서 인덱스에서 King Steven을 찾아보고 없으면 검색 종료
active set 결과를 user에게 전달

조합 인덱스의 선행 컬럼

조합 인덱스는 선행 컬럼을 잘 만들어야 함
기준
- 혼자서도 자주 사용되는 컬럼을 선행 컬럼으로 설정하는 것이 좋음
- 범위를 줄일 수 있는(중복성이 적은) 컬럼을 선행 컬럼으로 설정하는게 좋음
- 비교 연산자(=)

단일 컬럼 인덱스 삭제(EMP_LNAME_IDX, EMP_FNAME_IDX)

HR@ora19c> drop index hr.emp_lname_idx;

Index dropped.

HR@ora19c> drop index hr.emp_fname_idx;

Index dropped.

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name
        from user_indexes ix, user_ind_columns ic
        where ix.index_name = ic.index_name
        and ix.table_name = 'EMP';  2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
EMP_IDX                        UNIQUE    EMPLOYEE_ID
EMP_DEPT_IDX                   NONUNIQUE DEPARTMENT_ID
EMP_NAME_IDX                   NONUNIQUE LAST_NAME
EMP_NAME_IDX                   NONUNIQUE FIRST_NAME

선행 컬럼으로 조회

HR@ora19c> select * from hr.emp where last_name = 'King';

Execution Plan
----------------------------------------------------------
Plan hash value: 2092603121

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("LAST_NAME"='King')

last_name = 'King' 조건으로 조회했을 때는 last_name이 선행 컬럼이기 때문에 EMP_NAME_IDX 인덱스 사용 가능

Index Skip Scan

조합 인덱스를 사용할 때 선행 컬럼이 조건절에 사용되지 않은 경우 Index Skip Scan 사용 가능
이 방식은 선두 컬럼의 distinct value(고유 값)가 적고 후속 컬럼의 고유 값이 많을 때 유용

HR@ora19c> select * from hr.emp where first_name = 'Steven';

Execution Plan
----------------------------------------------------------
Plan hash value: 2645326825

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP          |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | EMP_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("FIRST_NAME"='Steven')
       filter("FIRST_NAME"='Steven')

HR@ora19c> select column_name, num_distinct from user_tab_columns where table_name = 'EMP';

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
EMPLOYEE_ID                             107
FIRST_NAME                               91
LAST_NAME                               102
EMAIL                                   107
PHONE_NUMBER                            107
HIRE_DATE                                98
JOB_ID                                   19
SALARY                                   58
COMMISSION_PCT                            7
MANAGER_ID                               18
DEPARTMENT_ID                            11

11 rows selected.

Index Full Scan

인덱스 리프 블록 처음부터 끝까지 탐색
Single Block I/O
속도 느림
정렬된 집합으로 수행
db file sequential read 대기 이벤트 발생

HR@ora19c> select last_name, first_name from hr.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 1448724716

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |   107 |  1605 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_NAME_IDX |   107 |  1605 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

HR@ora19c> select count(*) from hr.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 660937672

--------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |         |     1 |            |          |
|   2 |   INDEX FULL SCAN| EMP_IDX |   107 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';  2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
EMP_IDX                        UNIQUE    EMPLOYEE_ID
EMP_DEPT_IDX                   NONUNIQUE DEPARTMENT_ID
EMP_NAME_IDX                   NONUNIQUE LAST_NAME
EMP_NAME_IDX                   NONUNIQUE FIRST_NAME

HR@ora19c> select c.column_name, u.constraint_name, u.constraint_type,u.search_condition,u.index_name
from user_constraints u, user_cons_columns c
where u.constraint_name = c.constraint_name
and u.table_name = 'EMP';  2    3    4

COLUMN_NAME                    CONSTRAINT_NAME                C SEARCH_CONDITION               INDEX_NAME
------------------------------ ------------------------------ - ------------------------------ ------------------------------
LAST_NAME                      SYS_C007866                    C "LAST_NAME" IS NOT NULL
EMAIL                          SYS_C007867                    C "EMAIL" IS NOT NULL
HIRE_DATE                      SYS_C007868                    C "HIRE_DATE" IS NOT NULL
JOB_ID                         SYS_C007869                    C "JOB_ID" IS NOT NULL
EMPLOYEE_ID                    EMP_ID_PK                      P                                EMP_IDX

Index Fast Full Scan

인덱스 리프 블록 처음부터 끝까지 탐색
Multi Block I/O
속도가 빠름
정렬된 집합으로 수행하지 않음
db file scattered read 대기 이벤트 발생

HR@ora19c> select /*+ index_ffs(e emp_idx) */ count(*) from hr.emp e;

Execution Plan
----------------------------------------------------------
Plan hash value: 3644266057

-------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| EMP_IDX |   107 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

전체 건수를 셀 때는 index fast full scan을 쓰는 것이 좋음

index full scan보다 index fast full scan이 더 빠른 이유?

두 방식 모두 인덱스 전체를 스캔하는데 index fast full scan은 Multiblock I/O를 통해 1번의 call 당 여러 블록씩(db_file_multilblock_read_count) 읽어오기 때문에 index full scan보다 빠름
병렬 처리 가능

HR@ora19c> select /*+ index_ffs(e emp_idx) parallel_index(e, emp_idx, 2) */ count(*) from hr.emp e;

Execution Plan
----------------------------------------------------------
Plan hash value: 518463889

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |          |   107 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       INDEX FAST FULL SCAN| EMP_IDX  |   107 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------

인덱스를 이용한 정렬

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name, ic.column_position, ic.descend
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';   2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME                    COLUMN_POSITION DESC
------------------------------ --------- ------------------------------ --------------- ----
EMP_IDX                        UNIQUE    EMPLOYEE_ID                                  1 ASC
EMP_DEPT_IDX                   NONUNIQUE DEPARTMENT_ID                                1 ASC
EMP_NAME_IDX                   NONUNIQUE LAST_NAME                                    1 ASC
EMP_NAME_IDX                   NONUNIQUE FIRST_NAME                                   2 ASC

인덱스는 정렬된 자료 구조
현재 모든 인덱스는 오름차순 정렬되어 있음

HR@ora19c> select employee_id from hr.emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 1759850971

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |   107 |   428 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_IDX |   107 |   428 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Index Full Scan Descending

인덱스 리프 블록 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순 정렬된 결과 집합을 얻음

HR@ora19c> select employee_id from hr.emp order by 1 desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 2973123126

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |   107 |   428 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN DESCENDING| EMP_IDX |   107 |   428 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

index_desc 힌트 사용

HR@ora19c> select /*+ index_desc(e emp_idx) */ employee_id from hr.emp e;

Execution Plan
----------------------------------------------------------
Plan hash value: 2973123126

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |   107 |   428 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN DESCENDING| EMP_IDX |   107 |   428 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

index를 descending scan하고 row 수를 1개로 제한하면 최대 값 조회 가능

HR@ora19c> select /*+ index_desc(e emp_idx) */ employee_id from hr.emp e where rownum <= 1;

EMPLOYEE_ID
-----------
        206

Execution Plan
----------------------------------------------------------
Plan hash value: 3857570976

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY              |         |       |       |            |          |
|   2 |   INDEX FULL SCAN DESCENDING| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=1)

INDEX FULL SCAN (MIN/MAX)

인덱스 맨 왼쪽 리프 블록을 찾아가면 min 값을 찾을 수 있고, 맨 오른쪽 리프 블록을 찾아가면 max 값을 찾을 수 있음

HR@ora19c> select max(employee_id) from hr.emp;

MAX(EMPLOYEE_ID)
----------------
             206

Execution Plan
----------------------------------------------------------
Plan hash value: 2403234595

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |         |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

HR@ora19c> select min(employee_id) from hr.emp;

MIN(EMPLOYEE_ID)
----------------
             100

Execution Plan
----------------------------------------------------------
Plan hash value: 2403234595

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |         |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

min 값과 max 값을 동시에 조회하면 인덱스 전체를 탐색해야 함 → Index Full Scan

HR@ora19c> select min(employee_id), max(employee_id) from hr.emp;

MIN(EMPLOYEE_ID) MAX(EMPLOYEE_ID)
---------------- ----------------
             100              206

Execution Plan
----------------------------------------------------------
Plan hash value: 660937672

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |         |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| EMP_IDX |   107 |   428 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

min과 max를 따로 구하고 union all 연산자를 이용하는 것이 더 효율적 → But 결과 집합이 다름

HR@ora19c> select min(employee_id) from hr.emp
union all
select max(employee_id) from hr.emp;  2    3

MIN(EMPLOYEE_ID)
----------------
             100
             206

Execution Plan
----------------------------------------------------------
Plan hash value: 2740428894

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     2 |     8 |     2   (0)| 00:00:01 |
|   1 |  UNION-ALL                  |         |       |       |            |          |
|   2 |   SORT AGGREGATE            |         |     1 |     4 |            |          |
|   3 |    INDEX FULL SCAN (MIN/MAX)| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE            |         |     1 |     4 |            |          |
|   5 |    INDEX FULL SCAN (MIN/MAX)| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

인라인 뷰를 이용해서 한 줄로 출력

HR@ora19c> select min(id) min, max(id) max
from (select min(employee_id) id from hr.emp
union all
select max(employee_id) id from hr.emp);  2    3    4

       MIN        MAX
---------- ----------
       100        206

Execution Plan
----------------------------------------------------------
Plan hash value: 2846192510

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

스칼라 서브 쿼리를 사용하여 한 줄로 출력

HR@ora19c> select (select min(employee_id) from hr.emp) min, (select max(employee_id) from hr.emp) max from dual;

       MIN        MAX
---------- ----------
       100        206

Execution Plan
----------------------------------------------------------
Plan hash value: 3491623073

--------------------------------------------------------------------------------------
| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         |     1 |       |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |         |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE            |         |     1 |     4 |            |          |
|   4 |   INDEX FULL SCAN (MIN/MAX)| EMP_IDX |     1 |     4 |     1   (0)| 00:00:01 |
|   5 |  FAST DUAL                 |         |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

인덱스 컬럼 가공

insa 테이블 및 인덱스 생성

drop table hr.insa purge;

create table hr.insa
tablespace users
as
select employee_id id, last_name name, salary sal, hire_date hire, to_char(department_id) dept_id
from hr.employees;

create index hr.insa_name_idx on hr.insa(name) tablespace users;
create index hr.insa_sal_idx on hr.insa(sal) tablespace users;
create index hr.insa_hire_idx on hr.insa(hire) tablespace users;
create index hr.insa_dept_idx on hr.insa(dept_id) tablespace users;

HR@ora19c> select ix.index_name, ix.uniqueness, ic.column_name, ic.column_position, ic.descend
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'INSA';  2    3    4

INDEX_NAME                     UNIQUENES COLUMN_NAME                    COLUMN_POSITION DESC
------------------------------ --------- ------------------------------ --------------- ----
INSA_NAME_IDX                  NONUNIQUE NAME                                         1 ASC
INSA_SAL_IDX                   NONUNIQUE SAL                                          1 ASC
INSA_HIRE_IDX                  NONUNIQUE HIRE                                         1 ASC
INSA_DEPT_IDX                  NONUNIQUE DEPT_ID                                      1 ASC

name이 Ba로 시작하는 사원 조회

HR@ora19c> select * from hr.insa where substr(name, 1, 2) = 'Ba';

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       116 Baida                                2900 24-DEC-05 30
       167 Banda                                6200 21-APR-08 80
       172 Bates                                7300 24-MAR-07 80
       204 Baer                                10000 07-JUN-02 70

Execution Plan
----------------------------------------------------------
Plan hash value: 3456159766

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INSA |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(SUBSTR("NAME",1,2)='Ba')

name 컬럼이 substr 함수로 가공되어 INSA_NAME_IDX 인덱스가 있음에도 인덱스 스캔 사용 불가

LIKE 연산자 사용하여 해결

HR@ora19c> select * from hr.insa where name like 'Ba%';

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       204 Baer                                10000 07-JUN-02 70
       116 Baida                                2900 24-DEC-05 30
       167 Banda                                6200 21-APR-08 80
       172 Bates                                7300 24-MAR-07 80

Execution Plan
----------------------------------------------------------
Plan hash value: 1159457075

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INSA          |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INSA_NAME_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("NAME" LIKE 'Ba%')
       filter("NAME" LIKE 'Ba%')

연봉이 90000인 사원 조회

HR@ora19c> select * from hr.insa where sal * 12 = 90000;

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       154 Cambrault                            7500 09-DEC-06 80
       160 Doran                                7500 15-DEC-05 80

Execution Plan
----------------------------------------------------------
Plan hash value: 3456159766

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INSA |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("SAL"*12=90000)

sal 컬럼에 곱셈 연산자를 사용하여 가공했기 때문에 INSA_SAL_IDX 인덱스가 있음에도 사용 불가

곱셈을 우항으로 이항하여 해결

HR@ora19c> select * from hr.insa where sal = 90000 / 12;

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       154 Cambrault                            7500 09-DEC-06 80
       160 Doran                                7500 15-DEC-05 80

Execution Plan
----------------------------------------------------------
Plan hash value: 3674231906

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     2 |    50 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INSA         |     2 |    50 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INSA_SAL_IDX |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("SAL"=7500)

입사일이 2007/02/07인 사원 조회

HR@ora19c> select * from hr.insa where to_char(hire,'yyyymmdd') = '20070207';

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       107 Lorentz                              4200 07-FEB-07 60
       187 Cabrio                               3000 07-FEB-07 50

Execution Plan
----------------------------------------------------------
Plan hash value: 3456159766

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INSA |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIRE"),'yyyymmdd')='20070207')

hire 컬럼이 to_char 함수로 가공되어 INSA_HIRE_IDX 인덱스가 있음에도 사용 불가

조건 값을 형 변환하여 해결

HR@ora19c> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT                DD-MON-RR

HR@ora19c> select * from hr.insa where hire = '07-FEB-2007';

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       107 Lorentz                              4200 07-FEB-07 60
       187 Cabrio                               3000 07-FEB-07 50

Execution Plan
----------------------------------------------------------
Plan hash value: 1351908537

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INSA          |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INSA_HIRE_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("HIRE"=TO_DATE(' 2007-02-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

현재 date format이 DD-MON-RR 로 설정되어 있기 때문에 이 포맷으로 작성된 문자형 날짜만 날짜형으로 자동 형 변환이 발생할 수 있음

HR@ora19c> select * from hr.insa
where hire >= to_date('2007-02-07', 'yyyy-mm-dd')
and hire < to_date('2007-02-08', 'yyyy-mm-dd');  2    3

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       107 Lorentz                              4200 07-FEB-07 60
       187 Cabrio                               3000 07-FEB-07 50

Execution Plan
----------------------------------------------------------
Plan hash value: 1351908537

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INSA          |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INSA_HIRE_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("HIRE">=TO_DATE(' 2007-02-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "HIRE"<TO_DATE(' 2007-02-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

명시적으로 형 변환을 해주자

2001년 입사자 조회

HR@ora19c> select * from hr.insa where hire like '%01';

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       102 De Haan                             17000 13-JAN-01 90

Execution Plan
----------------------------------------------------------
Plan hash value: 3456159766

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   125 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INSA |     5 |   125 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(INTERNAL_FUNCTION("HIRE") LIKE '%01')

like는 문자형 연산자이기 때문에 비교되는 컬럼을 문자형으로 자동 형 변환하여 비교함
인덱스 컬럼이 가공되었으므로 인덱스 사용 불가

날짜형 컬럼을 비교하는 조건 값은 날짜형으로 작성

HR@ora19c> select * from hr.insa
where hire between to_date('20010101', 'yyyymmdd')
and to_date('20020101', 'yyyymmdd') - 1/24/60/60;  2    3

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       102 De Haan                             17000 13-JAN-01 90

Execution Plan
----------------------------------------------------------
Plan hash value: 1351908537

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |    15 |   375 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INSA          |    15 |   375 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INSA_HIRE_IDX |    15 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("HIRE">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "HIRE"<=TO_DATE(' 2001-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))

20번 부서 사원 조회

HR@ora19c> select * from hr.insa where dept_id = 20;

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       201 Hartstein                           13000 17-FEB-04 20
       202 Fay                                  6000 17-AUG-05 20

Execution Plan
----------------------------------------------------------
Plan hash value: 3456159766

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   250 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| INSA |    10 |   250 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("DEPT_ID")=20)

dept_id 컬럼은 문자형이고 문자형과 숫자형을 비교하면 문자형을 숫자형으로 자동 형 변환 후 비교함
dept_id 컬럼에 대해 자동 형 변환이 일어났으므로 컬럼이 가공되어 인덱스 사용 불가

문자형 컬럼을 비교하는 조건 값은 문자형으로 작성

HR@ora19c> select * from hr.insa where dept_id = '20';

        ID NAME                                  SAL HIRE      DEPT_ID
---------- ------------------------------ ---------- --------- ----------
       201 Hartstein                           13000 17-FEB-04 20
       202 Fay                                  6000 17-AUG-05 20

Execution Plan
----------------------------------------------------------
Plan hash value: 282223020

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |    10 |   250 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| INSA          |    10 |   250 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INSA_DEPT_IDX |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   2 - access("DEPT_ID"='20')

숫자형 컬럼

employees 테이블에서는 department_id 는 숫자형 컬럼
문자형과 숫자형을 비교할 때 숫자형이 더 강하기 때문에 조건 값으로 문자형이 들어와도 상관 없음

HR@ora19c> select * from hr.employees where department_id = 20;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         17-FEB-04 MK_MAN       13000                       100            20
        202 Pat                  Fay                       PFAY                      603.123.6666         17-AUG-05 MK_REP        6000                       201            20

Execution Plan
----------------------------------------------------------
Plan hash value: 235881476

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     2 |   138 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |   138 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   2 - access("DEPARTMENT_ID"=20)

HR@ora19c> select * from hr.employees where department_id = '20';

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID      SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         17-FEB-04 MK_MAN       13000                       100            20
        202 Pat                  Fay                       PFAY                      603.123.6666         17-AUG-05 MK_REP        6000                       201            20

Execution Plan
----------------------------------------------------------
Plan hash value: 235881476

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     2 |   138 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     2 |   138 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   2 - access("DEPARTMENT_ID"=20)

조건값으로 숫자형이 들어오나 문자형이 들어오나 실행 계획 동일

Explain Plan

optimizer가 SQL문 실행에 사용하는 실행 계획을 생성
plan_table에 저장
실행 SQL문은 실행하지 않음

Optimizer

사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최저 비용의 처리 경로를 선택해주는 엔진

plan_table 생성 스크립트

[oracle@ora19c ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ora19c admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin
[oracle@ora19c admin]$ ls utlxplan.sql
utlxplan.sql

plan_table

10g부터는 데이터베이스 생성 시 기본적으로 sys.plan_table$ 테이블이 만들어짐
plan_table이라는 public synonym 사용

HR@ora19c> select * from all_synonyms where synonym_name = 'PLAN_TABLE';

OWNER                SYNONYM_NAME                   TABLE_OWNER          TABLE_NAME           DB_LINK              ORIGIN_CON_ID
-------------------- ------------------------------ -------------------- -------------------- -------------------- -------------
PUBLIC               PLAN_TABLE                     SYS                  PLAN_TABLE$                                       0

실행 계획 생성

HR@ora19c> explain plan for select * from hr.employees where employee_id = 100;

Explained.

explain plan for <실행 계획을 생성할 SQL문>

dbms_xplan.display 패키지를 이용한 실행 계획 조회

basic

가장 단순한 옵션
Operation과 객체 이름만 출력

HR@ora19c> select * from table(dbms_xplan.display(null, null, 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |
|   2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |
-----------------------------------------------------

9 rows selected.

typical

표준 옵션

HR@ora19c> select * from table(dbms_xplan.display(null, null, 'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=100)

14 rows selected.

Id : 각 Operation 번호, Id * 있는 경우 Predicate Information에 access, filter 에 관한 정보
- access predicate : 실제 블록을 읽기 전에 어떤 방법으로 블록을 읽을 것인가를 결정(찾고자 하는 데이터가 있는 행이 있는 블록의 위치를 알고 있을 때)
- filter predicate : 실제 블록을 읽은 후에 데이터를 걸러내기 위해 사용(찾고자 하는 데이터가 있는 행이 있는 블록의 위치를 모를 때)
Operation : 실행되는 Job
Name : Operation에서 액세스하는 테이블, 인덱스
Rows : 각 Operation이 끝났을 때 return 되는 행 수(예상치)
Bytes : 각 Operation이 수행했을 때 byte 값(예상치)
Cost(%CPU) : 각 Operation의 cost(예상치)
Time : 각 Operation의 수행 시간(예상치)

실행 계획 생성 시 statement id 지정

HR@ora19c> explain plan set statement_id = 'demo1' for select * from hr.employees where employee_id = 100;

Explained.

HR@ora19c> select * from table(dbms_xplan.display('plan_table', 'demo1', 'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=100)

14 rows selected.

all

typical 옵션 + 추가 정보
- Predicate Information
- Query Block Name/Object Alias
- Column Projection Information

HR@ora19c> select * from table(dbms_xplan.display('plan_table', 'demo1', 'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1
   2 - SEL$1 / EMPLOYEES@SEL$1

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

   2 - access("EMPLOYEE_ID"=100)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPLOYEE_ID"[NUMBER,22], "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
       "EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20], "EMPLOYEES"."HIRE_DATE"[DATE,7],
       "EMPLOYEES"."JOB_ID"[VARCHAR2,10], "EMPLOYEES"."SALARY"[NUMBER,22],
       "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22], "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
       "EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
   2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]

31 rows selected.

outline

Outline Data 는 힌트 목록

HR@ora19c> select * from table(dbms_xplan.display('plan_table', 'demo1', 'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("EMPLOYEE_ID"=100)

28 rows selected.

advanced

all 옵션 + 추가 정보
- Outline Data
- Query Block Registry

HR@ora19c> select * from table(dbms_xplan.display('plan_table', 'demo1', 'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1
   2 - SEL$1 / EMPLOYEES@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("EMPLOYEE_ID"=100)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPLOYEE_ID"[NUMBER,22], "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
       "EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20], "EMPLOYEES"."HIRE_DATE"[DATE,7],
       "EMPLOYEES"."JOB_ID"[VARCHAR2,10], "EMPLOYEES"."SALARY"[NUMBER,22],
       "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22], "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
       "EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
   2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMPLOYEES]]></t><s><![CDATA[
        SEL$1]]></s></h></f></q>

52 rows selected.

제외 할 정보 지정

-<제외 할 정보명>
Projection과 Outline 정보 제외

HR@ora19c> select * from table(dbms_xplan.display('plan_table', 'demo1', 'advanced -Projection -Outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1
   2 - SEL$1 / EMPLOYEES@SEL$1

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

   2 - access("EMPLOYEE_ID"=100)

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[EMPLOYEES]]></t><s><![CDATA[
        SEL$1]]></s></h></f></q>

27 rows selected.

자주 쓰는 포맷

HR@ora19c> explain plan for select * from hr.employees where employee_id = 100;

Explained.

HR@ora19c> select * from table(dbms_xplan.display(null, null, 'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMPLOYEES@SEL$1
   2 - SEL$1 / EMPLOYEES@SEL$1

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

   2 - access("EMPLOYEE_ID"=100)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPLOYEE_ID"[NUMBER,22], "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
       "EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20], "EMPLOYEES"."HIRE_DATE"[DATE,7],
       "EMPLOYEES"."JOB_ID"[VARCHAR2,10], "EMPLOYEES"."SALARY"[NUMBER,22],
       "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22], "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
       "EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
   2 - "EMPLOYEES".ROWID[ROWID,10], "EMPLOYEE_ID"[NUMBER,22]

31 rows selected.

HR@ora19c> select * from table(dbms_xplan.display(null, null, 'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("EMPLOYEE_ID"=100)

28 rows selected.

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

[아이티윌 오라클 DBA 과정 91기] 260205 TIL (0)	2026.02.05
[아이티윌 오라클 DBA 과정 91기] 260204 TIL (0)	2026.02.04
[아이티윌 오라클 DBA 과정 91기] 260202 TIL (0)	2026.02.02
[아이티윌 오라클 DBA 과정 91기] 260130 TIL (0)	2026.01.30
[아이티윌 오라클 DBA 과정 91기] 260129 TIL (0)	2026.01.30

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

I/O

Random I/O

Sequential I/O

인덱스 튜닝

조합 인덱스 필요성

last_name = 'King' and first_name = 'Steven'

EMP_LNAME_IDX, EMP_FNAME_IDX 인덱스 생성

last_name = 'King' and first_name = 'Steven'

last_name = 'King'

first_name = 'Steven'

Index Merge Scan(and_eaual 힌트)

조합 인덱스

조합 인덱스 생성

last_name = 'King' and first_name = 'Steven'

조합 인덱스의 선행 컬럼

단일 컬럼 인덱스 삭제(EMP_LNAME_IDX, EMP_FNAME_IDX)

선행 컬럼으로 조회

Index Skip Scan

Index Full Scan

Index Fast Full Scan

index full scan보다 index fast full scan이 더 빠른 이유?

인덱스를 이용한 정렬

Index Full Scan Descending

INDEX FULL SCAN (MIN/MAX)

인덱스 컬럼 가공

insa 테이블 및 인덱스 생성

name이 Ba로 시작하는 사원 조회

LIKE 연산자 사용하여 해결

연봉이 90000인 사원 조회

곱셈을 우항으로 이항하여 해결

입사일이 2007/02/07인 사원 조회

조건 값을 형 변환하여 해결

2001년 입사자 조회

날짜형 컬럼을 비교하는 조건 값은 날짜형으로 작성

20번 부서 사원 조회

문자형 컬럼을 비교하는 조건 값은 문자형으로 작성

숫자형 컬럼

Explain Plan

Optimizer

plan_table 생성 스크립트

plan_table

실행 계획 생성

dbms_xplan.display 패키지를 이용한 실행 계획 조회

basic

typical

실행 계획 생성 시 statement id 지정

all

outline

advanced

제외 할 정보 지정

자주 쓰는 포맷

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바

Index Merge Scan(`and_eaual` 힌트)