'오라클 힌트'에 해당되는 글 1건

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 */

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

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

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

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

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

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)

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)

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;

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

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

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

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

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

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

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

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)

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

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

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

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

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명)

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