블로그 이미지
잡초 개발자의 좌충우돌 이야기 yysvip

카테고리

분류 전체보기 (211)
Java Programing (24)
Web Programing (20)
Database (36)
Tool (46)
까칠한 IT (27)
까칠한 정보 (42)
까칠한 Strory (16)
까칠한 Project (0)
Total906,149
Today3,632
Yesterday7,350

달력

« » 2018.10
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

공지사항

오라클 힌트 정리표

  까칠한 남자 Strory       yysvip.tistory.com       잡초 개발자 까찰한 쑤의 좌충우돌 개발 이야기

 

 

◆ 개요

 힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다.


SQL에 포함되어 쓰여져 Optimizer의 실행 계획을 원하는 대로 바꿀 수 있게 해준다.


오라클 Optimizer라고 해서 항상 최선의 실행 계획을 수립할 수는 없으므로 테이블이나 인덱스의 잘못된 실행 계획을 개발자가 직접 바꿀 수 있도록 도와주는 것이다.


사용자는 특정 SQL 문장에서 어떤 인덱스가 선택도가 높은지에 대해 알고 있는데 이 경우 오라클 서버의 Optimizer에 의존하여 나온 실행 계획보다 훨씬 효율적인 실행 계획을 사용자가 구사할 수 있다.

 

 

◆ 사용

힌트를 사용하여 아래와 같은 것들을 할 수 있다.
액세스 경로, 조인 순서, 병렬 및 직렬 처리, Optimizer의 목표(Goal)를 변경 가능하다.

 

 

◆ 오라클 힌트 사용예

 SELECT ⁄*+ INDEX(idx_col1) *⁄
        id, password, name
  FROM emp;

SELECT ⁄*+ ORDERED  INDEX(b idx_col1) *⁄
       id, password, name
  FROM emp a
     , depart b

※ 주의! 주석 표시 뒤에 '+' 기호가 있다.
 

 

◆ INDEX Access Operation 관련 HINT

 HINT

 내용

 사용법

 INDEX

 INDEX를 순차적으로 스캔

 INDEX(TABLE_name, INDEX_name)

 INDEX_ASC

 INDEX를 내림차순으로 스캔. 

 

 INDEX_DESC

 INDEX를 오름차순으로 스캔.

 INDEX_DESC(TABLE_name, INDEX_name)

 INDEX_FFS

 INDEX FAST FULL SCAN

 INDEX_FFS(TABLE_name, INDEX_name)

 PARALLEL_INDEX

 INDEX PARALLEL SCAN

 PARALLEL_INDEX(TABLE_name, INDEX_name)

 NOPARALLEL_INDEX

 INDEX PARALLEL SCAN 제한

 NOPARALLEL_INDEX(TABLE_name, INDEX_name)

 AND_EQUALS

 여러개의 INDEX MARGE 수행

 AND_EQUALS(INDEX_name, INDEX_name)

 FULL

 FULL SCAN

 지정된 테이블에 대한 전체 스캔.

 FULL(TABLE_name)


 

◆ JOIN Access Operator 관련 HINT

 HINT

 내용

 사용

 USE_NL

 NESTED LOOP JOIN

 옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다.

 먼저 특정 TABLE의 ROW에 액세스하고 그 값에 해당하는 다른 TABLE의 ROW를 찾는 작업을 해당범위까지 실행하는 조인.

 USE_NL(TABLE1, TABLE2)

 USE_NL_WITH_INDEX

 INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 한다.

 USE_NL_WITH_INDEX(TABLE  INDEX)

 USE_MERGE

 SORT MERGE JOIN

 옵티마이저가 SORT MERGE JOIN을 사용하도록 한다.

 먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식.

 USE_MERGE(TABLE1, TABLE2)

 USE_HASH

 HASH JOIN

 옵티마이저가 HASH JOIN을 사용하도록 한다.

 USE_HASH(TABLE1, TABLE2)

 HASH_AJ

 HASH ANTIJOIN

 HASH_AJ(TABLE1, TABLE2)

 HASH_SJ

 HASH SEMIJOIN

 HASH_SJ(TABLE1, TABLE2)

 NL_AJ

 NESTED LOOP ANTIJOIN

 NL_AJ(TABLE1, TABLE2)

 NL_SJ

 NESTED LOOP SEMIJOIN

 NL_SJ(TABLE1, TABLE2)

 MERGE_AJ

 SORT MERGE ANTIJOIN

 MERGE_AJ(TABLE1, TABLE2)

 MERGE_SJ

 SORT MERGE SEMIJOIN

 MERGE_SJ(TABLE1, TABLE2)

 

 

◆ JOIN시 DRIVING 순서 결정 HINT

 HINT

 내용

 사용법

 ORDERED

 FROM절에 명시된 테이블의 순서대로 DRIVING

 

 LEADING 

 파라미터에 명시된 테이블의 순서대로 JOIN

 LEAING(TABLE_name1, TABLE_name2, ...)

 DRIVING

 해당 테이블을 먼저 DRIVING

 DRIVING(TABLE)


 

◆ 기타 HINT

 HINT

 내용

 사용법

 APPEND

 INSERT시 DIRECT LOADING

 

 PARALLEL

 SELECT, INSERT시 여러개의 프로세스로 수행

 PARALLEL(TABLE, 개수)

 CACHE

 데이터를 메모리에 CACHING

 

 NOCACHE

 데이터를 메모리에 CACHING하지 않음

 

 PUSH_SUBQ

 SUBQUERY를 먼저 수행

 

 REWRITE

 QUERY REWRITE 수행

 

 NOREWIRTE

 QUERY REWRITE를 수행 못함

 

 USE_CONCAT

 IN절을 CONCATENATION ACCESS OPERATION으로 수행

 

 USE_EXPAND

 IN절을 CONCATENATION ACCESS OPERATION으로 수행못하게 함

 

 MERGE

 VIEW MERGING 수행

 

 NO_MERGE

 VIEW MERGING 수행못하게 함

 

 

