oracle - 오라클에서 힌트(Hint) 사용 예제

오라클에서 힌트(Hint) 사용 예제 

SQL*Plus로 scott 계정으로 로그인한 후 test라는 테이블을 만들어 실습을 합니다.

SQL> create table test (

  2  id number not null primary key,

  3  name varchar2(20)

  4  );

테이블이 생성되었습니다.

데이터는 5건 넣는데 순서를 잘 보시기를 바랍니다.

기본적으로 select하면 넣은 순서대로 나옵니다.

SQL> insert into test values  (4,'4길동');

1 개의 행이 만들어졌습니다.

SQL> insert into test values  (3,'3길동');

1 개의 행이 만들어졌습니다.

SQL> insert into test values  (5,'3길동');

1 개의 행이 만들어졌습니다.

SQL> insert into test values  (1,'1길동');

1 개의 행이 만들어졌습니다.

SQL> insert into test values  (2,'2길동');

1 개의 행이 만들어졌습니다.

SQL> select * from test;

        ID NAME

---------- --------------------

         4 4길동

         3 3길동

         5 3길동

         1 1길동

         2 2길동

그래서 이름으로 정렬하여 볼려면...

(그런데 order by는 사용하지 말라고 권고 하고 있죠.. .별도의 소트를 위한 공간을 이용하여 데이터를 정렬하므로 DB에겐 OverHead 가 있는거죠... 물론 대량의 데이터 일때...)

SQL> select * from test order by name;

        ID NAME

---------- --------------------

         1 1길동

         2 2길동

         3 3길동

         5 3길동

         4 4길동

이 경우 인덱스를 이용하면 쉽게 정렬된 데이터를 볼 수가 있습니다.

(멋지죠^^)

SQL> create index idx_test_name on test(name);

인덱스가 생성되었습니다.

SQL> select * from test where name > ' ';

        ID NAME

---------- --------------------

         1 1길동

         2 2길동

         3 3길동

         5 3길동

         4 4길동

그럼 이번에는 이름 역순으로 데이터를 가지고 올려고 합니다.

어떻게 할까요.. 아래처럼 order by를 descending으로 할까요...?

SQL> select * from test order by name desc;

        ID NAME

---------- --------------------

         4 4길동

         3 3길동

         5 3길동

         2 2길동

         1 1길동

이럴때 힌트라는 것을 이용하면 쉽게 해결할 수가 있습니다...

SQL> select /*+ index_desc (test, idx_tes_name) */ *

  2  from test

  3  where name > ' ';

        ID NAME

---------- --------------------

         1 1길동

         2 2길동

         3 3길동

         5 3길동

         4 4길동

물론 힌트는 여러 종류가 있습니다. 개인적으로 공부해 보시기 바라구요~~~

 

 

 

힌트를 사용할 때 고려되어야 하는 사항

 

PURPOSE

 

힌트를 사용할때 고려되어야 하는 사항

힌트를 사용하였다는 것은 plan에 대한 평가가 Cost Based Optimization를 사용하였다고

할 수 있다.

RULE일 경우, 힌트를 제외한 모든 힌트는 optimizer를 선택하는 것이다.

예를들어 통계정보가 없는 상태에서 힌트를 사용했다면, 일부분의 액세스에 대한 힌트는

잘못된 실행계획으로 갈 확률이 높다.

통계정보가 없는 상태에서 특정부분에 대한 액세스에 대한 힌트를 주면 Rule 대신 Cost

가 사용되는데, 통계정보는 전혀 없는 상태이므로 오류의 가능성이 커지는 것이다.

이럴때 액세스를 분명하고 완전하게 힌트를 통해주는 것이 필요하다.

힌트는 사용자명을 참조해서는 안된다. Alias를 정의했다면 반드시 Alias를 사용해야 

한다. 그리고 Pl/sql 블럭내에서 사용되는 경우라면 반드시 '/*+' 다음에 스페이스가

존재해야한다.

또한 3rd Party 툴에서 간혹 힌트를 사용할 수 없는 경우도 있는데 이럴때

뷰를 통하여 힌트를 정의하면 힌트는 뷰를 액세스하는 메인 절에서의 힌트와 경합하여 

원하는 실행으로 풀리지 않는 경우도 있다.

힌트를 주어도 원하는 액세스가 이루어 지지 않는 경우는 SQL처리 메카니즘으로 보아

그러한 액세스가 불가함을 의미하는 경우가 많다는 것도 고려해야한다.

특히 인라인 뷰나 서브커리를 이용한 액세스때 no merge효과가 나타나는 SQL 형태의

경우는 원하는 액세스를 유도하기가 어렵다.

Example

/*+ ALL_ROWS */

  ALL_ROWS는 Full Table Scan을 선호하며 CBO(Cost Based Optimization)는 default로

  ALL_ROWS를 선택 합니다.         

            

/*+ 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을 선호 합니다.

/*+ RULE */

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

<Access Methods - 접근 방법>

/*+ 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 function의 기능을 대신할 수 있습니다.    

/*+ INDEX_FFS(table index) */

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

/*+ ROWID(table) */

  Rowid로 Table Scan을 하도록 지정 합니다.

 

<Join Orders>

/*+ ORDERED */

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

<Join Operations>

/*+ USE_HASH (table_name) */

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

/*+ USE_MERGE (table_name) */

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

<Parallel Execution>

/*+ NOPARALLEL(table_name) */

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

/*+ PARALLEL(table_name, degree) */

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