1. Optimizer 힌트 예제

• ALL_ROWS: 전체 행을 처리하는 최적화를 적용합니다.

  SELECT /*+ ALL_ROWS */ employee_id, first_name, last_name
  FROM employees
  WHERE department_id = 10;
  

• FIRST_ROWS(10): 첫 10개의 행을 빠르게 가져오는 최적화를 적용합니다.

  SELECT /*+ FIRST_ROWS(10) */ employee_id, first_name, last_name
  FROM employees
  ORDER BY salary DESC;

2. Table Access 힌트 예제

• FULL: 테이블의 전체 스캔을 강제합니다.

  SELECT /*+ FULL(employees) */ employee_id, last_name, salary
  FROM employees
  WHERE department_id = 20;
  

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

  SELECT /*+ INDEX(employees emp_salary_ix) */ employee_id, last_name, salary
  FROM employees
  WHERE department_id = 30;
  

• INDEX_ASC: 인덱스를 오름차순으로 스캔합니다.

  SELECT /*+ INDEX_ASC(employees emp_salary_ix) */ employee_id, last_name, salary
  FROM employees
  WHERE department_id = 40;
  

• NO_INDEX: 특정 인덱스를 사용하지 않도록 강제합니다.

  SELECT /*+ NO_INDEX(employees emp_salary_ix) */ employee_id, last_name, salary
  FROM employees
  WHERE department_id = 50;
  

3. Join 힌트 예제

• USE_NL: Nested Loop 조인을 강제합니다.

  Nested Loop 조인은 두 테이블 간의 조인을 가장 기본적인 반복문 형태로 수행하는 방식입니다. 작은 테이블에서 각 행을 가져와
  큰 테이블과의 매칭을 반복합니다.

  동작 방식:

  • 첫 번째(외부) 테이블에서 행을 하나씩 읽어들이고, 그 행에 대해 두 번째(내부) 테이블에서 매칭되는 행을 찾습니다.
  • 일반적으로 소량의 데이터 조인이나 인덱스를 통해 빠르게 접근 가능한 경우 적합합니다.

  SELECT /*+ USE_NL(e d) */ e.first_name, e.last_name, d.department_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id;
  

  위 쿼리는 employees 테이블을 외부 테이블로, departments 테이블을 내부 테이블로 설정하여 Nested Loop 조인을 수행합니다.

  언제 사용해야 하나?

  • 작은 테이블이나 인덱스를 통해 빠르게 접근할 수 있는 경우.
  • 조인 대상 테이블 중 하나가 매우 작은 경우.

• USE_MERGE: Sort-Merge 조인을 강제합니다.

  Sort-Merge 조인은 두 테이블을 각각 정렬한 다음, 동일한 키 값에 대해 매칭을 수행하는 방식입니다.
  정렬 과정이 필요하기 때문에 기본적으로 대량의 데이터를 처리할 때 유용합니다.

  동작 방식:

  • 먼저 두 테이블을 조인 조건에 따라 정렬합니다.
  • 그 후, 정렬된 결과를 병합하여 매칭되는 행을 찾습니다.

  SELECT /*+ USE_MERGE(e d) */ e.first_name, e.last_name, d.department_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id;
  

  이 쿼리는 employees와 departments 테이블 간의 Sort-Merge 조인을 수행합니다.

  언제 사용해야 하나?

  • 조인 조건이 인덱스로 지원되지 않고, 테이블이 큰 경우.
  • 양쪽 테이블 모두 대량의 데이터가 포함되어 있고, 정렬을 통해 효율적인 조인을 수행할 수 있을 때.

