[SQL] 17. index

17. index

이제 ORACLE SQL의 데이터베이스 객체 중 마지막인 index를 알아보겠다.

index의 개념

(1) index는 목차를 나타낸다.
그래서 보통 튜너들이 많이 사용하며, index를 생성한 후 query에서는 order by를 쓰지 않아도 정렬된 결과로 출력된다.
보통 대용량의 데이터에서 query 속도를 빠르게 하기 위해 index를 이용한다고 알고 있다.

반은 맞고, 반은 틀린 이야기이다.

우선 query 속도를 빠르게 하는 것은 맞다.
그러나 대용량의 데이터에 index가 있다고 해서 query 속도가 빨라지는 것은 아니다.
→ 만약 index가 있는 테이블에 DML 작업이 진행되며 모든 index가 갱신되어야 함을 의미한다.

따라서, 아래와 같은 상황에서 index를 생성하여 사용하는 것을 추천한다.

• 데이터가 매우 많아 열(row)에 광범위한 값이 포함된 경우
• 열(row)에 많은 널 값이 포함된 경우
• 하나 이상의 열(row)이 where절이나 join 조건에서 함께 사용되는 경우
• 테이블이 크고 대부분의 query가 2~4% 미만의 행을 검색할 것으로 예상되는 경우

(2) index를 직접 눈으로 확인할 수는 없다.
어떠한 테이블에 있는 index가 무엇인지 확인하는 명령문은 있으나,
실제 이를 query를 통해 확인할 방법은 없다.

query 속도를 확인함으로서 index의 효과를 확인할 수 있다.

index 생성

index는 아래와 같이 생성한다.

create index 인덱스명
on 테이블명(컬럼명)

→ 삭제시에는 drop index 인덱스명을 사용하면 된다.

예제) emp 테이블의 부서번호(deptno)에 대한 인덱스를 생성하시오!
create index emp_deptno_index
on emp(deptno);

실제로 생성되었는지 확인해보자.
select * from user_indexes;

emp 테이블에 있는 index만 조회하려면 where table_name='EMP';를 추가하면된다.
이때, 테이블명은 무조건 대문자로 입력해야한다.

이제 index의 기능을 간단히 확인해보자.
각 테이블에는 고유의 rowid(열 번호)가 있다. 이는 대략
file 번호 + block 번호(테이블 느낌) + row 번호(어느 file안에 어느 block 안에 몇 번째 row다)로 해석된다.

emp테이블에 index를 만들지 않은 컬럼으로 전체 데이터를 불러올 때와,
index를 만든 deptno(부서번호)컬럼으로 전체 데이터를 불러올 때의 차이점을 보자.

index로 전체 데이터를 불러오려면 where절을 사용해야하는데
index가 걸린 컬럼의 데이터 타입에 따라
문자 > ' ' // 숫자 >=0 // 날짜 < to_date('9999/12/31', 'RRRR/MM/DD')의 형식으로 where 문을 채워준다.

▼ 미리 실행한 명령문
set autot on(query의 성능정보 알려주는 명령문)
set timing on(query 시간 알려주는 명령문)

• 매우 작은 데이터의 간단한 query임에도 불구하고, query 수행 시간에서 차이가 있었다.
  이는 데이터의 양이 많아질 수록. 복잡한 query일수록 더욱 강한 힘을 발휘할 것이다.

• 실행계획(Execution Plan)을 보면, index가 없는 컬럼으로 불러올 때에는
  Table Access Full로 전체 테이블을 검색한다.
  그러나 index가 있는 경우에는 index의 Range를 Scan하여 데이터를 검색하는 것을 확인할 수 있다.

• db block gets(메모리에서 읽은 블록의 갯수), consistent gets(메모리에서 읽은 블록의 갯수),
  physical reads(디스크에서 읽은 블록의 갯수) 지표로 query의 효율성을 확인하는데,
  consistent gets의 수가 줄어들며 index를 사용한 query가 높은 효율을 보였다.

쉽게 풀어서 설명하면 emp 테이블에서 월급이 2850인 사원의 이름과 월급만 가져오려고 할 때,
일반적으로 우리가 사용하는 query는
select ename, sal
from emp
where sal=2850;(테이블 전체를 검색하여 월급이 2850인 사람을 찾음) 이지만,

emp 테이블의 월급(sal) 컬럼에 index가 있을 때에는 자동으로
select ename, sal
from emp
where rowid ='AAAVMUAAEAACNSFAAB';(월급이 2850인 사원의 rowid) 를 통해
데이터를 검색하므로 query 시간과 효율성에서 차이가 나는 것이다.

※ 만약 하나의 테이블에서 두 개의 컬럼에 각각 index가 있으며,
두 가지 중 특정 컬럼의 index로 query를 실행하고 싶다면 를 사용한다.

▼ 예시

=================이 글은 Markdown을 활용해 작성하였습니다=================