Oracle HINT(기초)

오라클 HINT

---

• 쿼리문이 실행될 때 기본적으로 DB내에 존재하는 옵티마이저가 쿼리를 분석한 뒤 가장 좋은 계획을 세워서 쿼리를 실행하고 결과를 반환한다.

  모든 사항을 고려하여 자동으로 결정하므로 편하지만, 100% 믿을 수는 없다.

  그래서 옵티마이저에게 맡기지 않고 쿼리를 작성한 사람이 직접, 원하는 대로 실행방법을 지정하여 실행하는 방법이 오라클 HINT이다.

   

-프로그램이 완벽하지 않기 때문에 세부적이고, 중요한 처리를 사람이 직접 수정하기 위해 사용.

   

• 힌트를 사용하려면 두가지 방법이 있다.

   

1.   /*+   힌트    */

2.   --+   힌트

   

• 두 가지의 방식은 실제 힌트문장에는 차이가 없고, 서술하는 방식의 차이이다, 1번은 여러줄에 걸쳐쓸 수 있는 반면 2번은 한 줄에만 적용이 된다.  개발할때 주석도 한줄주석 여러줄 주석 그런거 처럼…

   

Hints from Optimization Approaches and Goals
HINT	내용	사용법
ALL_ROWS	cost-based optimizer에서 전체 응답시간이 가장 적은 plan 선택	/*+ ALL_ROWS */
FIRST_ROWS(n)	: cost-based optimizer에서 첫번째 row가 가장 빨리 나오는 plan으로 선택	/*+ FIRST_ROWS(10) */ /*+ FIRST_ROWS(n) */
CHOOSE	통계정보의 유무에 따라 RBO/CBO 가 선택됨	/*+ CHOOSE */
RULE	rule-based optimization로 plan 작성	/*+ RULE */

   

<TEST>

   

select distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:09

   

1. /*+ ALL_ROWS */

      

select /*+ All_ROWS(10) */ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:09

   

   

   

1. /*+ FIRST_ROWS(10) */

      

select /*+ FiRST_ROWS(10) */ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:10

   

   

   

1. /*+ CHOOSE */

      

select /*+ CHOOSE */ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:10

   

   

1. /*+ RULE */

      

select /*+ RULE */ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:12

   

Hints for Query Transformations
HINT	내용	사용법
USE_CONCAT	: IN 또는 OR 연산자가 있을 경우 CONCATENATION 을 사용하도록 한다 in절을 concatenation access operation으로 수행	/*+ USE_CONCAT */
USE_EXPAND	in절을 concatenation access operation으로 수행 못하게 함
NO_EXPAND
REWRITE	query rewrite 수행
EXPAND_GSET_TO_UNION
NOREWRITE	query rewrite 를  수행 못함
MERGE	view merging 수행
NO_MERGE	view merging 수행 못하게 함
STAR_TRANSFORMATION
FACT
NO_FACT

   

Hints from Access Paths
HINT	내용	사용법
FULL	TABLE FULL SCAN 을 하도록 유도 FULL SCAN	/*+ FULL(A) don't use the index on A.table */ FULL(TALBE명)
ROWID
CLUSTER	Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용 됩니다.	/*+CLUSTER(table name)*/
HASH
INDEX	( = INDEX_ASC) : 지정한 인덱스를 사용하여 ACCESS PLAN을 작성하도록 유도 INDEX를 순차적으로 스캔	/*+ INDEX(patients name_index) */, INDEX(TABLE명, INDEX명)
INDEX_ASC	지정된 index를 오름차순으로 쓰게끔 지정 합니다. Default로 Index Scan은 오름차순.
INDEX_JOIN
INDEX_DESC	INDEX_DESC : 지정한 인덱스를 사용하여 ACCESS PLAN을 작성하도록 유도 (인덱스를 맨뒤에서부터 SCAN 하도록 한다) INDEX를 역순으로 스캔	/*+ INDEX_DESC(patients name_index) */ INDEX_DESC(TABLE명, INDEX명)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE명, INDEX명)
AND_EQUAL	NDEX MERGE 수행	AND_EQUALS(INDEX_NAME, INDEX_NAME)

   

<Test1>

   

select d.department_id, d.department_name, COUNT(*) from departments d, employees e where d.department_id = e.department_id group by d.department_id, d.department_name having count(*) <3;

Full(A)

   

   

   

