3.3. 실행계획의 제어

3.3.4. 조인 방법 선택용 힌트

조인 방식들에 대한 확실한 이해가 바탕이 되어야 함

USE_NL ; Nested Loops 방식을 사용하여 조인을 수행

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

NO_USE_NL ; Nested Loops 방식을 제외한 다른 방식의 조인을 사용하도록 제시

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

USE_NL_WITH_INDEX ; USE_NL과 INDEX 힌트를 하나로 통합한것 (NL 조인에서 외측루프의 주관 인덱스를 지정)
    
ex) SELECT /*+ USE_NL_WITH_INDEX(l item_prod_ix) */ ...
    FROM orders h, order_items l
    WHERE l.order_id = h.order_id
        AND l.order_item like 'ADEN%'
        AND h.order_dt between '20051001' and '20051015';

USE_HASH ; 해쉬 조인 방식으로 조인이 수행되도록 유도

ex) 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 ; 해쉬 조인을 제외한 다른 방식의 조인을 고려토록 유도

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

USE_MERGE ; Sort Merge 방식으로 조인을 수행하도록 유도 , 필요시 'ordered' 힌트와 같이 사용

ex) 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 방식을 제외한 다른 방식의 조이 고려토록 유도

3.3.5. 병렬처리 관련 힌트

시스템 자원을 최대한 사용하여 결과를 얻는 절대 시간을 줄이겠다는 목적

PARALLEL ; 대량의 DATA에 대한 TABLE을 액세스 할때와 DML을 처리할때 SQL의 병렬처리를 지시하는 힌트
                  PARALLEL THREADS를 나타내는 숫자(PARALLEL DEGREE)와 함께사용, Parallel Degree를 지정하지 않으면

                  PARALLEL_THREADS_PER_CPU 파라메터에 정의된 값을 이용
                 TABLE 정의시 PARALLEL 을 지정하였다면 힌트없이 적용
                 DML 문에 적용시는 ALTER SESSION ENABLE PARALLEL DML' 을 지정해야함

ex) SELECT /*+ FULL (sales) PARALLEL (sales) */
       SUBSTR(sale_dt,1,6),SUM(qty)
    FROM sales
    WHERE sale_dt between '20030101' AND '20051231'
    GROUP BY SUBSTR(sale_dt,1,6);

    SELECT /*+ FULL (sales) PARALLEL (sales, 8 ) */
        SUBSTR(sale_dt,1,6), SUM(qty)
    FROM sales
    WHERE sale_dt    BETWEEN '20030101' AND '20051231'
    GROUP BY SUBSTR(sale_dt,1,6);

NOPARALLEL ; 해당 테이블의 PARALLEL  파라메터를 무시하고 병렬처리를 하지 않는 실행계획을 수립
                       (버전에 따라 NO_PARALLEL)
ex) SELECT /*+ NOPARALLEL (m) */ member_name
    FROM members m;

PQ_DISTRIBUTE ; 슬레이브 프로세스 ( 생산자(Producer)와 소비자(Consumer)프로세스) 사이에서 조인할 테이블 로우를 할당작업(Distribution)을 하는 방법을 정의하는 힌트

expression) /*+PQ_DISTRIBUTE (table, outer_distribution,inner_distribution) */
                - outer_distribution ; 외측 테이블에 대한 할당 방법을 기술
                - inner_distribution ; 내측 테이블에 대한 할당 방법을 기술
                - 할당 방법 :
                     - HASH: 해시 함수를 수행한 결과값을 이용하여 소비자 프로세스의 로우들을 할당
                     - BROADCAST : 외측 테이블 전체 로우를 모든 CONSUMER 프로세스로 보냄
                     - PARTITION : 조인 대상 테이블이 조인 키 칼럼으로 파티션되어있을 경우 파티션 키 값을 이용하여 로우들을

                                           소비자 프로세스에 할당
                     - NONE ; 조인 대상 로우들을 랜덤하게 할당

