튜닝

오라클 튜닝 기초 04강 — 인덱스를 무력화하는 실수들 + 결합 인덱스

🎯 학습 목표

  • 인덱스가 무력화되는 대표 패턴을 식별하고 고친다.
  • 결합 인덱스에서 컬럼 순서가 왜 중요한지 이해한다.
  • SARGable한(인덱스를 탈 수 있는) 조건으로 SQL을 작성한다.

📖 핵심 규칙: “인덱스 컬럼을 가공하면 인덱스를 못 탄다”

인덱스는 컬럼 값 그대로를 정렬해 둡니다. WHERE 절에서 그 컬럼을 함수로 감싸거나 연산하면, 인덱스에 저장된 “원래 값”과 매칭할 수 없어 옵티마이저가 인덱스를 포기하고 전체 스캔으로 빠집니다. 이것이 실무에서 인덱스가 안 타는 1순위 원인입니다.

💻 실수 1) 컬럼을 함수/연산으로 가공

-- ❌ 인덱스(hire_date) 무력화: 컬럼을 함수로 감쌌다
SELECT * FROM emp WHERE TO_CHAR(hire_date,'YYYY') = '2024';

-- ✅ 컬럼은 그대로 두고, 상수 쪽을 가공 (범위 조건으로 변환)
SELECT * FROM emp
 WHERE hire_date >= DATE '2024-01-01'
   AND hire_date <  DATE '2025-01-01';

-- ❌ 컬럼에 연산
SELECT * FROM orders WHERE price * 1.1 > 11000;
-- ✅ 상수 쪽으로 이항
SELECT * FROM orders WHERE price > 11000 / 1.1;

💻 실수 2) 암묵적 형변환(Implicit Conversion)

-- phone 컬럼이 VARCHAR2인데 숫자로 비교하면 오라클이 자동 형변환
-- ❌ 사실상 TO_NUMBER(phone) = ... 가 되어 인덱스 무력화
SELECT * FROM customer WHERE phone = 1055550000;

-- ✅ 타입을 맞춰 문자열로 비교
SELECT * FROM customer WHERE phone = '1055550000';

형변환은 눈에 안 보여 더 위험합니다. 컬럼과 비교 상수의 데이터 타입을 항상 일치시키세요.

💻 실수 3) 선두 와일드카드 LIKE / NULL 비교

-- ❌ 앞이 %면 정렬된 인덱스를 활용 못 함 → 전체 스캔
SELECT * FROM product WHERE name LIKE '%스피커%';
-- ✅ 앞부분이 고정이면 인덱스 범위 검색 가능
SELECT * FROM product WHERE name LIKE '무선%';
-- (앞 % 검색이 꼭 필요하면 Oracle Text 등 별도 기능 고려)

-- ❌ 일반 B-Tree 인덱스는 NULL을 따로 저장하지 않아 IS NULL 검색에 안 쓰임
SELECT * FROM emp WHERE mgr IS NULL;   -- 보통 Full Scan

📖 결합 인덱스(복합 인덱스)와 컬럼 순서

여러 컬럼을 묶은 결합 인덱스는 (A, B)로 만들면 “A로 먼저 정렬, 같은 A 안에서 B로 정렬”됩니다. 마치 전화번호부가 (성, 이름) 순인 것과 같습니다. 그래서:

CREATE INDEX ix ON emp(deptno, ename);   -- (deptno, ename) 순

WHERE deptno = 10 AND ename = 'KIM'  → ✅ 인덱스 최적 활용 (선두 deptno 사용)
WHERE deptno = 10                    → ✅ 선두 컬럼 사용 → 잘 탐
WHERE ename = 'KIM'                  → ❌ 선두(deptno) 조건이 없어 비효율
                                        (성을 모르고 이름만으로 전화번호부 찾기)

규칙: 결합 인덱스는 선두 컬럼이 WHERE 조건에 있어야 제대로 탄다. 그리고 등호(=)로 자주 쓰는 컬럼을 앞에, 범위(>, BETWEEN)로 쓰는 컬럼을 뒤에 두는 것이 일반적으로 유리합니다(범위 조건 뒤의 컬럼은 인덱스 정렬이 흐트러져 활용도가 떨어지기 때문).

⚠️ 주의사항

  • “함수를 꼭 써야 한다”면 함수 기반 인덱스(Function-Based Index)를 만들 수 있습니다: CREATE INDEX ix ON emp(TO_CHAR(hire_date,'YYYY')); — 단, 인덱스가 늘어나는 비용을 감안하세요.
  • OR 조건, 부정 조건(!=, NOT IN)도 인덱스 활용이 어려운 대표 패턴입니다. 가능하면 UNION ALL이나 IN으로 바꿀 수 있는지 검토하세요.

💡 팁

  • 실행계획에서 TABLE ACCESS FULL이 떴다면, 먼저 “WHERE 절에서 인덱스 컬럼을 가공하지 않았나?”부터 점검하세요. 십중팔구 여기서 걸립니다.
  • 결합 인덱스 설계 전에 “이 테이블에서 가장 자주 쓰는 WHERE 조합이 무엇인가”를 먼저 조사하세요. 순서가 곧 성능입니다.