인덱스

인덱스(Index)란?

책에 비유하면?
- 책에서 특정 단어를 찾을 때 “찾아보기(색인)” 를 이용하죠?
- 오라클의 인덱스도 같은 역할을 해요.
- 데이터를 빠르게 찾기 위해 정리된 목록(색인표) 을 만들어 놓는 거예요.

⸻

왜 인덱스가 필요할까?

상황	인덱스 없음	인덱스 있음
`SELECT * FROM employees WHERE name = 'Kim';`	전체 테이블을 처음부터 끝까지 검사함 ▸ Full Table Scan 발생	인덱스를 따라 바로 'Kim'의 위치로 감 ▸ Index Range Scan 동작

결과: 인덱스가 있으면 훨씬 빠릅니다!

⸻

인덱스의 기본 종류

인덱스 종류	설명	특징
B-tree 인덱스	가장 일반적인 인덱스	빠르고 대부분의 검색에 적합
Unique 인덱스	중복을 허용하지 않는 인덱스 (PK 같은 경우)	중복 데이터 방지
Composite 인덱스	여러 컬럼을 묶어서 만든 인덱스	WHERE 절에 여러 컬럼이 있을 때 효과적
Bitmap 인덱스	값의 종류가 적은 컬럼에 적합 (예: 성별)	주로 DW(데이터 웨어하우스)에서 사용
Function-Based 인덱스	계산 결과에 대해 인덱스를 만듦 (예: `UPPER(name)`)	함수가 들어간 WHERE 절에도 인덱스 사용 가능

⸻

인덱스는 자동으로 생길까?

Primary Key 또는 Unique 제약 조건을 만들면 Oracle이 자동으로 인덱스를 생성해줘요.
그 외에는 사용자가 직접 CREATE INDEX 문으로 만들어야 해요.

⸻

인덱스 만들기 예시

CREATE INDEX idx_emp_name ON employees(name);

⸻

인덱스가 항상 좋은 건 아닐까?

읽기 성능은 좋아지지만,데이터를 INSERT, UPDATE, DELETE 할 때는 인덱스도 같이 수정돼야 하므로 부하가 생깁니다.
그래서 필요한 컬럼에만 신중하게 인덱스를 만들어야 해요.

⸻

인덱스 고급 기능

1. Function-Based Index (함수 기반 인덱스)

설명

SQL에서 WHERE UPPER(name) = 'KIM' 같이 함수가 쓰이면 일반 인덱스는 사용 못 해요.
이럴 때, 함수의 결과에 인덱스를 미리 만들어두면 성능 향상 가능!

예시

CREATE INDEX idx_upper_name ON employees(UPPER(name));

⸻

2. Invisible Index (보이지 않는 인덱스)

설명

인덱스를 일시적으로 비활성화해서, 쿼리가 인덱스를 쓰지 않도록 설정.
성능 테스트할 때 아주 유용해요.

예시

ALTER INDEX idx_emp_name INVISIBLE;   -- 인덱스 감추기
ALTER INDEX idx_emp_name VISIBLE;     -- 다시 보이게 하기

⸻

3. Partial Index (부분 인덱스) - Oracle 12c부터는 Zone Maps로 유사 기능

설명

조건에 따라 일부 행에만 인덱스 생성 (일반적으로는 파티션 인덱스로 구현)
예: 2020년 이후 데이터만 인덱스 적용

⸻

4. Descending Index (내림차순 인덱스)

설명

정렬이 많은 컬럼에 대해 ORDER BY column DESC 쿼리 성능 향상

예시

CREATE INDEX idx_emp_salary_desc ON employees(salary DESC);

⸻

5. Compressed Index (압축 인덱스)

설명

중복되는 인덱스 키가 많은 경우, 공간 절약과 읽기 성능 향상 효과
특히 Composite Index(다중 컬럼 인덱스)에 유리

예시

CREATE INDEX idx_comp ON emp(dept_id, job_id) COMPRESS;

⸻

6. Index on Virtual Columns (가상 컬럼 인덱스)

설명

테이블에는 없지만 계산 결과로 만들어지는 가상 컬럼에도 인덱스를 생성할 수 있어요.

예시

ALTER TABLE emp ADD (full_name AS (first_name || ' ' || last_name));
CREATE INDEX idx_fullname ON emp(full_name);
<source lang=sql>



⸻

7. Global vs Local Partitioned Index

설명
* 파티션 테이블에 인덱스를 만들 때,
** Global Index: 전체 테이블 기준 하나의 인덱스
** Local Index: 각 파티션마다 따로 인덱스 생성 (DML 변경 시 유지가 쉬움)

⸻

8. Online Index Operations

설명
* Oracle 19c에서는 DML 중에도 인덱스를 생성하거나 재구성 가능
* 서비스 중단 없이 작업 가능 (Enterprise Edition 기준)

예시
<source lang=sql>
CREATE INDEX idx_emp_job ON employees(job_id) ONLINE;

⸻

요점 정리

고급 기능	설명	예시/비고
Function-Based Index	함수가 포함된 WHERE 절에서도 인덱스를 사용 가능	CREATE INDEX idx_upper_name ON emp(UPPER(name));
Invisible Index	인덱스를 일시적으로 숨겨 쿼리 테스트	ALTER INDEX idx_name INVISIBLE;
Descending Index	내림차순 정렬에 맞는 인덱스	CREATE INDEX idx_desc ON emp(salary DESC);
Compressed Index	중복 키를 압축하여 공간 절약 및 성능 향상	CREATE INDEX idx_comp ON emp(dept_id, job_id) COMPRESS;
Virtual Column Index	계산된 가상 컬럼에도 인덱스 가능	CREATE INDEX idx_virtual ON emp(full_name);
Global / Local Partitioned Index	파티션 테이블 인덱스 최적화 방식	Local은 유지관리 쉬움
Online Index Create/Rebuild	서비스 중단 없이 인덱스 작업 가능	CREATE INDEX ... ONLINE

⸻