[DB/Oracle] 오라클 SQL 힌트

SQL Hint

1. 힌트 작성 규칙

• 위치: SELECT, UPDATE, DELETE 키워드 바로 뒤
• Query Block 당 하나의 주석에 묶어야 함
• 별칭/원명 일관성: 테이블에 별칭을 썼으면 힌트도 별칭으로
• Compound Query(UNION, UNION ALL): 각 SELECT 블록마다 지정
• INSERT 전용 힌트: APPEND
• 원칙: 가급적 힌트 대신 SQL/통계/인덱스로 해결
• 부모/서브쿼리 위치 예시

-- 부모 블록에 힌트
SELECT /*+ ORDERED USE_NL(e d) */
       e.ename, d.dname
FROM   ( SELECT * FROM emp ) e
JOIN   dept d ON d.deptno = e.deptno;

-- 서브쿼리 블록에 힌트
SELECT e.ename
FROM   ( SELECT /*+ INDEX(emp emp_idx_deptno) */
                ename, deptno
         FROM   emp
         WHERE  deptno = :p_deptno
       ) e
JOIN   dept d ON d.deptno = e.deptno;

 

2. 최적화 방향 힌트

RULE

• 목적: Rule-Based Optimizer(RBO) 사용
• 상황: 11g 이전 버전 호환
• 효과: 통계 무시, 규칙 기반 실행계획

SELECT /*+ RULE */ ename FROM emp;

 

CHOOSE

• 목적: 통계 존재 여부에 따라 RBO/CBO 자동 선택
• 효과: 사실상 의미 없음 (현행 DB는 CBO만 사용)

SELECT /*+ CHOOSE */ ename FROM emp;

 

FIRST_ROWS / FIRST_ROWS(n)

• 목적: 첫 행 또는 n행까지 빠른 응답 속도
• 상황: OLTP 화면, 페이징 UI
• 효과: 옵티마이저가 NL Join/인덱스 선호
• 주의: 집계, GROUP BY, DISTINCT 등에서는 무시됨

SELECT /*+ FIRST_ROWS */ * FROM emp WHERE deptno = 10;
SELECT /*+ FIRST_ROWS(10) */ * FROM emp ORDER BY hiredate;

 

ALL_ROWS

• 목적: 전체 처리량 최소화 (CBO 기본)
• 상황: 배치, 리포트
• 효과: Hash Join, 병렬 처리 등 선택

SELECT /*+ ALL_ROWS */ deptno, COUNT(*) FROM emp GROUP BY deptno;

 

3. 조인 힌트

ORDERED

• 목적: FROM 절 순서대로 조인

SELECT /*+ ORDERED */ e.ename, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno;

LEADING

• 목적: 드라이빙 테이블 지정

SELECT /*+ LEADING(e) */ e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

USE_NL / USE_MERGE / USE_HASH

• 목적: 조인 방식을 강제
• 효과:
  • USE_NL: 소량 데이터 + 인덱스 → 응답 빠름
  • USE_MERGE: 대량 + 정렬 → 정렬 병합 조인
  • USE_HASH: 대량 + 비정렬 → 해시 조인

SELECT /*+ ORDERED USE_NL(e d) */ ...
SELECT /*+ ORDERED USE_MERGE(e d) */ ...
SELECT /*+ ORDERED USE_HASH(e d) */ ...

 

실행계획 비교 예시

SELECT /*+ USE_NL(e d) */ ...
SELECT /*+ USE_HASH(e d) */ ...

 

USE_NL (샘플)

| Id  | Operation             | Name | A-Rows | Buffers |
|  1  | NESTED LOOPS          |      |      1 |       5 |
|  2  |  INDEX RANGE SCAN     | EMPNO|      1 |       2 |
|  3  |  TABLE ACCESS FULL    | DEPT |      1 |       2 |

 

USE_HASH (샘플)

| Id  | Operation           | Name | A-Rows | Buffers |
|  1  | HASH JOIN           |      |      1 |       6 |
|  2  |  TABLE ACCESS FULL  | EMP  |      1 |       3 |
|  3  |  TABLE ACCESS FULL  | DEPT |      1 |       3 |

 

👉 소량일 때는 NL, 대량일 때는 HASH가 유리.

USE_NL_WITH_INDEX

• 목적: NL Join 시 인덱스 강제

SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ ...

 

STAR / STAR_TRANSFORMATION

• 목적: Star Schema 최적화
• 효과: Dimension 필터를 Fact 조인에 반영 → 성능 개선

SELECT /*+ STAR */ ...
SELECT /*+ STAR_TRANSFORMATION */ ...

4. 접근 방법 힌트

