[Oracle]실행계획 해석_인덱스 관련

서론

 

SQLP 시험을 준비하면서 실행계획 해석에 대해 깊은 이해가 필요하다고 생각하여 정리한 내용을 포스팅을 했습니다. 이번 글에서는 인덱스 관련 실행계획 해석을 중점으로 작성했습니다. 아래 실습은 scott 계정의 예제 데이터를 통해 진행했습니다.

 

---

 

본론

 

[INDEX UNIQUE SCAN]

 

인덱스에 존재하는 PK(기본키) 또는 Unique Index처럼 유일한 값을 스캔할 때 발생합니다.

 

select empno from emp where empno = 7369;--PK 조회

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     4 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

 

 

[TABLE ACCESS (BY INDEX ROWID)]

 

인덱스에 존재하지 않는 값을 인덱스를 경유하여 조회할 때 발생합니다.(테이블 랜덤 액세스)

 

select empno, ename --인덱스에 존재하지 않는 ename 조회
from emp 
where empno = 7369;

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

 

 

[INDEX RANGE SCAN]

 

인덱스를 비교 연산자(>, >=, <, <=, like, between 등)를 사용하여 범위 탐색하거나 Unique Index가 아닌 인덱스를 탐색할 경우 발생합니다.

 

비교 연산자로 스캔

 

select empno
from emp
where empno >= 7500;--비교연산자 조회

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    10 |    40 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_EMP |    10 |    40 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

Unique Index가 아닌 인덱스로 스캔

 

create index emp_idx1 on emp(deptno, empno);--Unique Index가 아닌 인덱스

select deptno, empno from emp where deptno = 10 and empno = 7521;

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     7 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_IDX1 |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

 

 

[INDEX SKIP SCAN]

 

복합 인덱스의 선행 컬럼에 대한 조건이 없고 후행 컬럼만 있을 경우 발생합니다. 인덱스 선두 컬럼의 Distinct Value 개수가 적고 (중복이 많음) 후행 컬럼의 Distinct Value 개수가 많을 때 (중복이 적음) 유용합니다. (9i 버전부터 사용 가능)

 

create index emp_idx2 on emp(deptno, ename);

select deptno, ename from emp where ename like 'J'||'%';--후행 컬럼으로 조회

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     9 |     1   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | EMP_IDX2 |     1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

 

 

[INDEX FULL SCAN]

 

조건절에서 인덱스 칼럼을 하나 이상 사용하는 경우 또는 조회 칼럼들이 하나의 인덱스에 모두 존재할 경우 발생합니다. 인덱스 구성 칼럼은 최소 한 개의 컬럼은 NOT NULL 제약조건을 충족해야 합니다. TABLE FUll SCAN 보다 I/O 비용을 줄일 수 있거나 정렬 결과를 쉽게 얻을 수 있을 경우 INDEX FULL SCAN을 선택합니다.

 

select empno, deptno from emp;

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    12 |    84 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_IDX1 |    12 |    84 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

 

 

[INDEX FAST FULL SCAN]

 

SELECT절과 조건절에 사용한 모든 칼럼이 인덱스 칼럼으로 구성되어 테이블을 검색하지 않고 인덱스만 검색하여 원하는 데이터를 얻을 수 있는 경우 사용하는 방식입니다. 인덱스 트리 구조를 무시하고 인덱스 세그먼트 자체를 Multiblock I/O 방식으로 스캔하므로 Singleblock I/O 방식인 INDEX FULL SCAN보다 빠릅니다. 병렬 처리가 가능하나 순서는 보장하지 않습니다.

 

select /*+ index_ffs(emp emp_idx1) */ deptno, empno from emp;

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    12 |    84 |     2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| EMP_IDX1 |    12 |    84 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

 

 

[TABLE ACCESS FULL]

 

테이블을 인덱스를 경유하지 않고 스캔할 때 발생합니다. 인덱스 손익 분기점을 넘을 때 인덱스 스캔보다 FULL 스캔이 유리합니다. (대용량 데이터 조회와 같이 인덱스로 스캔하여 테이블 랜덤 액세스가 많이 발생할 경우 FULL 스캔이 더 유리할 수 있습니다. )

 

select /*+ full(emp) */ * from emp;

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

 

---

 

결론

 

Oracle 인덱스 관련 실행계획 해석을 좀 더 자세히 알아보게 된 좋은 계기였습니다. SQL 튜닝에 있어서 실행계획 해석은 필수적이라 생각하므로 시간이 될 때 다른 실행계획에 대해서도 해석하는 글을 작성하여 실행계획에 대한 이해도를 더욱 높여야겠습니다.

 

Reference

 

[SQL) 001. SQL 인덱스(Index)의 종류와 각 Index Scan 방식의 특징]

https://baseofmint.tistory.com/5

SQL) 001. SQL 인덱스(Index)의 종류와 각 Index Scan 방식의 특징

[다양한 인덱스 스캔 방식,.]

http://wiki.gurubee.net/pages/viewpage.action?pageId=28116440

다양한 인덱스 스캔 방식,. - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식

[NDV(Number of Distinct Value)]

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jevida&logNo=140165206784

NDV(Number of Distinct Value)

[[oracle hint]ACCESS경로변경힌트(INDEX_FFS)]

http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=97

[oracle hint]ACCESS경로변경힌트(INDEX_FFS)