[오라클 DB] SQL의 인덱스 정의문, 인덱스의 장점과 단점

 

SQL의 CREATE TABLE 문에서 PRIMARY KEY절로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 기본 인덱스를 생성한다.

 

UNIQUE로 명시한 애트리뷰트에 대해서는 DBMS가 자동적으로 보조 인덱스를 생성한다. (오버헤드는 증가함)

이는 값이 새로 들어오면, 중복된 값인지 아닌지 빠르게 체크하기 위해서이다.

 

다른 애트리뷰트에 추가로 인덱스를 정의하기 위해서는 DBMS마다 구문이 다소 다른 CREATE INDEX문을 사용해야 한다.

여기서는 오라클 기준으로 설명을 이어나가겠다.

 

---

 

다수의 애트리뷰트를 사용한 인덱스 정의

 

한 릴레이션에 속하는 두 개 이상의 애트리뷰트들의 조합에 대하여 하나의 인덱스를 다음과 같이 정의할 수 있다.

 

CREATE INDEX EmpIndex ON EMPLOYEE (DNO, SALARY);

 

위와 같이 정의하게 되면, DNO를 기준으로 먼저 정렬하고 DNO가 같다면 SALARY를 기준으로 정렬하게 된다.

(우선순위는 먼저 정의하는 왼쪽에서부터 순서대로)

 

이렇게 만들어진 EmpIndex라는 인덱스는 아래의 질의에 활용될 수 있다.

 

SELECT  *
FROM      EMPLOYEE
WHERE   DNO = 3   AND   SALARY = 4000000;

 

조건식 WHERE 절에 DNO와 SALARY같이 인덱스 생성에 정의한 필드들을 같이 비교할 때 쿼리 속도가 상승하는 효과를 얻는다.

 

이는 다양한 질의에 사용할 수 있으나, 특정 경우에는 쿼리 속도가 비효율적이게 된다.

 

 

효율적인 경우

 

Case 1. 정의한 애트리뷰트들을 모두 조건절에 쓰는 경우

 

SELECT  *
FROM      EMPLOYEE
WHERE   DNO >= 2   AND  DNO <= 3  AND  SALARY >= 3000000  AND  SALARY <= 4000000;

 

 

Case 2. 정의한 애트리뷰트들 중 앞에 먼저 정의한 애트리뷰트를 조건절에 쓰는 경우

 

SELECT  *
FROM      EMPLOYEE
WHERE   DNO = 2; (또는 DNO에 대한 범위 질의)

 

 

 

비효율적인 경우

 

정의한 애트리뷰트들 중 뒤에 정의한 애트리뷰트를 조건절에 쓰는 경우

SELECT  *
FROM      EMPLOYEE
WHERE   SALARY >= 3000000  AND  SALARY <= 4000000; (또는 SALARY에 대한 동등 조건)

 

 

---

 

인덱스의 장단점

 

 

장점	단점
검색 속도를 향상시킨다. 소수의 레코드들을 수정하거나 삭제하는 연산의 속도는 향상된다. 릴레이션이 매우 크고 중복이 적으며 질의에서 릴레이션의 튜플들 중에 일부(예, 2% ~ 4%)를 검색하고, WHERE절이 잘 표현되었을 때 특히 성능에 도움이 된다.	인덱스를 저장하기 위한 공간이 추가로 필요하다. 삽입, 삭제, 수정 연산의 속도는 저하시킨다.