2017.12.20
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;