该hint用于子查询(query_block)
很多的情况下，如果子查询共用相同的别名（alias）， 可以通过设定不同的qb_name分别标识子查询。
实例如下
SQL*Plus: Release 10.2.0.1.0 – Production on Sat Dec 19 22:16:13 2009
。。。。。
43@dyl@TEST>EXPLAIN PLAN SET statement_id = ‘qb_name’
FOR
SELECT /*+ QB_NAME(outer) */
e.ename
, e.sal
FROM (
SELECT /*+ QB_NAME(inline_view) */
*
FROM emp e
WHERE e.sal > 300
AND e.deptno IN ( SELECT /*+ QB_NAME(dept_subquery) */ d.deptno
FROM dept d
WHERE d.dname IN (‘SALES’,'ACCOUNTING’) )
) e;
Explained.
Elapsed: 00:00:00.17
使用10g下的DBMS_XPLAN.DISPLAY，注意必须使用ALL参数，才能看得到自定义的query_block name
43@dyl@TEST>SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,'qb_name’,’ALL‘));
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 351108634
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 9 | 234 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 9 | 234 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
—————————————————————————————-
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$B9DAFA34
2 – SEL$B9DAFA34 / E@INLINE_VIEW
3 – SEL$B9DAFA34 / D@DEPT_SUBQUERY
4 – SEL$B9DAFA34 / D@DEPT_SUBQUERY
Predicate Information (identified by operation id):
—————————————————
2 – filter(“E”.”SAL”>300)
3 – filter(“D”.”DNAME”=’ACCOUNTING’ OR “D”.”DNAME”=’SALES’)
4 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=0) “E”.”ENAME”[VARCHAR2,10], “E”.”SAL”[NUMBER,22]
2 – “E”.”ENAME”[VARCHAR2,10], “E”.”SAL”[NUMBER,22], “E”.”DEPTNO”[NUMBER,22]
4 – “D”.ROWID[ROWID,10]
33 rows selected.
Elapsed: 00:00:00.05
在定义了qb_name之后最大的好处就是再设定其他hint时，可以指定qb_name，
语法相对更清晰一些，在非常复杂的select语句时不易加错hint。
43@dyl@TEST>EXPLAIN PLAN SET statement_id = ‘qb_hints’
FOR
SELECT /*+ QB_NAME(outer) FULL(@dept_subquery d) */
e.ename
, e.sal
FROM (
SELECT /*+ QB_NAME(inline_view) */
*
FROM emp e
WHERE e.sal > 300
AND e.deptno IN ( SELECT /*+ QB_NAME(dept_subquery) */ d.deptno
FROM dept d
WHERE d.dname IN (‘SALES’,'ACCOUNTING’) )
) e;
Explained.
Elapsed: 00:00:00.01
43@dyl@TEST>SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,'qb_hints’,'ALL’));
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 615168685
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 9 | 234 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 9 | 234 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 2 | 26 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
—————————————————————————
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$B9DAFA34
2 – SEL$B9DAFA34 / D@DEPT_SUBQUERY
3 – SEL$B9DAFA34 / E@INLINE_VIEW
Predicate Information (identified by operation id):
—————————————————
1 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
2 – filter(“D”.”DNAME”=’ACCOUNTING’ OR “D”.”DNAME”=’SALES’)
3 – filter(“E”.”SAL”>300)
Column Projection Information (identified by operation id):
———————————————————–
1 – (#keys=1) “E”.”ENAME”[VARCHAR2,10], “E”.”SAL”[NUMBER,22]
2 – “D”.”DEPTNO”[NUMBER,22]
3 – “E”.”ENAME”[VARCHAR2,10], “E”.”SAL”[NUMBER,22],
“E”.”DEPTNO”[NUMBER,22]
32 rows selected.