[Oracle] Index 탐색 방식 - Unique,Range,Full,Fast,Skip

Index

인덱스는 테이블에 대한 동작 속도를 높히기 위해 사용되는 오브젝트입니다. 정렬된 상태로 데이터가 저장되어 빠른 속도로 원하는 데이터에 접근할 수 있습니다. 하지만, 인덱스를 사용한다고 해서 무조건 속도가 빠른 것은 아닙니다. 테이블의 전체 데이터를 출력하는 경우에는 인덱스의 탐색보다는 테이블을 전체 탐색하는것이 빠릅니다. 그래서 주로 탐색하는 데이터가 많은데 반환되는 데이터가 적은 경우 인덱스를 사용하게 됩니다. 이런 인덱스를 사용할때 옵티마이저는 가정 최적화된 방법으로 탐색을 합니다. 인덱스에는 아래의 탐색 방식이 있고 하나씩 살펴보도록 하겠습니다.

• Index Unique Scan
• Index Range Scan
• Index Full Scan
• Index Fast Full Scan
• Index Skip Scan

 

실습환경

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 11.2.0.1
SID : orcl
user : jamong

 

테이블 구조

SYS@orcl> create table jamong.departments as select * from hr.departments;
SYS@orcl> create table jamong.employees as select * from hr.employees;
SYS@orcl> alter table jamong.departments add constraint pk_departments primary key(department_id);
SYS@orcl> alter table jamong.employees add constraint pk_employees primary key(employee_id);

JAMONG@orcl> alter session set statistics_level=all;

HR 계정의 기본 테이블의 데이터를  jamong 계정으로 이동하여 사용했고, 커서에 존재하는 실행계획을 수시로 확인하기 위해 statistics_level을 all로 설정했습니다.

 

xplan.sql

set linesize 150
set pagesize 100

select * from table(dbms_xplan.display_cursor(null,null,'allstats last cost'));

실행 계획에 대한 상세 내용은 아래 글에서 확인할 수 있습니다.

[Oracle] 실행계획 확인 방법 XPLAN, AutoTrace, SQL Trace

 

 

1. Index Unique Scan

Index Unique Scan은 결과로 하나의 건이 반환될때 사용되고 칼럼이 유일한 값으로 구성되어야 합니다. 즉 Primary Key에 기본적으로 생성되는 인덱스입니다. 조건 검색이 단일(=)인 경우 사용하게됩니다.

 

예시

JAMONG@orcl> 
select 
    employee_id
    ,first_name
    ,last_name
    ,salary 
from employees 
where employee_id = 100;

JAMONG@orcl> @xplan

INDEX UNIQUE SCAN을 통해 하나의 값만 받아서 사용하기 때문에 인덱스 상에서 하나의 블록을 읽고 테이블에 접근하여 하나의 블록을 읽어 총 2번의 논리적 블록 읽기를 했습니다.

 

특징

• 칼럼의 값이 유일한 Primary Key에 사용된 Index
• 동등 조건 "="
• Single Block

 

2. Index Range Scan

Index Range Scan은 가장 일반적인 Index의 탐색 방식입니다. Index를 수직 탐색 후 필요한 범위까지만 탐색은 하는 방식입니다. 인덱스의 데이터는 정렬된 상태로 저장이 되므로 범위에 대한 탐색이 필요할때 효율적으로 데이터를 탐색할 수 있으며 리프 블록에서 다음 리프블록의 정보를 갖고 있어 다시 브랜치 블록을 읽을 필요없이 다음 데이터를 바로 읽을 수 있습니다. 이런 특징을 잘 활용하면 정렬작업을 생략하거나 최대값, 최솟값을 찾는데 유용하게 활용될 수 있습니다.

 

예시

JAMONG@orcl> create index employees_dept_idx on employees(department_id);
JAMONG@orcl> 
select 
    employee_id
    ,first_name
    ,last_name
    ,department_id
    ,salary 
from employees 
where department_id > 80 
order by department_id;

JAMONG@orcl> @xplan

department_id 칼럼으로 인덱스를 생성하고 Index Range Scan을 사용하는 예시를 확인해봤습니다. 쿼리상에 department_id로 정렬하는 작업을 요청하는데 인덱스자체가 정렬된상태로 저장되어있어 따로 정렬작업을 하지 않는 것을 확인할 수 있습니다.

 

 

JAMONG@orcl> 
select 
    employee_id
    ,first_name
    ,last_name
    ,department_id
    ,salary 
from employees 
where department_id > 80 
order by department_id desc;

