반응형

/********************************************************************************************
-- Title : [10g] 오라클 힌트 정리
-- Reference : blog.naver.com/kyumi0705
-- Key word : oracle hint 오라클 힌트
********************************************************************************************/

http://blog.naver.com/itough2323?Redirect=Log&logNo=80125972268

http://blog.naver.com/explojh?Redirect=Log&logNo=60036603826

◆ 개요
힌트는 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를 가질 때, 해쉬 알고리즘에 의해 큰 테이블을 여러개의 버켓으로 나누어 쿼리를 수행하는 방식입니다. 작은


반응형

+ Recent posts