• USE_HASH: 해시 조인을 강제합니다.

  해시 조인은 대규모 데이터셋 간의 조인을 최적화하기 위해 해시 테이블을 생성하는 방식입니다.
  일반적으로 대량의 데이터에서 성능이 뛰어납니다.

  동작 방식:

  • 작은 테이블을 기준으로 해시 테이블을 생성합니다.
  • 큰 테이블의 행을 하나씩 읽어가며 해시 테이블에서 매칭되는 값을 찾습니다.

  사용 예시:

  SELECT /*+ USE_HASH(e d) */ e.first_name, e.last_name, d.department_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id;
  

  이 쿼리는 해시 조인을 강제하여 employees와 departments 간의 조인을 수행합니다.

  언제 사용해야 하나?

  • 대규모 테이블 조인 시 효율적입니다.
  • 인덱스가 없거나 비효율적인 경우에 좋은 성능을 제공합니다.

• LEADING: 특정 테이블을 조인에서 우선 처리합니다.

  LEADING 힌트는 옵티마이저에게 조인 순서를 지정합니다. 이 힌트는 복잡한 조인에서 특정 테이블을 우선적으로 처리하도록 강제할 수 있습니다.

  사용 예시:

  SELECT /*+ LEADING(d) */ e.first_name, e.last_name, d.department_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id;
  

  위 쿼리는 departments 테이블을 조인에서 먼저 처리하도록 합니다.

  언제 사용해야 하나?

  • 옵티마이저가 선택한 기본 조인 순서가 비효율적일 때.
  • 특정 테이블을 먼저 처리하여 성능을 최적화하고자 할 때.

• ORDERED: FROM 절에 나열된 순서대로 조인을 수행합니다.

  ORDERED 힌트는 FROM 절에 나열된 테이블 순서대로 조인을 수행하도록 강제합니다.
  일반적으로 옵티마이저는 가장 효율적인 조인 순서를 선택하지만, ORDERED 힌트를 사용하여 특정 순서를 지정할 수 있습니다.

  사용 예시:

  SELECT /*+ ORDERED */ e.first_name, e.last_name, d.department_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  JOIN locations l ON d.location_id = l.location_id;
  

  위 쿼리는 FROM 절에 나열된 순서대로 조인을 수행합니다.

  언제 사용해야 하나?

  • 수동으로 조인 순서를 최적화하고자 할 때.
  • 옵티마이저의 자동 결정이 비효율적일 때.

• PUSH_JOIN_PRED: 조인 조건을 최대한 조기에 적용하여 불필요한 데이터를 걸러냅니다.

  PUSH_JOIN_PRED 힌트는 조인 조건을 최대한 조기에 적용하여 불필요한 데이터를 걸러냅니다.
  이는 조인 대상이 되는 데이터 양을 줄여 성능을 개선하는 데 사용됩니다.

  사용 예시:

  SELECT /*+ PUSH_JOIN_PRED */ e.first_name, e.last_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  WHERE d.location_id = 1700;
  

  이 힌트는 조인 조건을 조기에 평가하도록 하여 성능을 개선합니다.

  언제 사용해야 하나?

  • 조인 대상 데이터가 클 때, 조기 필터링을 통해 성능을 개선하고자 할 때.

4. Parallel 힌트 예제

• PARALLEL: 테이블에 대한 병렬 처리를 활성화합니다.

  SELECT /*+ PARALLEL(employees, 4) */ employee_id, last_name, salary
  FROM employees
  WHERE department_id = 60;
  

• NOPARALLEL: 병렬 처리를 비활성화합니다.

  SELECT /*+ NOPARALLEL(employees) */ employee_id, last_name, salary
  FROM employees
  WHERE department_id = 70;
  

5. Query Transformation 힌트 예제

• MERGE: 뷰 병합을 강제합니다.

  뷰 병합은 뷰를 사용한 쿼리에서, 옵티마이저가 뷰를 기본 테이블로 병합하여 실행 계획을 최적화하는 과정입니다. MERGE 힌트는 이 병합을 강제합니다.

  예제:

  SELECT /*+ MERGE */ e.department_id, AVG(e.salary)
  FROM (SELECT department_id, salary FROM employees) e
  GROUP BY e.department_id;
  

  이 쿼리에서는 employees 테이블을 서브쿼리로 감쌌습니다. MERGE 힌트는 옵티마이저가 이 서브쿼리를 기본 테이블로 병합하여 직접 접근하도록 강제합니다.
  이를 통해 옵티마이저는 더 효율적인 실행 계획을 생성할 수 있습니다.

