Oracle Indexes and Performance Optimization

인덱스의 개념과 종류 (Concept and Types of Indexes)

인덱스의 개념 (Concept of Indexes)

인덱스는 데이터베이스 테이블의 열에 대해 검색 속도를 높이기 위해 사용되는 데이터 구조입니다. 책의 목차와 유사하게, 인덱스는 특정 열의 값을 빠르게 찾을 수 있도록 도와줍니다.

인덱스의 종류 (Types of Indexes)

B-Tree 인덱스 (B-Tree Index)

가장 일반적인 인덱스 유형으로, 트리 구조를 사용하여 데이터를 저장하고 검색합니다. 다양한 연산(=, <, >, <=, >=)에 효과적입니다.

CREATE INDEX idx_name 
ON table_name (column_name);

예제:

CREATE INDEX idx_employee_last_name 
ON employees (last_name);

비트맵 인덱스 (Bitmap Index)

비트맵 인덱스는 낮은 카디널리티(고유 값이 적은 열)에 적합합니다. 논리적 비트맵을 사용하여 값을 저장합니다.

CREATE BITMAP INDEX idx_name 
ON table_name (column_name);

예제:

CREATE BITMAP INDEX idx_employee_gender 
ON employees (gender);

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

함수를 적용한 결과에 대해 인덱스를 생성합니다. 특정 조건이나 계산이 자주 사용될 때 유용합니다.

CREATE INDEX idx_name 
ON table_name (function(column_name));

예제:

CREATE INDEX idx_employee_upper_last_name 
ON employees (UPPER(last_name));

역방향 키 인덱스 (Reverse Key Index)

인덱스 키 값을 역순으로 저장하여 일련번호 값의 열에 대한 분포를 고르게 만듭니다.

CREATE INDEX idx_name 
ON table_name (column_name) REVERSE;

예제:

CREATE INDEX idx_employee_id_reverse 
ON employees (employee_id) REVERSE;

인덱스 생성 및 관리 (Creating and Managing Indexes)

인덱스 생성 (Creating Indexes)

인덱스는 CREATE INDEX 문을 사용하여 생성합니다.

CREATE INDEX idx_name 
ON table_name (column_name);

예제:

CREATE INDEX idx_department_name 
ON departments (department_name);

인덱스 제거 (Dropping Indexes)

인덱스를 제거하려면 DROP INDEX 문을 사용합니다.

DROP INDEX idx_name;

예제:

DROP INDEX idx_department_name;

인덱스 모니터링 (Monitoring Indexes)

인덱스 사용률을 모니터링하여 필요 없는 인덱스를 제거하거나 필요한 인덱스를 추가할 수 있습니다.

ALTER INDEX idx_name MONITORING USAGE;

예제:

ALTER INDEX idx_employee_last_name MONITORING USAGE;

쿼리 성능 최적화 기법 (Query Performance Optimization Techniques)

인덱스 활용 (Using Indexes)

인덱스를 사용하여 쿼리 성능을 최적화할 수 있습니다. 인덱스가 존재하는 열에 대해 조건을 걸면 성능이 향상됩니다.

SELECT * 
FROM employees 
WHERE last_name = 'Smith';

이 쿼리는 last_name 열에 인덱스가 있을 경우 빠르게 수행됩니다.

적절한 인덱스 선택 (Choosing Appropriate Indexes)

• 자주 검색되거나 조인되는 열에 인덱스를 생성합니다.
• 카디널리티가 낮은 열(고유 값이 적은 열)에는 비트맵 인덱스를 사용합니다.
• 함수나 표현식이 자주 사용되는 열에는 함수 기반 인덱스를 사용합니다.

쿼리 리팩토링 (Query Refactoring)

쿼리를 재구성하여 성능을 최적화할 수 있습니다. 서브쿼리를 줄이고, 조인 순서를 최적화하며, 불필요한 연산을 제거합니다.

SELECT e.first_name, e.last_name, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id 
WHERE d.department_name = 'Sales';

실행 계획 분석 (Analyzing Execution Plans)

실행 계획을 확인하여 쿼리의 성능 병목 지점을 찾고 최적화합니다.

EXPLAIN PLAN FOR 
SELECT * 
FROM employees 
WHERE last_name = 'Smith';

실행 계획을 확인하려면 SQL Developer 또는 TOAD와 같은 도구를 사용할 수 있습니다.

데이터베이스 힌트 사용 (Using Database Hints)

힌트를 사용하여 옵티마이저에게 특정 실행 계획을 사용하도록 지시할 수 있습니다.

SELECT /*+ INDEX(e idx_employee_last_name) */ e.first_name, e.last_name 
FROM employees e 
WHERE last_name = 'Smith';

Oracle 데이터베이스에서 인덱스와 성능 최적화를 잘 활용하면, 데이터 검색 속도를 크게 향상시킬 수 있습니다. 적절한 인덱스를 선택하고 쿼리를 최적화함으로써 효율적인 데이터베이스 운영을 할 수 있습니다.