Optimizing Oracle Optimizer (2011년)
QB_NAME 0 0 2,341

by 구루비스터디 QB_NAME [2018.07.14]


  • QB_NAME 힌트는 복잡한 쿼리의 가독성을 높이고 제어를 쉽게함
  • 10g에서 추가됨



create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);
create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);

exec dbms_stats.gather_table_stats ('HYUN','t1',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');
exec dbms_stats.gather_table_stats ('HYUN','t2',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');


select
  t1.c1, v.c2
from
  t1,
  (select
    c1, c2
   from t2
   where c1 between 1 and 5) v
where
  t1.c1 = v.c1
;


-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    39 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    26 |     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |     1 |    39 |     0   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | T1_N1 |     1 |    13 |     0   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


t2에 대해서 FULL SCAN을 태우고 싶다면 서브쿼리에 힌트를 추가한다


select
  t1.c1, v.c2
from
  t1,
  (select /*+ FULL(T2) */
    c1, c2
   from t2
   where c1 between 1 and 5) v
where
  t1.c1 = v.c1
;

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2    |     1 |    26 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T1_N1 |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">=1 AND "C1"<=5)
   3 - access("T1"."C1"="C1")
       filter("T1"."C1">=1 AND "T1"."C1"<=5)


서브쿼리에 힌트를 주지 못하고 최상위 블럭에 힌트를 준다면


  select /*+ full(t2) */
    t1.c1, v.c2
  from
    t1,
    (select
      c1, c2
     from t2
     where c1 between 1 and 5) v
  where
    t1.c1 = v.c1
  ;       

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    39 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    26 |     0   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |     1 |    39 |     0   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | T1_N1 |     1 |    13 |     0   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."C1">=1 AND "T1"."C1"<=5)
   4 - access("T1"."C1"="C1")
       filter("C1">=1 AND "C1"<=5)  
       

힌트가 제대로 반영되지 않은 것을 볼 수 있다.



***************************************************************************************
* GLOBAL힌트를 사용하여 해당 쿼리블럭에 힌트를 적용시킬 수 있음
***************************************************************************************
  select /*+ full(v.t2) */
    t1.c1, v.c2
  from
    t1,
    (select
      c1, c2
     from t2
     where c1 between 1 and 5) v
  where
    t1.c1 = v.c1
  ;       

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2    |     1 |    26 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T1_N1 |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">=1 AND "C1"<=5)
   3 - access("T1"."C1"="C1")
       filter("T1"."C1">=1 AND "T1"."C1"<=5)



DBMS_XPLAN 패키지를 사용하여 Query Block Name 을 찾는 방법은 생략함

       
***************************************************************************************
* QB_NAME 힌트로 해당 블럭의 이름을 지정할 수 있음
***************************************************************************************

  select /*+ full(@view1 t2) */
    t1.c1, v.c2
  from
    t1,
    (select /*+ qb_name(view1) */
      c1, c2
     from t2
     where c1 between 1 and 5) v
  where
    t1.c1 = v.c1
  ;

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2    |     1 |    26 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T1_N1 |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">=1 AND "C1"<=5)
   3 - access("T1"."C1"="C1")
       filter("T1"."C1">=1 AND "T1"."C1"<=5)




"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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