since 2019.

2 minute read

Updated:

인덱스 스캔 방법 7가지

  인덱스 엑세스 방법 관련 힌트
1 index range scan index
2 index unique scan index
3 index skip scan index_ss
4 index full scan index_fs
5 index fast full scan index_ffs
6 index merge scan and_equal
7 index bitmap merge scan index_combine

index range scan

  • range scan은 데이터가 한 건만 있다고 하더라도 값을 한번 더 읽는 것이다. 컬럼의 데이터가 unique 제약이 걸려있지 않으면 즉, 중복이 없다는 제한이 설정돼있지 않으면 range scan을 하게 된다.
--index range scan으로 조회
select /*+ index(emp emp_sal) */ --index가 있음에도 full tabel scan할 경우 힌트를 써준다. 
ename, sal 
from emp 
where sal = 1600;

--실행 계획 조회
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | EMP_SAL |      1 |      1 |      1 |00:00:00.01 |       1 |

인덱스 컬럼이 가공된 SQL 튜닝하기

  • where절의 인덱스 컬럼을 가공할 경우, 인덱스를 엑세스하지 못하고 full table scan을 하게 된다.

1. 숫자형 컬럼에 인덱스 생성하기

문제) 연봉(sal * 12)이 36000인 사원들의 이름과 연봉을 출력하세요.

튜닝전

select /*+ gather_plan_statistics index(emp emp_sal)*/ ename, sal*12
from emp
where sal*12 = 36000;

--실행계획 조회
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      2 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      2 |      2 |00:00:00.01 |       7 |

튜닝후

select /*+ gather_plan_statistics */ ename, sal*12
from emp
where sal = 36000/12;

--실행계획 조회
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |      2 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |      1 |      2 |      2 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN                  | EMP_SAL |      1 |      2 |      2 |00:00:00.01 |       1 |

2. 문자형 컬럼에 인덱스 생성하기

문제) 직업의 첫번째 부터 5번째 자리가 SALES인 사원들의 이름과 직업을 출력하는 아래의 SQL을 튜닝하세요.

튜닝전

select /*+ gather_plan_statistics */ ename, job
from emp
where substr(job,1,5) = 'SALES';

튜닝후

select /*+ gather_plan_statistics */ ename, job
from emp
where job like 'SALES%';

3. 날짜형 컬럼에 인덱스 생성하기

문제) 1981년도에 입사한 사원들의 이름과 입사일을 출력하세요.

튜닝전

select /*+ gather_plan_statistics */ ename, hiredate
from emp
where to_char(hiredate, 'RRRR') = '1981';

튜닝후

select /*+ gather_plan_statistics */ ename, hiredate
from emp
where hiredate between to_date('1981/01/01', 'RRRR/MM/DD')
                and to_date('1981/12/31', 'RRRR/MM/DD') + 1;
-- +1을 쓰지 않으면 1981/12/31 00시까지이므로, 12/31을 포함하려면 하루를 더한 +1을 해주어야 한다.

You may also enjoy