study:oracle:datadb:4week

3.3.8. 기타

기타 힌트들 ^^

APPEND

데이터를 입력하는 INSERT문에서 사용하는 힌트이다.
데이터 입력작업을 'DIRECT-PATH'방식으로 수행되도록 하여 SGA를 거치지 않고 직접 저장공간으로 입력이 되도록 함으로써 매우 빠른 입력처리를 보장한다.
한가지 유의할 점은 반드시 최고 수위점(High water mark)다음 위치에 데이터를 저장한다는 것이다.

예) INSERT /*+ APPEND */ INTO TAB2
    SELECT *
       FROM TAB1
     WHERE COL1 >= '20050101';

NOAPPEND힌트는 INSERT문을 처리할때 'CONVENTIONAL-PATH'방식으로 수행하도록 유도하기 위해 사용한다. 이 방식은 기본적으로 직렬모드(Serial mode)로 수행되고 'DIRECT-PATH'방식은 병렬모드로 수행된다.

CACHE

전체 테이블 스캔방식으로 읽혀진 블록을 데이터베이스의 버퍼캐쉬를 관리하는 LRU 리스트의 최근 사용위치 (Most Recent Used End)에 머물도록 하여 계속해서 메모리 내에 머물 수 있도록 하는 힌트이다
크키가 작은 테이블에 유용하게 사용될수 있다. 이 힌트를 사용하면 옵티마이져는 테이블에 이미 정의되었던 기본 캐쉬(Default Caching)정의를 무효화한다.

예) SELECT /*+ FULL(t) CACHE(t) */ last_name
    FROM tech_spec t;

NOCAHCE

전체테이블 스캔 방식으로 읽혀진 블록을 데이터베이스 버퍼 캐쉬의 LRU리스트의 끝에 위치하도록 유도함으로써 메모리 내에서 우선적으로 제거되도록 하는 힌트이다.
이것은 데이터베이스버퍼 캐쉬에서 옵티마이져가 블록을 관리하는 일반적인 방법이기도 하다.

예) SELECT /*+ FULL(m) NOCACHE(m) */ member_name
      FROM member m;

CARDINALITY

옵티마이져에게 해당 쿼리 전체나 일부 구성에 대한 카디널러티 예상 값을 제시하여 실행계획 수립에 참조하도록 하는 힌트이다.
힌트에 테이블을 지정하지 않으면 이때의 카디널러티는 전체 쿼리를 수행한 결과로 얻어진 총 건수로 간주한다.
실행계획의 공유 비율을 높일수 있음
CURSOR_SHARING 파라미터
1. EXACT : 리터럴 값을 바인드 변수로 변경하지 않고 있는 그대로 파싱
2. FORCE OR SIMILAR : SQL조건에 상주값을 지정해도 옵티마이져는 변수로 인정하여 실행계획 수립

예) SELECT /*+ CARDINALITY( s 9999 ) */ ...
      FROM sales s, prod p
      WHERE s.sales_date >= '20050901'
    AND p.prod_id LIKE 'ABEC%';

CURSOR_SHARING_EXACT

CURSOR_SHARING 초기화 파라메터가 'EXACT'로 지정되어 있다면 리터럴 값을 바인드 변수로 변경하지 않고 있는 그대로 파싱을 한다.
FORCE와 SIMILAR를 지정하면 SQL조건절에 상수값을 지정했다라도 옵티마이져는 변수로 인정하여 실행계획을 수립하므로 실행계획의 공유비율을 높일 수 있다.
또한 경우에 따라서 'ALTER SESSION …'명령을 이용하여 이 파라메터를 조정하기도 한다. 그러나 SQL에서 지정한 상수값에 따라 미묘하게 달라지는 실행계획을 얻을 수 없으므로 이 힌트를 이용하여 CURSOR_SHARING 파라메터를 'EXACT'로 지정한것과 동일한 상태로 만들수 있다.

예) SELECT /*+ CURSOR_SHARING_EXACT */ * 
      FROM EMP
      WHERE ENAME = 'SCOTT';

DRIVING_SITE

원격(Remote)테이블과 조인(분산쿼리)을 할 때 쿼리가 수행될 사이트를 지정하여 분산쿼리를 최적화하는데 적용하는 힌트이다.

예) SELECT /*+ DRIVING_SITE(b) */..........
     FROM cust a, order@ord_svr b
     WHERE a.join_date >= '20050101'
   AND b.order_date >= TO_CHAR(SYSDATE-7,'YYYYMMDD');

DYNAMIC_SAMPLING

우리가 통계정보를 생성해두지 않았더라도 언제나 비용기준(CBO)으로 작동할수 있게 하기위한 동적 표본화(Dynamic sampling)에 관한 힌트이다.
이 기능은 통계정보를 가지고 있지않거나, 에러 등의 문제로 사용할수 없게 되거나, 너무 오래되어 더 이상 신뢰할수 없을 때 적용한다.
샘플링 레벨은 0~10 사이로 줄수 있으며, 값이 클수록 더욱 정확한 통계정보를 수집하게 된다. 이 힌트는 바로 이 기능을 단위 SQL에 적용할수 있기 위해 탄생되었다.

예) SELECT /*+ DYNAMIC_SAMPLING(e 1) */ .....
     FROM EMP
     WHERE ENAME = 'SCOTT';

어떤 테이블에 대한 카디널러티 통계가 있을 때 만약 테이블 하나만 액세스하는 쿼리에 조건절이 없는 상태에서 이 힌트를 사용하면 옵티마이져는 현존하는 통계정보를 선택하고 이 힌트를 무시한다.

