[SQLD-SQL 최적화 기본원리]복합인덱스 사용 예시

Oracle 데이터베이스에서 복합 인덱스(Composite Index)는 여러 컬럼을 조합하여 생성된 인덱스입니다.

이를 활용하면 특정 쿼리의 성능을 더욱 최적화할 수 있습니다.

 

실무에서 주로 복합인덱스를 사용하며 시험에도 복합인덱스가 출제됩니다. 

---

1. 테이블 생성

다음은 직원 정보를 저장하는 테이블의 예입니다.

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,    -- 기본적으로 PRIMARY KEY에 대해 인덱스 생성
    emp_name VARCHAR2(100),
    department_id NUMBER,
    hire_date DATE,
    salary NUMBER
);

---

2. 복합 인덱스 생성

department_id와 hire_date 컬럼을 기준으로 데이터를 자주 검색한다고 가정합니다.

-- 복합 인덱스 생성
CREATE INDEX idx_department_hiredate ON employees(department_id, hire_date);

---

3. 데이터 삽입

테이블에 샘플 데이터를 삽입합니다.

INSERT INTO employees VALUES (1, 'Alice', 10, TO_DATE('2023-01-01', 'YYYY-MM-DD'), 5000);
INSERT INTO employees VALUES (2, 'Bob', 20, TO_DATE('2023-02-15', 'YYYY-MM-DD'), 6000);
INSERT INTO employees VALUES (3, 'Charlie', 10, TO_DATE('2023-01-10', 'YYYY-MM-DD'), 7000);
INSERT INTO employees VALUES (4, 'David', 30, TO_DATE('2023-03-01', 'YYYY-MM-DD'), 8000);

---

4. 복합 인덱스 활용 쿼리

(1) 두 컬럼 모두 사용하는 경우

-- 복합 인덱스를 완전히 활용
SELECT * 
FROM employees 
WHERE department_id = 10 AND hire_date = TO_DATE('2023-01-01', 'YYYY-MM-DD');

(2) 첫 번째 컬럼만 사용하는 경우

-- 복합 인덱스의 첫 번째 컬럼(department_id)만 활용
SELECT * 
FROM employees 
WHERE department_id = 10;

(3) 두 번째 컬럼만 사용하는 경우

-- 복합 인덱스는 두 번째 컬럼(hire_date)만으로는 사용할 수 없음
SELECT * 
FROM employees 
WHERE hire_date = TO_DATE('2023-01-01', 'YYYY-MM-DD');

 

Note: 복합 인덱스는 첫 번째 컬럼을 반드시 조건에 포함해야 효과적으로 사용됩니다.★★★★★ 중요!!!

 

---

5. EXPLAIN PLAN으로 확인

복합 인덱스 사용 여부를 확인합니다.

EXPLAIN PLAN FOR
SELECT * 
FROM employees 
WHERE department_id = 10 AND hire_date = TO_DATE('2023-01-01', 'YYYY-MM-DD');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

결과 예시:

-----------------------------------------------------------------------------------
| Id  | Operation        | Name                     | Rows  | Bytes | Cost (%CPU)| Time|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                          |     1 |    52 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    52 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| IDX_DEPARTMENT_HIREDATE |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

• INDEX RANGE SCAN이 idx_department_hiredate를 사용했음을 보여줍니다.

---

6. 복합 인덱스의 주의사항

• 컬럼 순서: 복합 인덱스는 첫 번째 컬럼의 조건이 필수적입니다.
• 선택도: 조건에 자주 사용되는 컬럼을 앞쪽에 배치하는 것이 유리합니다.
• 인덱스 크기: 너무 많은 컬럼을 포함하면 인덱스 크기가 커지고 유지보수 비용이 증가할 수 있습니다.

---