ex)     SELECT /*+ ORDERED PQ_DISTRIBUTE (b HASH, HASH) USE_HASH(b) */ ...
          FROM TAB1 a, TAB2 b
          WHERE a.col1 = b.col2;

        SELECT /*+ PQ_DISTRIBUTE ( b BROADCAST, NONE) USE_HASH(b) */ ...
        FROM TAB1 a, TAB2 b
        WHERE a.col1 = b.col2;
*** ????


PARALLEL_INDEX ; 파티션 인덱스에 대한 인덱스 범위 스캔을 병렬로 수행하기 위한 병렬도를 지정

ex) SELECT /*+ PARALLEL_INDEX(table1,index1, 3) */ ...

NOPARALLEL_INDEX ; parallel 파라메터를 무시하여 병렬 인덱스 범위 스캔을 하지 않음
                                버젼에 따라 NO_PARALLEL_INDEX

ex) SELECT /*+NOPARALLEL_INDEX (m mem_join_idx) */ ...
    FROM members m
    WHERE join_date BETWEEN '20040101' AND '20051231';

3.3.6. 액세스 수단 선택을 위한 힌트

FULL ; 전체 테이블 스캔 방식으로 유도
ex) SELECT /*+ FULL (t) */ ...
    FROM BIG_TAB T
    WHERE cre-date >='20050101';

HASH ;  해쉬 스캔 방식으로 액세스 하도록 유도하는 힌트
ex) /*+ HASH(TABLE_NAME) */

CLUSTER ; 클러스터 인덱스를 통해 스캔하도록 유도
ex) /*+ CLUSTER(table_name) */

INDEX ; 인덱스 범위 스캔을 유도 (뷰의 경우 뷰쿼리 내부의 테이블 인덱스 지정가능)
ex) SELECT /*+ INDEX(s sales_pk) */ ...
    FROM sales s
    WHERE sale_date='20051015';

    SELECT /*+ INDEX(VIEW_cust cust cname_idx_ */ ...
    FROM VIEW_cust
    WHERE cust_name = 'TOMCAT';

NO_INDEX ; 지정한 인덱스를 제외하고, 다른 액세스 방법을 고려하도록 유도
                   INDEX,INDEX_ASC,INDEX_DESC,INDEX_COMBINE,INDEX_FFS등을 함께사용하면 두 힌트 모두 무시

ex) SELECT /*+ NO_INDEX(m member_pk) */ ...
    FROM members m
    WHERE member_id > 10200;

INDEX_ASC ; 지정한 인덱스를 인덱스 컬럼 값의 오름차순으로 범위 스캔하도록 유도

ex) SELECT /*+ INDEX_ASC(a idx01) */ ...
    FROM tab1 a
    WHERE col1 LIKE 'ABC%';

INDEX_DESC ; 지정한 이덱스를 인덱스 컬럼값의 내림차순으로 범위 스캔하도록 유도

ex) SELECT /*+ INDEX_DESC(a idx01) */ ...
    FROM tab1 a
    WHERE col1 LIKE 'ABC%';

INDEX_COMBINE ; 2개 이상의 인덱스를  비트맵 인덱스로 변경/결합하여 테이블을 액세스 하는 방식으로 유도하는 힌트

ex) SELECT /*+ INDEX_COMBINE (e sal_bix hiredate_bix) */ ...
    FROM emp e
    WHERE sal>3000 AND hiredate < '20050715';

INDEX_FFS ; 인덱스 전체 범위를 스캔하는 방식으로 유도하는 힌트, 다중 블록을 스캔

ex) SELECT /*+ INDEX_FFS (table_name index_name) */ ...

NO_INDEX_FFS ; 고속 전체 인덱스 스캔 방식을 제외한 다른 액세스 방법을 사용하도록 유도