화면 캡처: 2011-12-27 오전 11:16

   

   

• /*+ FULL(A) don't use the index on A.table */

     

select /*+ FULL(e) don't use the index on e*/ distinct d.department_id, d.department_name, COUNT(*) from departments d, employees e where d.department_id = e.department_id group by d.department_id, d.department_name having count(*) <3;

   

   

화면 캡처: 2011-12-27 오전 11:19

   

   

<Test2>

   

select distinct job_id from employees where department_id in (10,20);

   

   

   

화면 캡처: 2011-12-27 오전 11:26

  

• /*+ INDEX(patients name_index) */

     

select /*+ INDEX(employees EMP_JOB_IX)*/ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:28

   

select /*+ INDEX(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:28

   

   

• /*+ INDEX_ASC(patients name_index) */

     

  선수되어있던 인덱스를 이용해서 순차적으로 검색?

     

select /*+ INDEX_ASC(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:31

   

• /*+ INDEXJOIN(patients name_index) */

     

select /*+ INDEX_JOIN(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:32

   

   

• /*+ INDEX_DESC(patients name_index) */

     

select /*+ INDEX_DESC(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:33

   

   

   

• /*+ INDEX_FFS(patients name_index) */

     

select /*+ INDEX_FFS(employees EMP_EMP_ID_PK)*/ distinct job_id from employees where department_id in (10,20);

   

   

화면 캡처: 2011-12-27 오전 11:33

   

Hints from join orders
HINT	내용	사용법
ORDERED	FROM 절에 있는 테이블들의 JOIN 순서를 지정함	/*+ ORDERED */
STAR
DRIVING	해당 테이블을 먼저 DRIVING- driving(table)

   

Hints for join Operations
HINT	내용	사용법
USE_NL	지정한 테이블에 대해 NESTED LOOP 조인이 일어나도록 지정함 NESTED LOOP JOIN	/*+ ORDERED USE_NL() to get first row faster */ USE_NL(TABLE1, TABLE2)
USE_MERGE	지정한 테이블에 대해 SORT MERGE 조인이 일어나도록 지정함 SORT MERGE JOIN	/*+USE_MERGE(emp dept)*/ USE_MERGE(TABBLE1, TABLE2)
USE_HASH	지정한 테이블에 대해 HASH 조인이 일어나도록 지정함 HASH JOIN	/*+USE_HASH(emp dept)*/ USE_HASH(TABLE1, TABLE2)
DRIVING_SITE
LEADING
HASH_AJ, HASH_SJ	HASH ANTIJOIN HASH SEMIJOIN	HASH_AJ(TABLE1, TABLE2) HASH_SJ(TABLE1, TABLE2)
MERGE_AJ, MERGE_SJ	SORT MERGE ANTIJOIN SORT MERGE SEMIJOIN	MERGE_AJ(TABLE1, TABLE2) MERGE_SJ(TABLE1, TABLE2)
NL_AJ, NL_SJ	NESTED LOOP ANTI JOIN NESTED LOOP SEMIJOIN	NL_AJ(TABLE1, TABLE2) NL_SJ(TABLE1, TABLE2)

   

<Test>

   

select * from employees, departments where employees.department_id = departments.department_id;

   

   

   

화면 캡처: 2011-12-27 오전 11:40

   

   

1. /*+ USE_NL(TABBLE1, TABLE2) */

      

select /*+ USE_NL(employees, departments) */ * from employees, departments where employees.department_id = departments.department_id;

   

   

화면 캡처: 2011-12-27 오전 11:40

   

   

1. /*+ USE_MERGE(TABBLE1, TABLE2) */

      

select /*+ USE_MERGE(employees, departments) */ * from employees, departments where employees.department_id = departments.department_id;

   

   

화면 캡처: 2011-12-27 오전 11:40

   

   

1. /*+ USE_HASH(TABBLE1, TABLE2) */

      

select /*+ USE_HASH(employees, departments) */ * from employees, departments where employees.department_id = departments.department_id;

   

   

화면 캡처: 2011-12-27 오전 11:40

   

Hints for Parallel Execution
HINT	내용	사용법
Parallel	select, insert 시 여러 개의 프로세스로 수행- parallel(table, 개수)
NOPARALLEL
PQ_DISTRIBUTE
PARALLEL_INDEX	NDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE명,INDEX명)
NOPARALLEL_INDEX	NDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE명,INDEX명)

   

