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)