INDEX_JOIN ; 두개 이상의 인덱스들만으로 조인을 수행하도록 유도 ( 인덱스가 필요로 하는 모든 컬럼의 값을 가져야함)

ex) SELECT /*+ INDEX_JOIN(e SAL_BMP HIREDATE_IX) */sal,hiredate
    FROM emp e
    WHERE hiredate < sysdate and sal > 2000;

INDEX_SS ; 인덱스 스킵 스캔 방식으로 인덱스를 액세스 하도록 유도

ex) SELECT /*+ INDEX_SS (e emp_idx_ */ ..
    FROM emp e
    WHERE hiredate = sysdate;

** 다시 한번더 확인

NO_INDEX_SS ; 지정한 테이블의 인덱스에 대한 스킵스캔을 제외한 다른 액세스 방법을 사용하도록 유도

INDEX_SS_ASC ; 인덱스 스킵 스캔 방식에서 오름차순으로 읽도록 유도

INDEX_SS_DESC ; 인덱스 스캡 스캔 방식에서 내림차순으로 읽도록 유도

ex) SELECT /*+ INDEX_SS_DESC(e ename_ix) */ ..
    FROM employees e
    WHERE first_name = 'Steven';

3.3.7. 쿼리형태 변형(Query Transformation)을 위한 힌트

USE_CONCAT ; OR 이나 IN 연산자를 별도의 실행 단위로 분리하여 실행계획을 수립하고 연결하는 통합실행계획을 수립하도록 유도

ex) SELECT /*+USE_CONCAT */
    FROM emp
    WHERE job ='CLERK' OR deptno=10;

NO_EXPAND ; OR 이나 IN을 연결 실행계획으로 처리되지 않도록 사용 (USE_CONCAT의 반대)

ex) SELECT /*+ NO_EXPAND */ ..
    FROM CUSTOMER
    WHERE CUST_TYPE IN ('A','B');

REWRITE ; M-VIEW(실체뷰) 를 사용할때 뷰에 사용된 테이블을 직접액세스하거나 M-VIEW를 액세스하는 것중 유리한것을 석택하도록 쿼리 재작성작업을 함

ex) SELECT /*+REWRITE (sales_mv) */
            c.cust_id, MIN(c.cust_name), COUNT(distinct order_id)
    FROM sales s, order o, customer c
    WHERE s.order_id=o.order_id
    AND o.order_cust = c.cust_id
    GROUP BY c.cust_id;

NOREWRITE ; QUERY_REWRITE_ENABLED 파라메터가 TRUE로 정어되어있어도 이를 무시
                     M-view가 있어도 원 테이블로 직접 계산을 유도함으로 최신값으로 결과로 출력 할수있음

ex) SELECT /*+ NOREWRITE */ sum(s.amount_sold) AS dollars
    FROM sales s, times t
    WHERE s.time_id = t.time_id
    GROUP BY t.calendar_month_desc;


MERGE ; 뷰쿼리 병합이 가능함에도 불고하고 뷰쿼리 병합이 일어나지 않을 경우
ex) SELECT /*+ MERGE(table_name) */ ...

NO_MERGE ; 뷰쿼리 병합이 일어나지 않도록 요구 하는 힌트
ex) SELECT /*+ NO_MERGE(table_name) */ ..

STAR_TRANSFORMATION ; 소량의 데이터를 가진 여러개의 디멘전 테이블과 팩트 테이블의 개별 비트맵 인덱스를 이용하여 처리 범위를 줄이는 조인방식

ex) SELECT /*+ STAR_TRANSFORMATION */
                d.dept_name, c.cust_city, p.product_name, SUM(s.amount) sales_amount
    FROM SALES s, PRODUCTS t, CUSTOMERS c, DEPT d
    WHERE s.product_cd = t.product_cd
        AND s.cust_id = c.cust_id
        AND s.sales_dept = d.dept_no
        AND c.cust_grade between '10' and '15'
        AND d.location = 'SEOUL'
        AND p.product_name IN ('PA001','DR210')
    GROUP BY d.dept_name, c.cust_city, p.product_name;