• FULL: Full Table Scan 강제
• ROWID: Rowid 접근
• CLUSTER/HASH: 클러스터/해시 클러스터 스캔

SELECT /*+ FULL(emp) */ * FROM emp;
SELECT /*+ ROWID(emp) */ * FROM emp WHERE rowid = :rid;
SELECT /*+ CLUSTER(emp) */ * FROM emp;
SELECT /*+ HASH(emp) */ * FROM emp WHERE deptno = :p_deptno;

5. 인덱스 힌트

• INDEX/INDEX_ASC/INDEX_DESC: 인덱스 강제, 정렬 방향 지정
• NO_INDEX: 특정 인덱스 배제
• INDEX_COMBINE: 다중 Bitmap 인덱스 결합
• INDEX_FFS: Index Fast Full Scan 강제
• INDEX_JOIN: 여러 인덱스 조합
• INDEX_SS: Skip Scan 강제

SELECT /*+ INDEX(emp emp_idx_deptno) */ * FROM emp WHERE deptno = 10;
SELECT /*+ NO_INDEX(emp emp_idx_salary) */ * FROM emp WHERE sal > 3000;
SELECT /*+ INDEX_FFS(emp emp_idx) */ COUNT(*) FROM emp;
SELECT /*+ INDEX_SS(emp emp_idx) */ * FROM emp WHERE job='CLERK';

👉 인덱스 여러 개 지정 → 비용 가장 낮은 것 선택
👉 인덱스 생략 → 사용 가능한 것 중 최소 비용 선택

---

6. 서브쿼리 힌트

• HASH_AJ/MERGE_AJ: NOT IN → Anti-Join 변환
• HASH_SJ/MERGE_SJ: EXISTS → Semi-Join 변환
• PUSH_SUBQ: 서브쿼리 조기 실행

SELECT e.* FROM emp e
WHERE e.deptno NOT IN (
  SELECT /*+ HASH_AJ */ d.deptno FROM dept d WHERE d.loc = 'DALLAS');

SELECT d.* FROM dept d
WHERE EXISTS (
  SELECT /*+ HASH_SJ */ 1 FROM emp e WHERE e.deptno = d.deptno);

 

7. 병렬 / DML / 모니터링 힌트

• PARALLEL: 병렬 실행
• APPEND: Direct-Path Insert
• MONITOR: SQL Monitoring 강제

SELECT /*+ PARALLEL(emp 4) FULL(emp) */ * FROM emp;

INSERT /*+ APPEND */ INTO sales_archive
SELECT * FROM sales WHERE txn_dt < DATE '2025-01-01';

SELECT /*+ MONITOR gather_plan_statistics */ *
FROM scott.dept d, scott.emp e
WHERE d.deptno = e.deptno AND d.deptno = 10;

8. 글로벌 vs 로컬 힌트

• 로컬: 현재 블록만 적용
• 글로벌: View 내부까지 적용 (뷰별칭.내부별칭)

CREATE VIEW instructors AS
SELECT * FROM employees e WHERE job LIKE '%INSTRUCTOR%';

SELECT /*+ INDEX(i.e emp_sal_idx) */ ...
FROM instructors i WHERE i.salary > 5000;

 

9. 힌트 무시 조건

• 키워드 뒤가 아닐 때
• 문법 오류
• 충돌 (RULE vs CBO, INDEX_FFS 조건 불일치 등)
• 조건 불충족 (USE_HASH인데 키 없음, PARALLEL 불가 상황)
• 무시 시 → 단순 주석 처리

---

10. 검증 방법

• DBMS_XPLAN.DISPLAY_CURSOR('ALLSTATS LAST'): A-Rows vs E-Rows 확인
• SQL Monitoring (MONITOR 힌트): 실시간 실행 단계 추적
• SQL Trace + tkprof: Parse/Exec/Fetch 시간 확인
• Plan Hash Value: 동일 계획 여부 확인

---

🚨 힌트 사용 시 주의사항

1. 통계 변경 시 무력화: 통계 재수집 후 오히려 성능 저하
2. 데이터 분포 변화 취약: NL 유리 → 대량 증가 시 Hash가 더 나을 수 있음
3. 이식성/유지보수성 저하: DB 버전 업 시 성능 악화 위험
4. 절대적 강제 아님: 조건 불일치 시 무시됨

---

결론

• 힌트는 옵티마이저 보정 도구
• 목적: 비효율적인 실행계획 보정
• 효과: 응답속도 개선, 리소스 절감, 대량 처리 안정화
• 원칙: SQL 리팩토링/통계/인덱스로 먼저 → 힌트는 최후의 수단
• 검증 필수: DBMS_XPLAN, SQL Monitoring, tkprof