[ SQL ] Hint에 대하여 알아보자 (feat.Oracle)

안녕하세요.

 

오늘은 Oracle에서 SQL 작성시 Hint에 대하여 알아보겠습니다.

 

힌트란?

Oracle Hint는 데이터베이스에서 SQL 쿼리의 실행 계획을 제어하거나 최적화하기 위해 사용하는 특별한 주석입니다.

SQL 문에 힌트를 추가하면 Oracle Optimizer에 특정한 실행 방식을 지시할 수 있습니다.

 

 

힌트의 특징

 

      1. 힌트는 SQL 주석처럼 /*+ hint_name */ 형태로 작성됩니다.

      2. 옵티마이저가 기본적으로 선택하는 실행 계획 대신 사용자가 지정한 실행 계획을 따르도록 강제합니다.

      3. 힌트를 사용하여 성능 문제를 해결하거나 쿼리 실행 속도를 개선할 수 있습니다.

 

힌트 작성시 주의사항

 

      1. 힌트를 잘못 사용하면 성능이 저하될 수 있으므로, 정확한 분석과 테스트가 필요합니다. 

             - 힌트를 잘못 사용할 경우 옵티마이저가 기본적으로 선택하는 실행계획이 더 우수한 성능을 낼 수 있습니다.

      2. 힌트를 사용할 때는 시스템의 통계 정보와 테이블 구조를 충분히 이해하고 작성해야 합니다.

      3. Oracle 버전에 따라 지원하는 힌트가 다를 수 있습니다.

 

 

 

 

Oracle Hint의 종류 및 작성방법

1. 접근 경로 힌트

SELECT /*+ INDEX(emp emp_idx) */ * 
FROM emp 
WHERE dept_id = 10;

설명:

• INDEX(emp emp_idx):
  이 힌트는 옵티마이저에게 테이블 emp에 대해 emp_idx라는 인덱스를 사용하라고 지시합니다. ( emp_idx 인덱스는 미리 생성하여야합니다.)
• 인덱스를 명시적으로 사용함으로써 풀 테이블 스캔 대신 인덱스 스캔이 이루어지도록 강제합니다.
• 사용 상황: 테이블에 데이터가 많고, 조건절(예: WHERE dept_id = 10)로 인해 일부 데이터만 조회해야 할 때 유용합니다.

 

2. 조인 방법 힌트

SELECT /*+ USE_NL(emp dept) */ emp.name, dept.name
FROM emp, dept
WHERE emp.dept_id = dept.dept_id;

설명:

• USE_NL(emp dept):
  이 힌트는 옵티마이저에게 emp와 dept 테이블 간의 조인을 Nested Loop 방식으로 수행하라고 지시합니다.
• Nested Loop Join은 작은 데이터셋이나 선행 테이블에서 조건을 만족하는 데이터만 후행 테이블에서 검색할 때 효율적입니다.
• 사용 상황:
  • 선행 테이블(emp)의 결과가 적고, 후행 테이블(dept)에 적절한 인덱스가 있는 경우.
  • 쿼리에서 빠른 응답 시간이 필요한 경우.

 

3. 병렬 처리 힌트

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

설명:

• PARALLEL(emp, 4):
  이 힌트는 emp 테이블의 작업을 병렬로 4개 프로세스에서 처리하도록 지시합니다.
• 병렬 처리를 통해 테이블이 크거나 데이터 검색 작업이 많을 때 실행 속도를 크게 개선할 수 있습니다.
• 사용 상황:
  • 테이블 스캔이나 대량 데이터 처리가 필요한 쿼리.
  • 쿼리가 실행될 데이터베이스 서버에 충분한 리소스(CPU, 메모리)가 있는 경우.

4. 쿼리 변환 힌트

SELECT /*+ NO_MERGE(subq) */ * 
FROM (SELECT * FROM emp WHERE dept_id = 10) subq;

설명:

• NO_MERGE(subq):
  이 힌트는 옵티마이저에게 서브쿼리 subq를 메인 쿼리와 병합하지 말고 별도로 실행하라고 지시합니다.
• 일반적으로 옵티마이저는 서브쿼리를 메인 쿼리로 병합(merge)하여 실행 성능을 최적화하려 하지만, 특정 상황에서는 병합하지 않는 것이 유리할 수 있습니다.
• 사용 상황:
  • 서브쿼리를 독립적으로 실행하여 중간 결과를 유지해야 할 때.
  • 서브쿼리 결과를 반복적으로 사용할 경우.

5. 코스트 기반 옵티마이저 힌트

SELECT /*+ ALL_ROWS */ * 
FROM emp;

설명:

• ALL_ROWS:
  이 힌트는 코스트 기반 옵티마이저(CBO)에게 전체 쿼리 실행 비용을 최소화하도록 지시합니다.
• **ALL_ROWS**는 배치 처리와 같이 전체 결과를 가져오는 작업에 적합하며, 전체 실행 시간이 짧아지도록 최적화합니다.
• 사용 상황:
  • 대규모 데이터 조회 작업에서 성능 최적화가 필요할 때.
  • 빠른 응답 시간보다는 전체 쿼리 처리 비용을 줄이는 것이 중요한 경우.

 

정리 및 요약

힌트 유형	목적	사례
INDEX	특정 인덱스 사용 강제	조건 검색이 자주 발생할 때
USE_NL	Nested Loop 조인 강제	데이터량이 적고 인덱스가 있는 경우
PARALLEL	병렬 처리 사용	대규모 테이블 작업 시
NO_MERGE	서브쿼리 병합 방지	중간 결과가 중요한 경우
ALL_ROWS	전체 처리 비용 최적화	배치 처리 및 대량 데이터 조회

 

 

여기까지 힌트에 대하여 알아보았습니다.

 

힌트란 index와 같이 쿼리의 성능 향상 시키기 위하여 사용되지만,

옵티마이저가 자동으로 선택한 경우가 더욱 성능이 좋을수도있으니, 

구조 및 상황에따라 충분히 파악 후에 적절한 상황에 적절한 힌트를 사용할 수 있도록 해야합니다.

 

감사합니다.