NO_STAR_TRANSFORMATION ; 스타변형 조인을 하지 않도록 유도

FACT ; 스타 변형 조인에서 팩트 테이블을 지정하기 위하 사용
NO_FACT ; 지정한 테이블을 팩트 테이블로 인정하지 말아 달라고 요구

UNNEST ; 서브 쿼리와 메인쿼리를 합쳐 조인 형태로 변형하도록 하는 실행계획을 생성하도록 유도하는 힌트

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

NO_UNNEST ; UNNESTING 을 하지 않도록 유도

3.3.8. 기타 힌트

APPEND ; INSERT문에서 사용, SGA를 거치지 않고 'DIRECT-PATH'방식으로 직접 저장공간으로 입력 (최고 수위점 다음위치에 저장)
ex) INSERT /*+ APPEND */ INTO TAB2
    SELECT *
    FROM TAB1
    WHERE COL1 >= '20050101' ;

NOAPPEND ; INSERT시 'CONVENTIONAL-PATH'방식으로 수행 (직렬모드)

CACHE ; FULL 스캔 방식으로 읽혀진 블록을 LRU 리스트의 최근 사용위치에 머물도록 하여 계속 메모리에 머물수 있도록 처리
ex) SELECT /*+ FULL (t) CACHE(t) */ last name
    FROM tech_spec t;

NOCACHE ; FULL 스캔방식으로 읽혀진 블록을 LRU 리스틔 끝에 위치하도록 유도(메모리에서우선제거), 옵티마이져가 블록을 관리하는 일반적방법

ex) SELECT /*+ FULL(m) NOCACHE (m) */ member_name
    FROM members m;

CARDINALITY ; 옵티마이져에게 해당쿼리나 일부구성에 대한 카디널리티 에상값을 제시하여 실행계획수립에 참조토록 함
                       테이블을 지정하지 않으면 전체 쿼리를 수행한 결과로 얻어진 총건수로 간주

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

CURSOR_SHARING_EXACT ; CURSOR_SHARING 초기화 파라메터가 'EXACT' 일경우와 동일한 결과 , 리터럴 값을 바인드 변수로 변경하지 않음

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

DRIVING_SITE ; 원격 테이블과 조인을 수행할 사이트를 지정하여 분산 쿼리를 최적화 하는데 적용

ex) 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 ; 통계정보를 가지고 있지 않거나 ,에러등의 문제로 사용할수 없게되거나, 너무 오래되어 신뢰할수 없을 때 적용
                            통계정보가 있는 하나의 테이블에 사용할경우 무시됨

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

PUSH_PRED ; 뷰나 인라인뷰의 외부에 있는 조인 조건을 뷰 쿼리 내로 삽입하는 힌트

ex) 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 ; 뷰나 인라인 뷰의 외부에 있는 조인 조건을 뷰쿼리 내료 삽입하지 않도록 하는 힌트

ex) 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 ; 머지 되지않는 서브 쿼리를 최대한 먼저 수행할 수 있도록 실행계획을 수립하기를 요구

ex) SELECT /*+ PUSH_SUBQ(@qb) */ ..
    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;

QB_NAME ; 쿼리 블록에 이름을 부여하여 외부의 다른 힌트에서 참조할수 있도록 함

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

REWRITE_ON_ERROR ; 적합한 M-VIEW가 존재하지 않아 쿼리 재생성을 할수 없을 경우

                                      ORA-30393 에러를 유발하여 쿼리 수행을 중단

역시 옵티마이져에 대한 전체 적인 지식이 받힘이 안되면 사용하는것도 이해하는것도 힘들거 같습니다.

또 실제로 적용해보고 사용해보고 각 건의 경험이 꽤 중요할꺼같습니다.