JAMONG@orcl> @xplan

똑같은 쿼리를 내림차순으로 정렬했을 때의 실행계획입니다. Index Range Scan을 진행하는데 Descending으로 탐색하는 것을 확인할 수 있습니다. 이는 오름차순으로 정렬되어 있는 인덱스를 앞에서부터가 아닌 뒤에서부터 탐색하여 결과를 반환하는 것입니다.

 

처음부터 인덱스에 데이터를 내림차순으로 저장하는 descending index를 생성하는 방법도 있습니다.

JAMONG@orcl> drop index employees_dept_idx;
JAMONG@orcl> create index employees_dept_idx on employees(department_id desc);
JAMONG@orcl> 
select 
    employee_id
    ,first_name
    ,last_name
    ,department_id
    ,salary 
from employees 
where department_id > 80 
order by department_id desc;

JAMONG@orcl> @xplan

데이터가 내림차순으로 인덱스에 저장되어있어 내림차순 결과를 요청할때 일반 Index Range Scan을 합니다. Descending Index Range Scan은 최대값을 구할때 사용됩니다. max 값을 구할때 인덱스로부터 가장 끝에 있는 행 하나만 읽어오면 되기때문에 전체 데이터를 탐색할 필요가 없습니다. 만약 옵티마이저가 다른 선택을 한다면 힌트를 사용해서 하나의 행만 읽도록 유도할 수 있습니다.

 

특징

• 비교조건(>,<) 사용 시 --> Unique Index
• Non Unique Index 사용시 사용될 수 있음
• 수직 탐색 후 필요한 범위까지만 탐색
• Single Block Read

 

3. Index Full Scan

Index Full Scan은 첫번째 리프블록까지 수직적 탐색 후, 인덱스 전체를 탐색하는 방법입니다. 주로 테이블에서 Table Full Scan의 부담이 크거나 정렬작업을 생략하기 위해 테이블 전체를 탐색하는 것보다 Index를 사용하는 것이 유리합니다. 그런데 이 방법은 생성된 인덱스가 없어 사용된 방법으로 Index Range Scan으로 유도하는 것이 좋습니다.

 

예시

JAMONG@orcl> drop index employees_dept_idx;
JAMONG@orcl> create index employees_dept_idx on employees(department_id);

JAMONG@orcl> 
select 
    * 
from employees 
where department_id is not null
order by department_id;

JAMONG@orcl> @xplan

department_id에 다시 인덱스를 생성했습니다. 우선 조건절에 range scan을 피하기 위해 is not null 조건을 부여했습니다. 그리고 department_id를 기준으로 정렬하는 작업을 했습니다. 이번 예시는 정렬작업을 생략하기 위해 Index Full Scan을 사용한 것 입니다. is not null 조건이 아니더라도 Full Scan을 사용할 순 있습니다. 하지만 이번 예시에서는 단일 칼럼 인덱스를 사용하기 때문에 조건을 부여하면 Range Scan으로 탐색하기 때문에 not null 조건을 사용했습니다. 이런 식으로 정렬작업을 생략하기 위해 많이 사용됩니다.

 

JAMONG@orcl> select count(*) from employees;
JAMONG@orcl> @xplan

count 함수를 사용할때도 Index Full Scan을 사용합니다. 우선 조건절이 없는데도 불고하고 Index를 사용합니다. count 함수는 테이블 액세스를 할 필요없이 인덱스만으로도 Count값을 받아올 수 있습니다. 데이터 전체가 있는 테이블에 비해서 인덱스에 저장되어 있는 데이터는 일부 칼럼입니다. 그래서 대부분 인덱스가 차지하는 데이터의 공간이 테이블보다 훨씬 적기 때문에 테이블 전체보다 인덱스 전체를 탐색하는 비용이 적습니다.

 

JAMONG@orcl> select max(department_id) from employees;
JAMONG@orcl> @xplan

JAMONG@orcl> select min(department_id) from employees;
JAMONG@orcl> @xplan

다음 Index Full Scan을 가장 많이 사용하는 부분이 max와 min함수를 사용할때 입니다. Index Full Scan은 인덱스 전체를 확인하는데 Min Max를 구할때는 인덱스의 시작 혹은 끝 한번 건만 확인하고 탐색은 끝내는 방법으로 1개의 블록을 읽어 결과를 얻을 수 있습니다.

 

특징

• 인덱스의 모든 블록을 읽는다.
• 정렬 작업을 생략하기 위해 사용된다.
• Not Null 조건으로도 인덱스 탐색이 가능하다.
• Single Block Read

