Oracle Hint

Hint란?

• SQL 튜닝의 핵심 부분으로, 일종의 지시 구문
• 오라클 옵티마이저에게 SQL문 실행을 위한 데이터를 스캐닝하는 경로, 조인 방법 등을 알려주기 위해 SQL 사용자가 SQL 구문에 작성하는 것 (직접 최적 경로 작성)
  => 오라클이 항상 최적의 실행 경로를 만들어내는 것은 불가능하기 때문
• 단, 힌트, 인덱스, 조인의 개념을 정확히 알고 사용하지 않은 무분별한 힌트의 사용은 성능의 저하를 초래하기 때문에 적절하게 사용하여야 한다.
• 만약, 힌트에 오타가 있는 경우 -> 잘못 사용된 힌트는 무시되어 힌트가 없는 것처럼 동작하기 때문에 큰 관계 없음

사용 예시

• 액세스 경로, 조인 순서, 병렬 및 직렬 처리, 옵티마이저의 목표 변경 가능
• 데이터 값을 정렬하는 경우
• 드라이빙 테이블을 원하는 대로 선정하는 경우

Optimizer 접근 방식

1. Rule-Based Optimization(RBO)

• 미리 정해진 규칙에 따라 실행 계획을 수립
  - 연산자 우선 순위
  - 접근 경로의 우선 순위
  - SQL 문장의 syntax 규칙 => 상식에 의거하지만 융통성은 없음
• 데이터에 대한 통계, 실제 SQL을 수행할 경우에 소요될 비용 고려 안함
  - ex) 인덱스 있으면 무조건 사용(테이블 크기, 인덱스 효율 등 무시)
• Oracle 버전 6 이하와의 역 호환성을 위해 지원

2. Cost-Based Optimization(CBO)

• 데이터에 대한 각종 통계 사용
• 실행 비용(Cost)의 예측을 통한 지능적인 실행 계획의 수립
  - I/O 횟수
  - CPU 사용량
  - 메모리 사용량
  - 네트워크 전송량 => 보다 현실적이고 지능적인 판단을 내림
• Oracle 7에 처음 도입
  - 지속적으로 향상
  - 일반적으로 RBO보다 우수

OPTIMIZER_MODE 지정 가능 값

1. /*+ ALL_ROWS */

• 전체 리소스 소비를 최소화시키기 위한 힌트
  Cost-Based 접근방식으로 ALL_ROWS는 Full Table Scan을 선호하며 CBO(Cost Based Optimization)는 디폴트로 ALL_ROWS를 선택한다.

2. /*+ FIRST_ROWS */

• 조건에 맞는 첫 번째 row 리턴, Cost-Based 접근 방식 이용
• Index Scan이 가능하다면 옵티마이저가 full table scan 대신 index scan 선택, Sort-Merge보단 Nested Loop 선택
• Order by절에 의해 Index scan이 가능하면, Sort 과정을 피하기 위해 Index Scan 선택
• Delete/Update Block에서는 무시됨
• 집합연산자, Group By, For UpDate, Group 함수, Distinct를 포함한 Select문에서도 제외
• full table scan보다는 index scan 선호, Interactive Application인 경우 best response time 제공
• sort merge join 보다는 nested loop join 선호

3. /*+ CHOOSE */

• access되는 테이블에 통계치 존재 여부에 따라 선택함
• Optimizer로 하여금 Rule-Based Approach와 Cost-Based Approach를 선택하고, 그렇지 않으면 Rule-Based Approach 선택
• Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지 CBO(Cost-Based Approach)인지 선택
• 만약 주어진 테이블의 통계 정보가 없다면 RBO 사용

4. /*+ RULE */

• Rule Based 접근 방식을 사용하도록 지정한다.

[참고자료](https://devuna.tistory.com/35)
[Optimizer 접근 방식 참고자료](https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jyc8618&logNo=220212548623)