DEGREE의 의미 및 결정

•   Parallel Query에서 degree란 하나의 operation 수행에 대한 server process의 개수 입니다. 이러한 degree 결정에 영향을 주는 요인들에는 다음과 같은 것들이 있습니다.

  (1) system의 CPU 갯수

  (2) system의 maximum process 갯수

  (3) table이 striping되어 있는 경우, 그 table이 걸쳐있는 disk의 갯수

  (4) data의 위치 (즉, memory에 cache되어 있는지, disk에 있는지)

  (5) query의 형태 (예를 들어 sorts 혹은 full table scan)

•   한 사용자만이 parallel query를 사용하는 경우, sorting이 많이 필요한 작업과 같은 CPU-bound 작업의 경우는 CPU 갯수의 1 ~ 2배의 degree가 적당하며, sorting보다는 table scan과 같은 I/O bound 작업의 경우는 disk drive 갯수의 1 ~ 2배가 적당합니다.
•   동시에 수행되는 parallel query가 많은 경우에는 위의 각 사용자의 degree를 줄이거나 동시에 사용하는 사용자 수를 줄여야 합니다.

   

Additional Hints
HINT	내용	사용법
APPEND	insert 시 direct loading	/*+ APPEND */
NOAPPEND
CACHE	데이터를 메모리에 caching
NOCACHE	데이터를 메모리에 caching하지 않음
UNNEST
NO_UNNEST
PUSH_PRED
NO_PUSH_PRED
PUSH_SUBQ	subquery를 먼저 수행
NO_PUSH_SUBQ
ORDERED_PREDICATES
CURSOR_SHARING_EXACT
DYNAMIC_SAMPLING

   

   

조인 순서 조정을 위한 힌트

이 힌트는 다수의 테이블을 조인하는 경우에 조인 순서에 혼선이 있을 때 적용하는 것이 가장 일반적이다.

Sort Merge 조인 이나 해쉬조인에서는 적은 테이블을 먼저 처리하도록 유도할 때 사용하는 것이 좋다.

ORDERED

FROM절에 기술된 테이블 순서대로 조인을 수행하도록 유도한다.

만약 LEADING 힌트와 함께 사용하면 LEADING 힌트는 무시된다.

예) SELECT /*+ ORDERED */ ...
FROM TAB1, TAB2, TAB3
WHERE ....

ORDERED 힌트는 액세스 순서만 제시할 뿐이고, 조인 방법 과는 무관하기 때문에 조인방법을 유도하기 위한 USE_NL, USE_MERGE등의 힌트와 함께 사용하는 것이 일반적이다

예) SELECT /*+ ORDERED USE_NL(A B C) */.........
FROM TAB1 a, TAB2 b, TAB3 c
WHERE ........

LEADING

FROM절에 기술한 테이블의 순서와 상관없이 조인 순서를 제어하는 힌트로서 ORDERED 힌트와는 달리 FROM절을 변경 필요 없이 사용할 수 있다.

ORDERED 힌트와 함께 사용되면 무시된다.

예) SELECT /*+ LEADING(b c) */......
FROM CUST a, ORDER_DETAIL b, ITEM c
WHERE a.cust_no = b.cust_no
         AND b.item_no = c.item_no
         AND ....

    

   

   

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 10g Documentation Library - councepts

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.oracleclub.com/lecture/1260>

원본 위치 <http://blog.naver.com/PostView.nhn?blogId=comele&logNo=70069313846&beginTime=0&jumpingVid=&from=search&redirect=Log&widgetTypeCall=true&topReferer=http%3A%2F%2Fsearch.naver.com%2Fsearch.naver%3Fsm%3Dtab_hty.top%26where%3Dnexearch%26ie%3Dutf8%26query%3D%25EC%2598%25A4%25EB%259D%25BC%25ED%2581%25B4%2B%25ED%259E%258C%25ED%258A%25B8%26x%3D0%26y%3D0>

원본 위치 <http://blog.naver.com/PostView.nhn?blogId=myshyz&logNo=50125858009&beginTime=0&jumpingVid=&from=search&redirect=Log&widgetTypeCall=true&topReferer=http%3A%2F%2Fsearch.naver.com%2Fsearch.naver%3Fwhere%3Dnexearch%26query%3DOracle%2BHint%26x%3D21%26y%3D23%26sm%3Dtop_hty%26fbm%3D1%26ie%3Dutf8>