[Oracle] 오라클 인덱스(Index) 사용방법

 

✔️ 인덱스(Index)란?

• 인덱스는 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블에 저장된 데이터의 검색 속도를 향상시키기 위한 자료 구조이다.
• 데이터베이스에서 검색 성능을 높여주는 방법으로, 관계형 데이터베이스에서 B+Tree 구조로 된 인덱스를 사용하여 검색 속도를 향상시킨다.
• 특히 select ~ where 등의 쿼리와 같이 특정 데이터를 찾을 때 빠른 속도로 검색할 수 있도록 해준다.

 

✔️ 인덱스를 사용하는 이유

• 테이블의 데이터는 순서와 상관없이 쌓이게 되므로 특정 조건의 데이터를 찾기 위해서는 테이블의 모든 데이터에 접근하여 비교하며 찾는다.
• 그러나 인덱스를 사용하면 search-key가 정렬되어 있기 때문에 조건 검색 시 속도가 빠르다.
• where절에서 자주 조회하고, 수정 빈도가 낮고 데이터 중복이 적은 컬럼에 사용하는 것이 좋다.

 

✔️ 설계 방법

• 조회시 자주 사용하는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋음
  (한 컬럼이 가지고 있는 중복도가 낮을수록)
• INDEX키의 크기는 되도록 작게 설계
• PK, JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

 

✔️ 인덱스 문법

인덱스 생성

-- 단일 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1)

-- 다중 컬럼 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1, 필드이름2, ...)

 

인덱스 조회

SHOW INDEX FROM 테이블이름

 

UNIQUE 인덱스 생성(중복 값을 허용하지 않는 인덱스)

-- 단일 인덱스
CREATE UNIQUE INDEX 인덱스이름 ON 테이블이름(필드이름1)

-- 다중 컬럼 인덱스
CREATE UNIQUE INDEX 인덱스이름 ON 테이블이름(필드이름1, 필드이름2, ...)

 

인덱스 정렬(인덱스 생성 시점에 필드의 정렬방식 설정)

CREATE INDEX 인덱스이름 ON 테이블이름(필드이름 DESC)
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름 ASC)

 

인덱스 삭제

ALTER TABLE 테이블이름 DROP INDEX 인덱스이름;

 

인덱스 추가

ALTER TABLE 테이블이름 ADD (UNIQUE) INDEX 인덱스이름(컬럼명1, 컬럼명2, ...)

 

✔️ 인덱스 힌트 사용방법

실무에서 쿼리문을 작성하다보면 "인덱스가 안탄다.", "인덱스를 태워야 한다"와 같은 이야기를 자주 한다. 아래는 어떻게 인덱스를 태워야 하는지 정리한 내용이다.

오라클 인덱스 힌트

 

• select절 첫 줄에 힌트 주석(*/+ */)을 작성하여 적절한 인덱스를 부여하면 된다. 주석에 꼭 "+"를 붙여야 힌트절이 실행되며 "+"가 없으면 일반 주석으로 간주하고 아무런 이벤트가 없다.

SELECT /*+ INDEX(a EMP_IDX02) */
	  a.empno
	, a.ename
	, a.hiredate
FROM emp a

 

 

🔑 인덱스 힌트 정리

• 적절한 인덱스 힌트를 사용하면 쿼리의 수행 속도를 향상시킨다.
• order by를 사용하지 않아도 인덱스의 컬럼 순서로 정렬되어 조회된다.
• INDEX(INDEX_ASC): 오름차순 정렬, INDEX_DESC: 내림차순 정렬
• 멀티라인 주석(/*+ */)과 싱글라인 주석(--+) 모두 인덱스 힌트를 사용할 수 있다.
• 여러 개의 복합 인덱스 힌트를 사용할 수 있다.
  
  • /*+ INDEX(....) INDEX(....) */

 

일반적인 인덱스 힌트 사용 방법

SELECT /*+ INDEX(a EMP_IDX02) */
	  a.empno
	, a.ename
	, a.hiredate
from emp a
where hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
	AND TO_DATE('1981-12-31', 'YYYY-MM-DD');

 

인덱스 컬럼의 역순으로 데이터를 조회하는 방법
- order by를 사용하지 않고 인덱스 힌트를 사용하여 내림차순으로 정렬할 수 있다.

SELECT /*+ INDEX_DESC(a EMP_IDX02) */
	  a.empno
	, a.ename
	, a.hiredate
from emp a
where hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
	AND TO_DATE('1981-12-31', 'YYYY-MM-DD');

 

복합 인덱스 힌트를 부여하는 방법
여러 개의 테이블을 조인하여 쿼리를 작성할 경우 인덱스 힌트를 여러 개 부여할 수 있다.

SELECT /*+ INDEX(a EMP_PK) INDEX_DESC(b DEPT_PK) */
	, a.empno
	, a.ename,
	, a.hiredate
	, b.deptno
	, b.dname
FROM emp a, dept b
WHERE a.empno = '7989'
AND a.deptno = b.deptno;

 

인덱스 힌트 사용 시 테이블 명을 사용하는 방법
- 테이블 별칭이 없을 경우 테이블 명을 사용할 수 있으나 되도록 테이블 별칭 사용을 권장한다.

SELECT /*+ INDEX(emp EMP_IDX02) */
	  empno
	, ename
	, hiredate
FROM emp
WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
		AND TO_DATE('1981-12-31', 'YYYY-MM-DD')

 

싱글라인 주석 힌트 절 사용 방법

SELECT --+ INDEX(a EMP_IDX02)
	  a.empno
	, a.ename
	, a.hiredate
FROM emp a
WHERE hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD')
		AND TO_DATE('1981-12-31', 'YYYY-MM-DD')

 

 

 

 

 

 

 

 

📌 참고자료

데이터베이스에서 인덱스는 왜 사용하는 걸까?

 

[Oracle] 오라클 인덱스(Index) 힌트 사용방법 (주석, 튜닝)

 

[Postgresql] 인덱스(INDEX) 개념 및 효과적인 인덱스 설계 방법 정리