• NO_MERGE: 뷰 병합을 방지합니다.

  SELECT /*+ NO_MERGE */ department_id, AVG(salary)
  FROM employees
  GROUP BY department_id;
  

• UNNEST: 서브쿼리 UNNEST를 강제합니다.

  서브쿼리 언네스트는 WHERE 절에 있는 서브쿼리를 메인 쿼리로 병합하여 성능을 개선하는 기법입니다. UNNEST 힌트는 옵티마이저가 이를 강제하도록 합니다.

  예제:

  SELECT /*+ UNNEST */ employee_id, first_name, last_name
  FROM employees e
  WHERE e.department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

• NO_UNNEST: 서브쿼리 UNNEST 방지.

  서브쿼리가 언네스트되지 않도록 방지해야 하는 경우, NO_UNNEST 힌트를 사용합니다.

  예제:

  SELECT /*+ NO_UNNEST */ employee_id, first_name, last_name
  FROM employees e
  WHERE e.department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
  

  이 힌트는 옵티마이저가 서브쿼리를 독립적으로 유지하도록 강제합니다.

• PUSH_SUBQ: 서브쿼리 푸시다운을 강제합니다.

  서브쿼리를 메인 쿼리로 조기에 평가하여 성능을 최적화합니다. 이는 특정 조건을 미리 필터링하여 처리해야 할 데이터 양을 줄일 때 유용합니다.

  SELECT /*+ PUSH_SUBQ */ employee_id, first_name
  FROM employees e
  WHERE department_id = (SELECT department_id FROM departments WHERE location_id = 1700);

• MATERIALIZE: 서브쿼리를 물리적으로 저장하여 재사용 최적화합니다.

  MATERIALIZE 힌트는 서브쿼리 결과를 물리적으로 저장하여 재사용을 최적화합니다. 특히 복잡한 서브쿼리가 여러 번 참조될 때 유용합니다.

  SELECT /*+ MATERIALIZE */ department_id, AVG(salary)
  FROM (SELECT department_id, salary FROM employees) e
  GROUP BY department_id;

6. Aggregation 힌트 예제

• HASH GROUP BY: 해시 기반의 집계 연산을 사용합니다.

  SELECT /*+ HASH GROUP BY */ department_id, COUNT(*)
  FROM employees
  GROUP BY department_id;
  

• SORT GROUP BY: 정렬 기반의 집계 연산을 사용합니다.

  SELECT /*+ SORT GROUP BY */ department_id, COUNT(*)
  FROM employees
  GROUP BY department_id;
  

7. Partitioning 힌트 예제

• PARTITION: 특정 파티션에 접근하도록 강제합니다.

  SELECT /*+ PARTITION(employees p1) */ employee_id, first_name, salary
  FROM employees
  WHERE department_id = 80;
  

8. Miscellaneous 힌트 예제

• APPEND: INSERT 작업 시 데이터를 새롭게 추가합니다.

  INSERT /*+ APPEND */ INTO employees (employee_id, first_name, last_name, salary, department_id)
  VALUES (999, 'John', 'Doe', 6000, 90);
  

• CACHE: 결과를 캐시에 저장합니다.

  SELECT /*+ CACHE */ employee_id, last_name
  FROM employees
  WHERE department_id = 100;
  

• NOCACHE: 결과를 캐시에 저장하지 않도록 합니다.

  SELECT /*+ NOCACHE */ employee_id, last_name
  FROM employees
  WHERE department_id = 110;
  

• CARDINALITY: 예상되는 행 수를 지정합니다.

  SELECT /*+ CARDINALITY(10) */ employee_id, last_name
  FROM employees
  WHERE department_id = 120;