*Oracle기반 설명입니다.

Hint란?

정의 및 간단한 설명

• Hint는 쿼리 옵티마이저(Optimizer)에게 특정 실행 계획을 제안하여, 쿼리 실행 방식을 개발자가 직접 제어할 수 있도록 해주는 기능입니다.
• DBMS는 일반적으로 자체적으로 최적의 실행 계획을 생성하지만, 옵티마이저가 항상 가장 좋은 경로를 선택하는 것은 아닙니다.
• 이러한 경우, 개발자가 쿼리의 성능을 개선하기 위해 명시적으로 힌트를 사용하여 옵티마이저의 선택을 유도할 수 있습니다.
  
  

추가적인 설명

DB hint의 도입배경은 아래와같다

• DB에서 SQL 명령어가 입력되었을떄 DBMS 서버가 자체적으로 쿼리익스플랜을 설정하고 쿼리를 실행하여 클라이언트가 요청한 값을 리턴하는 프로세스에서 옵티마이저는 자체적으로 최적의 쿼리익스플랜을 채택하는 역할을 수행한다.
• 하지만 옵티마이저가 항상 최적의 경로를 설정할 수 는 없다. 그렇기 때문에 개발자가 앞서 공부했듯 쿼리 익스 플랜을 검토하여 최적의 쿼리 실행을 할 수 있도록 수정해야하는 경우도 존재한다.
• 이떄 개발자가 직접 쿼리 익스플랜을 설정할 수 있게 도와주는 도구가 hint 인 것이다. hint를 개발자가 입력하여 최적의 데이터 탐색 쿼리를 수행 명령을 부여하면 쿼리 옵티마이저는 힌트에 따라 주어진 경로탐색(데이터작업쿼리)를 수행한다.
• 쉽게 표현하면, 힌트는 개발자가 데이터베이스 서버 옵티마이저에게 부여하는 명령어이다.
• hint란? 각 DBMS에서 지원하는 기능으로 쿼리 옵티마이저에게 어떤 방식으로 쿼리를 실행할지 제안을 하는 역할을 가진다.

정리하자면

Hint의 필요성

• 엑세스 경로, 조인 순서, 병렬/직렬처리 여부, 옵티마이저의 목표를 변경하기 위해
• 정렬, 병렬/직렬 처리 여부 등을 제어하여 성능 개선 유도
• 드라이빙 테이블, 조인 순서 등 실행계획을 개발자 의도대로 제어하고자 할 때
  • 드라이빙 테이블이란:
    • 조인 연산 시 먼저 접근하여 액세스 경로를 주도하는 테이블로
    • 조인 성능에 큰 영향을 미치므로, 어떤 테이블을 드라이빙 테이블로 선택하느냐에 따라 쿼리 속도가 크게 달라진다.
• 최적화 엔진이 실행계획을 선택하여 성능향상 가능성이 존재. 이때 개발자의 역량이 중요한 요인이 된다.
• 개발자의 의도대로 커스터마이징하여 예측가능한 쿼리익스플랜을 만들 수 있다. 옵티마이저가 항상 최적의 경로를 선택하지 못할 수 있음
• 데이터베이스엔진(서버)가 예상치 못한 것들(즉 개발자가 생각해낸 것들)을 수행하여 특정 상황에 대해 대응할 수 있다.
• 통계 정보가 없거나 비정상적인 경우 RBO 방식 강제 가능

Hint 사용법

• 위치 : SELECT 문 바로 뒤 주석 형태로 삽입
• 문법: /*+ 힌트명 */ 형식 (자바 주석처럼 /* */ 안에 + 사용)
• 예시

SELECT /*+ index_asc(e idx_myemp1_ename) */
       EMPNO, ENAME, SALARY
  FROM MYEMP1 e 
 WHERE ENAME >= 'k';

주요 Hint 종류

1. 옵티마이저 목표 설정