추가

ALL_ROWS : 가장 좋은 단위 처리량의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (즉, 전체적인 최소의 자원 소비, 모든 레코드의 처리하는 시간의 최소화를 목적으로 최적화)

FIRST_ROWS : 가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (첫번째 레코드의 추출 시간을 최소화할 목적으로 최적화)

CHOOSE : 최적자(optimizer)가 그 문에 의해 접근된 테이블을 위해 통계의 존재에 근거를 두는 SQL문을 위해 rule-based 접근 방법과 cost-based 접근 방법 사이에 선택하게 됩니다.

CLUSTER : 지정된 테이블에 대한 클러스터 스캔.

HASH : 지정된 테이블에 대한 해쉬 스캔.

ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔.

RULE : explicitlly chooses rule-based optimization for a statement block. rule-base Optimizer를 사용.


 

◆ 주의

 SELECT ⁄*+ ORDERED USE_NL(A B C) INDEX(B ITOREDRETL1) *⁄

   FROM TORDERDTL  B, TORDER  A, TITEM  C

  WHERE ...

1. ORDERED : FROM 절에 기술한 테이블 순(B → A → C 순)으로 조인함. USE_NL에 사용한 순서가 아님.

    ※ 참고) LEADING, 예) LEADING(B) : FROM 절의 기술 순서와 상관없이 B테이블이 가장 먼저 선행됨.

 

2. USE_NL : 조인방법을 Nested Loops방식으로 선택.

    예) USE_NL(A B), 테이블명이 아닌 테이블에 대한 Alias명

    참고) USE_HASH, USE_MERGE

 

3. INDEX : 특정 인덱스를 오름차순으로 읽음.

    예) INDEX(B  ITORDERDTL1) : B는 TORDERDTL 테이블의 Alias명, 사용할 인덱스명 기술

    참고) USE_DESC(B ITORDERDTL1) : ITORDERDTL1 인덱스를 내림차순으로 읽음

 

◆ 실행계획 살펴보기 1

SELECT *

    FROM ( ==> 인라인뷰 3

                ( ==> 인라인뷰 2

                   ( ==> 인라인뷰 1

                   )

                )

              )

 

과 같이 되어 있다고 하자.

 

이럴 경우 실행계획을 보면, 우리가 예상했던대로 인라인뷰1 ==> 인라인뷰2 ==> 인라인뷰3 순서로 드라이빙 되지 않는 경우가 있다.

 

이런 경우 강제로 위 순서대로 실행계획을 수립하도록 하는 힌트

 

SELECT /*+ ORDERED */

            *

  FROM ( .....

 

위 힌트를 사용하면 맨 깊숙한 인라인뷰부터 실행된다.

 

 

◆ 힌트 사용하기

 

1. /*+ USE_CONCAT */

USE_CONCAT : 조건절에 있는 OR 연산자조건 (또는 IN 연산자 조건)을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결(Concatenation)하는 실행계획을 수립하도록 유도하는 힌트.

반드시 처리주관 조건이 OR로 나누어졌을 때 적용해야 하며, 잘못 사용하면 비효율이 발생할 수 있으므로 주의해야 함.

 

예>

SELECT ⁄*+ USE_CONCAT *⁄

   FROM employees

 WHERE job = &job

       OR dept_no = &deptno;

풀어쓰자면

SELECT *

    FROM employees

  WHERE job = &job

UNION ALL

SELECT *

    FROM employees

WHERER dept_no = &deptno;

WHERE 절 이후에 나오는  컬럼에 맞게 인덱스를 탄다.

 

2. /*+ NO_EXPAND */

조건절에 있는 OR 연산자 조건 또는 IN 연산자 조건을 연결 실행계획으로 처리되지 않도록 할 때 사용하는 힌트.

USE_CONCAT의 반대 개념.

 

예>

SELECT ⁄*+ NO_EXPAND *⁄

   FROM customer

 WHERE cust_type in ('A','B');

참고 : http://blog.naver.com/hkjhc107?Redirect=Log&logNo=130035827974

 

 

 

◆ 참고

1. Nested Loop

   - 테이블의 인덱스끼리 inner-outer 루프를 형성하여 결과를 쿼리하는 방식입니다.

   - 제일 많은 유형의 실행계획입니다.

 

2. Sort Merge

   - 쿼리의 결과가 많은 양의 데이터를 읽는 경우, 테이블들을 각각 full-scan하여 같은 키값을 갖는 데이터끼리 조인하여 실행합니다.

   - Sort-Merge 방식은 많은 메모리와 디스크 I/O를 필요로 하기 때문에, sqlplus를 실행하는 주체의 메모리/CPU/디스크 스펙에 많은 영향을 받습니다.

 

3. Hash Join

   - 한 테이블은 매우 많은 Row를 갖고, 다른 한 테이블은 매우 적은 Row를 가질 때, 해쉬 알고리즘에 의해 큰 테이블을 여러개의 버켓으로 나누어 쿼리를 수행하는 방식입니다. 작은 테이블은 인덱스를 태우는 것보다 full-scan을 하는 것이 유리할 때 사용됩니다.

 

 

Posted by yysvip

최근에 달린 댓글

최근에 받은 트랙백

글 보관함