Oracle Database TIP
오라클 HINT 44 8 99,999+

by 김정식 ALL_ROWS CHOOSE FIRST_ROWS RULE INDEX_ASC INDEX_DESC INDEX_FFS [2002.04.02]


  아래 강좌는 Oracle8i 버전을 기준으로 작성 되었습니다. 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를 줄이거나 동시에 사용하는 사용자 수를 줄여야 합니다.

- 강좌 URL : http://www.gurubee.net/lecture/1260

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by hackeryu [2004.11.08 10:54:47]
USE_NL 설명도 부탁드립니다.

Nested Loop Join을 사용한다는 것으로 알고 있지만..

by 김부장 [2008.03.10 17:46:43]
옵티마이저가 통계정보를 기준으로 해쉬조인으로 데이터를 가져오는 경우 hint에 /*+use_nl(a, b)*/ nested loop join으로 조인을 유도할 경우 데이터가 적을 경우 효과가 있더이다.

by sukja [2008.06.30 17:59:54]
leading 에 대한 설명도 써주세요~~ 9i 에서 10g에 가면서 바뀐기능이 있던데요

by 오라오라 [2010.09.09 11:23:48]
/*+ ORDERED */ 를 하면 순서대로 읽히면서 NL조인을 하지 않나요? /*+use_nl(a, b)*/ 와 같은 힌트가 되는건 아닌지요

by dbjang [2010.10.28 10:30:41]
INDEX_SS 에 대해서 설명 써주세요 ㅎㅎ
skip scan이라고만 알고 잇어서..
자세한 설명 부탁드립니다~

by 김정식 [2010.10.28 10:40:00]
dbjang 처럼 덧글을 달아주시면 이벤트 당첨 조건이 됩니다.
INDEX_SS에 대한 설명도 더 추가해 주시면 당첨 확률이 더 올라갑니다. ^^

by 양대만 [2011.11.09 10:20:33]
혹시 실행되는 SQL이 shared library에 저장되지 않게 하는 힌트나 v$SQL에 안나오게 하는 힌트 있나요?

by 손님 [2012.09.18 10:51:18]

감사히 봤습니다. ㅎㅎ
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입