Full Table Scan과 Index Full Scan의 차이점?

가장 큰 차이점은  Multi-Block Read가 가능 여부 입니다. Full Table Scan은 Multi-Block Read가 가능하지만 Index Full Scan은 리프블록을 일일히 방문하기 때문에 Single Block Read밖에 할 수 없습니다. 그래서 때로는 Full Table Scan의 성능이 더 좋을 때가 있습니다.

 

 

4. Index Fast Full Scan

Index Fast Full Scan은 말 그대로 Index Full Scan보다 빠르다. 전부를 탐색하면서 더 빠르게 결과를 얻을 수 있는 이유는 Multi-Block Read가 가능하기 때문입니다. 하지만 이로 인해 정렬된 상태로 데이터를 받을 수 없다는 제한이 있습니다. Index Full Scan은 데이터의 논리적인 순서로 결과를 얻지만 Index Fast Full Scan은 데이터의 물리적 저장 구조의 순서로 받아오고 그래서 Multi-Block Read가 가능합니다.

 

예시

JAMONG@orcl> select count(employee_id) from employees;
JAMONG@orcl> @xplan

Employees 테이블에서 count로 전체 결과를 받아왔을때 Index Full Scan을 사용하는 것을 확인할 수 있습니다. 그럼 대용량용 테이블을 하나 생성해서 똑같은 질의를 해보겠습니다.

 

JAMONG@orcl> create table test as select * from employees;

JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;
JAMONG@orcl> insert into test select * from test;

JAMONG@orcl> create index test_emp_id_idx on test(employee_id);

JAMONG@orcl> select count(employee_id) from test;
JAMONG@orcl> @xplan

기존의 107개였던 employees 테이블을 대략 90만건의 데이터를 갖도록 테이블을 생성했습니다. 이후 똑같이 employee_id에 인덱스를 생성해주고 같은 쿼리를 질의 했습는데 Index Fast Full Scan을 사용했습니다. 이는 데이터가 늘어나면서 Index Fast Full Scan이 비용이 더 적게 드는 것으로 옵티마이저가 판단했기 때문입니다. 

 

Index Fast Full Scan은 주로 힌트를 사용해서 Multi Block Read가 가능하도록 쿼리튜닝을 하는 것 같습니다. Index Full Scan을 사용하는데 Disk I/O가 많이 발생할때 사용하면 효과적입니다. Index Fast Full Scan을 사용하면 Disk I/O는 비슷할 수 있어도 한번이 읽어들이는 블록이 db_file_multiblock_read_count 파라미터 만큼 이기 때문에 순차적으로 데이터를 읽어오기 위해 발생했던 대기 이벤트를 줄일 수 있습니다.

 

특징

• Index Full Scan보다 빠름
• 물리적 순서대로 탐색
• Multi Block Read 가능
• 병렬 처리 가능

 

5. Index Skip Scan

Index Skip Scan은 Multi Column Index에서 후행 칼럼을 조건절에 사용할때 활용할 수 있습니다. 성별처럼 선두 칼럼의 고유 값의 개수가 적고 후행 칼럼의 고유 값이 많을 때 효과적입니다. 예를 들어 부서가 10,20,30,40 인 회사가 있는데 직원이 1000명입니다. 각각 500,100,200,200 명의 직원이 있습니다. 우리가 알고 싶은 내용은 급여가 5000 ~ 7000 사이인 직원입니다. 생성된 인덱는 (부서번호,급여)로 결합된 인덱스가 있습니다. 만약 Index Skip Scan을 사용하면 10번 부서에서 5000 ~ 7000인 직원을 찾고 그 이후 10번 부서의 데이터는 확인할 필요가 없기 때문에 20번 부서로 넘어가서 똑같이 반복합니다. 이런식으로 필요없는 부분에 대해서 건너뛴다면 Full Table Scan보다 성능이 좋을 수 있습니다.

 

예시

JAMONG@orcl> create index employees_dept_sal_idx on employees(department_id,salary);
JAMONG@orcl> 
select /*+index_ss(employees employees_dept_sal_idx)*/ * 
from employees 
where salary between 5000 and 7000;

마땅한 예시 쿼리를 찾기 힘들어 힌트를 사용해서 결과를 출력해봤습니다.

 

특징

• 결합 인덱스의 선행 칼럼이 조건식에 없어도 사용 가능
• Single Block Read
• 선두컬럼 Distinct Value 적고 후행 컬럼 Distinct Value 많을 때 효과적