예) SELECT /*+ DYNAMIC_SAMPLING(e 1) */ count(*)
     FROM employees e;

반대로, 이 상황에서 조건절이 존재하면 옵티마이져는 현존하는 카디널리티 통계를 이용하여 조건문의 선택도를 추정함으로써 이 힌트를 적용하게 된다.

PUSH_PRED

뷰나 인라인뷰의 외부에 있는 조인 조건을 뷰 쿼리 내로 삽입하도록 하는 힌트이다.
인라인뷰가 수행된 결과와 조인을 하는 것이 아니라 직접 조인을 한것처럼 외부의 조인조건에 의해 조인을 수행한 후 체크 기능이 수행된다.

예) SELECT /* NO_MERGE(v) PUSH_PRED(v) */..........
     FROM member m,
    ( SELECT carrier_name, carrier_duration
       FROM member_carrier
       WHERE carrier_type IN ('1','2','4') ) v
 WHERE m.member_id = v.member_id(+)
      AND m.member_type=  '1001';

NO_PUSH_PRED

뷰나 인라인뷰의 외부에 있는 조인조건을 뷰 쿼리 내로 삽입하지 않도록 하는 힌트이다.
아래 SQL은 인라인뷰가 먼저 수행한 결과와 조인이 수행된다.

예) SELECT /* NO_MERGE(v) NO_PUSH_PRED(v) */..........
   FROM employees e,
    ( SELECT manager_id
       FROM employees) v        
 WHERE e.manager_id = v.manager_id(+)
     AND e.employee_id =  100';

PUSH_SUBQ

이 힌트는 머지되지 않는 서브쿼리를 최대한 먼저 수행할 수 있도록 실행계획을 수립하기를 요구한다.
머지가 된 서브쿼리는 먼저 수행되고 그 결과를 메인쿼리의 처리범위를 줄이는 '제공자'역활을 한다. 그러나 일반적으로 머지가 불가능해지면 '확인자'역활을 해야 하기 때문에 실행계획의 마지막 단계에서 수행된다. 만약 머지를 할수 없는 서브쿼리가 상대적으로 적은 로우를 가지고 있거나 처리범위를 줄여 주는데 중요한 역활을 할 수 있다면 이 힌트를 이용하여 최대한 앞 부분에서 수행되도록 함으로써 수행속도 향상을 얻을 수 있다.
예를 들어 어떤 쿼리가 선택한 결합 인덱스에서 중간에 있는 컬럼에 조건이 부여되지 않았을때 '='조건을 공급하기 위해서 서브쿼리를 추가 했는데 우리의 생각과는 달리 서브쿼리가 확인자의 역활을 하도록 실행계획이 수립된다면 이 힌트를 이용하여 제공자 역활을 하도록 할수 있다.

예) SELECT /*+ PUSH_SUBQ(@qb) */..........                 [INDEX: ITEM+SALTYPE+SALDATE]
     FROM sales
     WHERE item = :b1
    AND saltype IN (SELECT /*+ QB_NAME(qb) */ code_id
                           FROM code_table
		           WHERE code_type = 'SALTYPE' )
          AND  saldate between :b2 and :b3;

그러나 그 서브쿼리가 원격 테이블이거나 Sort Merge조인의 일부로써 수행될 때는 이 힌트는 아무런 역활도 하지 못한다.

NO_PUSH_SUBQ 힌트는 옵티마이져가 머지되지 않는 서브쿼리르 제일 나중에 수행되도록한다. 만약 이러한 서브쿼리가 처리범위를 거의 줄여주지 못하거나 상대적으로 부담이 된다면 가능한 가장 마지막에 처리하는것이 유리한다.
다시 말해서 다른 조건들이 최대한 적용되어 가장 많이 줄여진 다음에 수행되는것이 유리하다.

QB_NAME

쿼리 블록에 이름을 부여하여 해당 쿼리 블록 외부의 다른 힌트에서 지정한 쿼리블록을 참조할 수 있도록 하는 힌트이다.
Query Block에 명시적으로 이름을 부여하는 역할을 한다. 주로 Inline View나 Subquery 등에 이름을 붙여서 실행 계획의 가독성을 높이는 용도로 사용된다.
명명하지 않은 쿼리 블록의 경우 옵티마이져가 자동으로 부여(PLAN_TABLE에서 확인)
참고 : http://ukja.tistory.com/93

예) SELECT /*+ UNNEST(@qb) */........
     FROM emp e
     WHERE e.deptno IN (SELECT /*+ QB_NAME(qb) */ d.deptno
                             FROM dept d
			     WHERE d.loc = 'DALLAS');

쿼리 블록에 대한 명칭은 전체 쿼리 내에 유일하게 부여되어야 하며, 동일한 쿼리블록은 다른 이름으로 여러 힌트에서 반복 사용될 수 없다. 이 힌트를 사용하면서 명명하지 않는 쿼리블록은 옵티마이져가 쿼리 블록명을 자동 생성하며, 사용된 쿼리블록명은 PLAN_TABLE에서 확인할수 있다.

REWRITE_OR_ERROR

적합한 실체뷰가 존재하지 않아서 옵티마이져가 쿼리 재생성을 실행할수 없는 경우 만약 이 힌트가 지정되어 있으면 ORA-30393에러를 유발하여 쿼리 수행을 중단 시키도록 하는 힌트이다.

예) SELECT /*+ REWRITE_ OR_ERROR */ p.prod_subcatgory,
                      SUM(s.amount_sold)
     FROM sales s, products p
     WHERE s.prod_id = p.prod_id
     GROUP BY p.prod_subcatgory;
 
      ORA-30393: a query block in the statement did not rewrite

참조한문서

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