옵티마이저에서 index와 hint의 목적은 무엇이고, 어떤 점이 다르고, 수행 방법 및 순서는?

옵티마이저에서 index와 hint의 목적은 무엇이고, 어떤 점이 다르고, 수행 방법 및 순서는?

 

 

옵티마이저에게
“어떤 인덱스를 써라”는 지시를 내릴 수 있는 방법이 두 가지 있습니다.
하나는 통계를 기반으로 자동으로 고르게 하는 것(INDEX) 이고,
다른 하나는 개발자가 강제로 지정하는 것(HINT) 입니다.
목적·차이·수행(적용) 순서를 정리하면 다음과 같습니다.

---

1. 목적

---

• INDEX(자동 선택)
  – 통계 정보(칼럼 분포, 리프 블록 수, 클러스터링 팩터 …)를 바탕으로
  최소 비용을 내는 실행계획을 옵티마이저가 스스로 찾는 것.
• HINT(힌트)
  – 통계가 부정확하거나 특수한 데이터 패턴 때문에 자동 계획이 나쁠 때
  개발자가 원하는 계획을 강제로 삽입해 주는 것.
  – 힌트는 지시일 뿐, 반드시 따른다는 보장은 없지만(무시될 수 있음)
  대부분의 경우 지정한 대로 계획이 고정됨.

---

1. 차이점

---

항목 INDEX(자동) HINT

---

결정 주체 옵티마이저 개발자/DBA 정보 근거 데이터 딕셔너리 통계 사람의 판단, 경험치 변동성 통계 바뀌면 계획 바뀜 쿼리를 고쳐야 바뀜 유연성 높음(데이터 변화에 자동 반영) 낮음(계획 고정) 위험도 통계 오류 시 나쁜 계획 가능 힌트 잘못 시 극단적으로 나쁜 계획 유지보수 필요 없음 SQL 전체를 재배포해야 할 수도 있음 버전 업그레이드 계획이 자동으로 개선될 수 있음 힌트가 새 버전에서 무시/오류 날 수 있음

---

1. 수행(적용) 방법

---

1. INDEX(자동)
   ① SQL 파싱 → ② 통계 읽기 → ③ 옵티마이저가 가능한 인덱스 후보군 생성
   → ④ 각 후보별 I/O·CPU 비용 계산 → ⑤ 최소 비용 인덱스 선택 → ⑥ 실행계획 고정
2. HINT
   ① SQL 파싱 단계에서 힌트 문법 검사 → ② 힌트가 지정한 엑세스 경로를
   후보군 맨 위로 강제 삽입 → ③ 비용 계산 단계에서 해당 경로만
   “매우 낮은 비용”으로 책정 → ④ 결과적으로 힌트된 인덱스 선택
   (※ 힌트가 물리적으로 불가능하면 무시되고 자동 모드로 돌아감)

---

1. 처리 순서(실행계획 수립 흐름)

---

1. SQL 문 파싱
2. 힌트 존재 여부 확인
   • 힌트 있음 → 지정된 엑세스 경로를 우선 후보로 삽입
   • 힌트 없음 → 모든 가능한 인덱스 후보 수집
3. 통계 정보 로드(테이블, 인덱스, 칼럼 히스토그램)
4. 조인 순서·조인 방식 후보 생성
5. 각 후보별 예상 비용 산정
   • 힌트 경로는 인위적으로 낮은 비용 부여
6. 최소 비용 계획 선택
7. 실행계획 생성 및 캐시(공유 풀)

---

1. 실무 팁

---

• 힌트는 최후의 수단으로 사용하고, 가능하면 통계 갱신이나 인덱스 재설계로 해결
• 힌트 후 반드시 실행계획 확인(EXPLAIN PLAN, DBMS_XPLAN)
• 19c 이상에서는 SQL Plan Management(SPM) 나 어댑티브 플랜으로
  힌트 없이도 계획을 안정시킬 수 있음
• 힌트 문법이 바뀌는 경우(업그레이드) 호환성 파라미터 확인 필수

---

한 줄 요약
INDEX는 “통계로 계산한 최선”, HINT는 “개발자가 지정한 최선”이며,
옵티마이저는 파싱 → 힌트 반영 → 통계 활용 → 비용 계산 → 최종 계획 확정 순으로 두 경로를 구분해 처리합니다.