• /*+ ALL_ROWS */

  • full table scan으로 CBO (cost based optimizer)는 default방식이다.
  • 전체 결과 반환을 가장 효율적으로 수행. CBO 기본 방식

• /*+ FIRST_ROWS */

  • 빠르게 첫 번째 결과를 반환. Index Scan, Nested Loop 선호 (인터랙티브 앱에 적합)
  • 주의 : GROUP BY, DISTINCT, FOR UPDATE, 집합 연산 등에서는 무시됨
  • delete, update 블럭에서는 무시됨
  • 다음 select문도 무시됨
    • 집합연산자 UNION, INTERSECT, MINUS, UNION ALL
    • group by
    • for update
    • group함수
    • distint
  • full table scan보다는 index scan을 선호하며 interactive application인 경우 best response time임
  • sort merge join보다는 nested loop join이 선호된다.

2. 옵티마이저 방식 선택

• /*+CHOOSE*/

  • CBO인지, RBO인지 선택하는 명령어
  • 딕셔너리가 해당 테이블의 통계정보를 가지고 있다면 옵티마이저는 cost based approach를 선택하며,
  • 없다면 rule based optimizer를 선택

• /*+RULE*/

  • RBO 방식 강제 적용 (옵티마이저 무시)

3. 엑세스 경로 지정하기

• /*+ FULL(table) */
  • 테이블을 full scan 하길 원할 떄 사용
• /*+INDEX(table index_name)*/
  • 지정된 index를 강제적으로 쓰게끔 지정
• /*+INDEX_ASC(table index_name) */
  • 지정된 index를 오름차순으로 지정
  • 오름차순이 기본값이다.
• /*+ INDEX_DESC(table index_name) */
  • 지정된 index를 내림차순으로
• /*+ INDEX_COMBINE(table) */
  • index가 주어지지않으면, 옵티마이저는 best cost 방식으로 boolean combination index를 사용
  • index가 주어지면, bitmap index의 boolean combination를 사용
• /*+ INDEX_FFS(table) */
  • Fast Full Index Scan (정렬 없이 전체 인덱스 스캔)
• /*+ INDEX_SKIP_SCAN(table index_name) */
  • Prefix가 다른 조건에서도 인덱스 사용 가능 (Index Skip Scan)
    
    
    

4. 조인 방식 지정

조인 방식 강제 설정 힌트도 자주 사용됩니다.

• /*+USE_NL(table) */
  • 테이블 조인시 테이블의 각 행이 inner 테이블을 nested loop형식으로 join한다.
  • 지정된 table이 inner table이 된다.
  • 일반적으로 USE_NL은 ORDERED 힌트와 함께 사용되어 조인 순서를 명확히 지정해야한다.
• /*+USE_MERGE(table) */
  • 지정된 테이블들의 조인이 SORT-MERGE 형식으로 일어나도록 유도한다.
• /*+USE_HASH(table) */
  • 각 테이블 간 HASH JOIN이 일어나도록 유도

주의사항

• 힌트는 성능을 보장하는 도구가 아님. 오히려 잘못 사용 시 성능이 저하될 수 있음
• DBMS 종류 및 버전에 따라 지원하는 힌트가 상이함 (예: Oracle은 힌트 지원이 풍부, MySQL은 힌트 기능이 제한적)
  
• 쿼리 구조가 변경되면 기존 힌트가 비효율적으로 작용할 수 있으므로 정기적인 검토 필요
• 힌트가 무시될 수도 있으므로 항상 쿼리 익스플랜을 확인해야한다.
  
  
  
  

정리

• Hint는 쿼리 옵티마이저의 실행 계획을 개발자가 직접 제어할 수 있도록 제공되는 도구이다.

• 주로 인덱스 사용 강제, 조인 순서 및 방식 지정, 정렬 방식, 실행 전략 등의 미세 조정을 위해 사용된다.

• 올바른 사용은 성능 개선에 큰 도움이 되지만, 잘못 사용할 경우 오히려 성능 저하를 초래할 수 있으므로 신중한 분석과 테스트가 필요하다.