[Oracle] 오라클 HINT

/*+ ALL_ROWS */

  ALL_ROWS는 Full Table Scan을 선호하며 CBO(Cost Based Optimization)는 default로 ALL_ROWS를 선택 합니다.

 
SQL> SELECT /*+ ALL_ROWS */  ename, hiredate 
     FROM emp  
     WHERE ename like '%%%';
       
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=5 Bytes=80)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=80)
    

 

/*+ CHOOSE */

  Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지 CBO(Cost Based Optimization) 인지를 선택 합니다. 만약 주어진 table의 통계 정보가 없다면 Rule Based 접근 방식을 사용 합니다.

 

/*+ FIRST_ROWS */

  Full Table Scan보다는 index scan을 선호하며 Interactive Application인 경우 best response time을 제공 합니다. 또한 sort merge join보다는 nested loop join을 선호 합니다.

 
SQL> SELECT /*+ FIRST_ROWS */  ename 
     FROM emp 
     WHERE empno=7876;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=20)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=20)
   2    1     INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=1)
    

 

/*+ RULE */

  Rule Based 접근 방식을 사용하도록 지정 합니다.

 

/*+ CLUSTER(table_name) */

  Cluster Scan을 선택하도록 지정한다. 따라서 clustered object들에만 적용 됩니다.

 

/*+ FULL(table_name) */

  Table을 Full Scan하길 원할 때 사용 합니다.

 

/*+ HASH(table) */

  Hash scan을 선택하도록 지정한다. 이 hint는 HASHKEYS parameter를 가지고 만들어진 cluster내에 저장된 table에만 적용이 됩니다.

 

/*+ INDEX(table_name index_name) */

  지정된 index를 강제적으로 쓰게끔 지정 합니다.

 

/*+ INDEX_ASC(table_name index_name) */

  지정된 index를 오름차순으로 쓰게끔 지정 합니다. Default로 Index Scan은 오름차순 입니다

 

/*+ INDEX_DESC(table_name index_name) */

  지정된 index를 내림차순으로 쓰게끔 지정 합니다.

  아래 예제는 제일 큰 것 하나만 조회되므로, max 함수의 기능을 대신할 수 있습니다.

 
SQL> SELECT /*+ index_desc(emp pk_emp) */  empno
     FROM   emp
     WHERE  rownum = 1 ;
    

 

/*+ INDEX_FFS(table index) */

  Full table scan보다 빠른 Full index scan을 유도 합니다.

 

/*+ ORDERED */

  From절에 기술된 테이블 순서대로 join이 일어나도록 유도 합니다.

 

/*+ USE_HASH (table_name) */

  각 테이블간 HASH JOIN이 일어나도록 유도 합니다.

 

*+ USE_MERGE (table_name) */

  지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도 합니다.

 

/*+ NOPARALLEL(table_name) */

  NOPARALLEL hint를 사용하면, parallel query option을 사용하지 않도록 할 수 있다.

 

/*+ PARALLEL(table_name, degree) */

  PARALLEL hint를 사용하면 query에 포함된 table의 degree를 설정할 수 있습니다.

  예를 들어, 다음과 같이 hint를 적어 degree 4로 parallel query option을 실행하도록 할 수 있습니다. 이 때 parallel이란 글자와 괄호( '(' )사이에 blank를 넣지 않도록 주의해야 합니다.

 
SQL> SELECT /*+ PARALLEL(emp, 4) */   * 
     FROM emp;
    

 

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를 줄이거나 동시에 사용하는 사용자 수를 줄여야 합니다.