3.3.4. 조인 방법 선택용 힌트

• 앞서서 공부한 다양한 조인방식을 힌트(HINT)를 사용해 옵티마이져에게 권고(충고)할 수 있다.
• 조인방법은 다양한 주어진 상황에 영향을 받는다. 다음과 같은 요소를 잘 검토해 HINT를 사용하도록 하자.
  1. 부분범위 처리의 가능여부
  2. 인덱스의 구성상태
  3. 처리범위나 사용빈도
  4. 메모리의 가용상태
  5. 작업영역(hash_area_size, sort_area_size 등)
  6. 쿼리가 수행되는 시간대나 시스템의 유형(OLTP, OLAP등)

• Nested Loops방식을 사용하여 조인을 수행하도록 유도하는 힌트이다
• 이 힌트는 대상 집합 간의 조인 방식을 지칭할 뿐이며, 조인 순서에는 영향을 미치지 않는다.
• 예)
  

  SELECT /*+ USE_NL(a b c) */...
         FROM TAB1 a, TAB2 b, TAB3 c
         WHERE ...

• USE_NL 힌트가 옵티마이져에게 지정한 테이블에 대한 조인 방식을 제시하는 것인데 반해, NO_USE_NL 힌트는 옵티마이져에게 지정한 테이블에 대해 Nested Loop 조인방식을 제외한 다른 방식의 조인-해쉬조인 이나 Sort Merge 조인등 -을 사용할을 제시한다.
• 옵티마이져의 실행계획 수립에 대해 USE_NL이 적극적인 개입이라면 NO_USE_NL은 소극적인 개입이라고 할 수 있다.
• Nested Loops조인이 최적이라 판단되는 상황에서는 옵티마이져가 이 힌트를 무시할수도 있다.

예) SELECT /*+ NO_USE_NL(l h) */.......
     FROM orders h, order_items l
     WHERE l.order_id, = h.order_id
   AND l.order_date >= '20050101';

• Nested Loops조인에서 선행처리되는 외측루프의 처리주관 인덱스를 지정할 때 사용하는 힌트이다.
• 과거에는 USE_NL 과 INDEX 힌트를 같이 사용하였지만 이 힌트는 이들을 하나로 통합한 것이다.

예) SELECT /*+ USE_NL_WITH_INDEX(l item_prod_ix) */....
    FROM orders hm order_items l
    WHERE l.order_id = h.order_id
   AND l.order_item like 'ADEN%'
   AND h.order_dt between '20051001' and '20051015';

• 만약 이 힌트에서 인덱스를 지정하지 않았다면, 내측 루프의 연결고리 인덱스가 정상이어야 외측루프(선행 테이블)의 어떤 인덱스가 처리주관 인덱스로 사용될 수 있다. 만약 특정 인덱스를 지정하였다면, 내측 루프의 연결고리가 정상일 때 지정한 인덱스가 적용될 수 있다.
• 참고: Nested Loops 조인에서 '연결고리 정상'에 대한 상세한 개념 [ 2.2 연결고리 상태가 조인에 미치는 영향(page 517~523)편]

• 해쉬조인 방식으로 조인이 수행되도록 우도하는 힌트이다.
• 해쉬조인은 어느 한쪽 테이블이작아서 인-메모리 해쉬조인으로 수행될 수 있다면 매우 양호한 속도를 갖는다.
  • 대부분 경우는 옵티마이져가 통계정보를 토대로 빌드 입력과 검색입력을 결정하므로 일부러 'ordered'힌트를 주어 함부로 순서를 결정하는 것은 바람직하지 못하다. 그러나 옵티마이져의 판단에 문제가 있을 때나 인라인뷰에서 가공한 결과 집합처럼 적절한 통계정보를 가질 수 없는 경우에는 활용가치가 있다.

예) SELECT /*+ USE_HASH (a b) */...
     FROM sale a, pre_order b
     WHERE a.order_id = b.order_id
   AND a.sale_dt LIKE '2005%';

• 이와 반대로 NO_USE_HASH 힌트는 옵티아미져가 지정하 테이블들을 조인하는 데 있어서 해쉬 조인을 제외한 다른 방식의 조인을 고려하도록 유도한다.

예) SELECT /*+ NO_USE_HASH(m d) */....
    FROM member m, dept d
    WHERE m.deptid = d.deptid;

• Sort Merge방식으로 조인을 수행하도록 유도하느 힌트이다.
• 필요하다면 'ordered'힌트와 같이 사용할 것을 추천한다.

예) SELECT /*+ USE_MERGE(a b) */.......
     FROM sale a, pre_order b
     WHERE a.order_id = b.order_id
         AND a.sale_dt BETWEEN '20050701' AND '20050930'
         AND b.cust_no = '101';

• 이와는 반대로 NO_USE_MERGE 힌트는 옵티마이져가 지정한 테이블을 조인하는데 있어서 Sort Merge조인을 제외한 다른 방식의 조인을 고려하도록 유도한다.

“오라클클럽” http://wiki.oracleclub.com/pages/viewpage.action?pageId=1966259&