QB NAME

基本情報

 

ヒント名

QB_NAME

 

構文

/*+ QB_NAME(queryblock_name)*/

 

サポート

10g R1〜

 

詳細

Query Blockに明示的に名前を付与する役割をする。主Inline ViewやSubqueryなどの名前を付けて実行計画の読みやすさを向上させる目的で使用される。

 

使用例

 

実行計画の可読性

次の例のようにQB_NAMEヒントを使用すると、Inline ViewとSubqueryが多く使用されている複雑なクエリの実行プランの読みやすさを向上させることができます。

 

- Statistics LevelをAllに変更します。
alter session set statistics_level = all;

- QB_NAMEヒント付与
explain plan for select /*+ qb_name(main) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;

- CursorのALL statsを照会します。 
select * from table(dbms_xplan.display(null,null, 'ALL'));

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   113 |    14  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE         |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN      | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN              |        |     1 |   113 |    14  (15)| 00:00:01 |
|*  4 |   HASH JOIN SEMI        |        |     1 |   106 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS         |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN           |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN          |        |     1 |   53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS      |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL| T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS FULL    | T3     |     1 |    20 |     2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS FULL     | T5     |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SCALAR
   2 - SCALAR       / S@SCALAR
   3 - SEL$EA1A1EE6
   9 - SEL$EA1A1EE6 / T5@INLINE
  10 - SEL$EA1A1EE6 / T4@INLINE
  11 - SEL$EA1A1EE6 / T2@MAIN
  12 - SEL$EA1A1EE6 / T1@MAIN
  13 - SEL$EA1A1EE6 / T3@MAIN
  14 - SEL$EA1A1EE6 / T3@SUBQUERY
  15 - SEL$EA1A1EE6 / T5@MAIN

 

Query Block Nameを他のHintで使用

次の例のようにQB_NAMEを利用して付けた名前を、他のヒントで使用することができます。

 

select /*+ qb_name(main) no_unnest(@subquery) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;