오라클 힌트(Oracle Hint) 정리 – 자주 사용하는 힌트와 최적화 전략

오라클 데이터베이스에서 SQL 실행 계획을 제어하는 방법 중 하나가 바로 힌트(Hint)입니다. 옵티마이저(Optimizer)는 자동으로 최적의 실행 계획을 선택하지만, 때때로 예상과 다른 실행 계획이 선택될 수도 있습니다. 이때, 힌트를 사용하면 개발자가 원하는 방식으로 실행 계획을 유도할 수 있습니다.

이번 포스팅에서는 오라클에서 가장 많이 사용되는 힌트들을 정리하고, 각각의 동작 방식과 활용 예제를 소개하겠습니다.

---

1. 오라클 힌트란?

힌트(Hint)는 SQL 문 내에서 /*+ HINT_NAME */ 형식으로 작성되며, 옵티마이저가 특정 실행 계획을 선택하도록 유도하는 역할을 합니다.

 

힌트 기본 사용법:

SELECT /*+ HINT_NAME */ 컬럼명 FROM 테이블명 WHERE 조건;

• /*+ */ 안에 힌트를 명시하면 오라클이 이를 인식하여 실행 계획을 조정합니다.
• 힌트는 SELECT, INSERT, UPDATE, DELETE 문에 적용할 수 있습니다.

---

2. 가장 많이 사용되는 오라클 힌트 정리

2.1 인덱스 관련 힌트

인덱스 힌트는 옵티마이저가 특정 인덱스를 강제로 사용하도록 유도합니다.

• INDEX : 특정 인덱스를 사용하도록 강제

SELECT /*+ INDEX(emp emp_idx) */ * FROM emp WHERE empno = 1001;

• INDEX_FFS : Fast Full Scan 실행

SELECT /*+ INDEX_FFS(emp emp_idx) */ empno FROM emp;

• INDEX_COMBINE : 비트맵 인덱스 조합 사용

SELECT /*+ INDEX_COMBINE(emp idx1, idx2) */ * FROM emp WHERE deptno = 10 AND job = 'MANAGER';

 

---

 

2.2 실행 계획(쿼리 변환) 제어 힌트

SQL 실행 계획을 직접 조정할 수 있는 힌트입니다.

• NO_MERGE : 뷰 또는 서브쿼리 병합을 방지

SELECT /*+ NO_MERGE(e) */ * FROM (SELECT * FROM emp WHERE deptno = 10) e;

• PUSH_PRED : 서브쿼리의 WHERE 조건을 메인 쿼리로 이동

SELECT /*+ PUSH_PRED */ * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE location = 'NY');

• LEADING : 특정 테이블을 조인의 드라이빙 테이블로 지정

SELECT /*+ LEADING(emp) */ * FROM emp JOIN dept ON emp.deptno = dept.deptno;

 

활용 예제:

• NO_MERGE는 옵티마이저가 서브쿼리를 병합하지 않도록 합니다.
• PUSH_PRED는 서브쿼리의 필터 조건을 조기에 적용하여 성능을 향상시킵니다.
• LEADING을 사용하면 조인 순서를 강제로 지정할 수 있습니다.

---

 

2.3 조인 방식 제어 힌트

조인은 SQL에서 성능 최적화의 핵심 요소 중 하나입니다. 옵티마이저가 기본적으로 가장 효율적인 조인 방식을 선택하지만, 특정 조인 방식을 강제하고 싶다면 아래 힌트를 사용할 수 있습니다.

• USE_NL : Nested Loop Join 사용 강제

SELECT /*+ USE_NL(emp dept) */ * FROM emp JOIN dept ON emp.deptno = dept.deptno;

• USE_HASH : Hash Join 사용 강제

SELECT /*+ USE_HASH(emp dept) */ * FROM emp JOIN dept ON emp.deptno = dept.deptno;

• USE_MERGE : Sort Merge Join 사용 강제

SELECT /*+ USE_MERGE(emp dept) */ * FROM emp JOIN dept ON emp.deptno = dept.deptno;

 

활용 예제:

• USE_NL은 데이터 건수가 적을 때 유리합니다.
• USE_HASH는 데이터가 많은 경우 성능이 더 좋을 수 있습니다.
• USE_MERGE는 정렬된 데이터에서 효율적입니다.

---

 

2.4 병렬 처리 및 리소스 활용 힌트

대량의 데이터를 처리할 때 성능을 향상시키기 위해 병렬 처리 힌트를 사용할 수 있습니다.

• PARALLEL : 병렬 실행을 활성화

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

• NOPARALLEL : 병렬 실행을 비활성화
•  

SELECT /*+ NOPARALLEL(emp) */ * FROM emp;

 

활용 예제:

• PARALLEL(emp, 4)는 emp 테이블을 4개의 프로세스로 병렬 실행합니다.
• NOPARALLEL을 사용하면 특정 쿼리에 병렬 실행을 적용하지 않도록 설정할 수 있습니다.

---

반응형

2.5 최적화 관련 기타 힌트

• ORDERED : FROM 절에 명시된 순서대로 조인 수행

SELECT /*+ ORDERED */ * FROM emp, dept WHERE emp.deptno = dept.deptno;

• CARDINALITY : 특정 테이블의 예상 결과 건수를 지정

SELECT /*+ CARDINALITY(emp, 10000) */ * FROM emp WHERE job = 'MANAGER';

• APPEND : INSERT 작업에서 Direct Path Insert 수행

INSERT /*+ APPEND */ INTO emp SELECT * FROM emp_backup;

 

활용 예제:

• ORDERED를 사용하면 조인 순서를 강제할 수 있습니다.
• CARDINALITY는 옵티마이저가 테이블의 행 수를 잘못 예측할 때 직접 지정해줄 수 있습니다.
• APPEND는 대량의 데이터를 삽입할 때 성능을 높이는 데 유용합니다.

---

Oracle 힌트를 활용하면 실행 계획을 제어하여 쿼리 성능을 최적화할 수 있습니다. 그러나 모든 경우에 힌트를 적용하는 것이 좋은 것은 아니며, 옵티마이저의 기본 실행 계획을 먼저 분석한 후, 필요한 경우에만 힌트를 적용하는 것이 중요합니다.

 

핵심 요약:

1. 인덱스 힌트 → 특정 인덱스를 강제 (INDEX, INDEX_FFS, INDEX_COMBINE)
2. 실행 계획 제어 힌트 → 서브쿼리, 조인 순서 변경 (NO_MERGE, PUSH_PRED, LEADING)
3. 조인 방식 힌트 → 특정 조인 방식을 지정 (USE_NL, USE_HASH, USE_MERGE)
4. 병렬 처리 힌트 → 대량 데이터 최적화 (PARALLEL, NOPARALLEL)
5. 기타 최적화 힌트 → 실행 순서, Direct Path Insert (ORDERED, CARDINALITY, APPEND)