Oracle之hint研究查询生效范围

/*
  简单的SQL语句只有一个单独的查询块。当使用视图或类似子查询、内联视图、集合操作符等结构时，就会
  出现多个查询块（比如本次的例子的查询就有两个查询块，第一个是引用了dept表的主查询，第二个是引用了emp表的子查询）。
  之前我们总结了hint的分类，除了第一类初始化参数hint外，所有其他的hint都是仅针对单个查询块起作用。
  本例就是描述如何让各个模块的HINT生效的各种方法。

*/

hints：

1.full() :/*+full(emp)*/

2./*+full(dept) full(emps.emp)*/ emps是with as的别名

3./*+qb_name(sq)*/ 调用：/*+ full(@sq emp)*/ 

drop table emp purge;
create table emp as select * from scott.emp;
create index idx_emp_deptno on emp(deptno);
create index idx_emp_empno on emp(empno);
drop table dept purge;
create table dept as select * from scott.dept;
create index idx_dept_deptno on dept(deptno);




set linesize 1000
set pagesize 2000
set autotrace traceonly


with emps as (select deptno,count(*) as cnt  from emp
               where empno in (7369,7782,7499)group by deptno)
select dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno; 
执行计划
---------------------------------------------------------------------------------------------------
Plan hash value: 174555140
----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |     3 |   144 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                    |                 |       |       |            |          |
|   2 |   NESTED LOOPS                   |                 |     3 |   144 |     4  (25)| 00:00:01 |
|   3 |    VIEW                          |                 |     3 |    78 |     3  (34)| 00:00:01 |
|   4 |     HASH GROUP BY                |                 |     3 |    78 |     3  (34)| 00:00:01 |
|   5 |      INLIST ITERATOR             |                 |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| EMP             |     3 |    78 |     2   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | IDX_EMP_EMPNO   |     1 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN              | IDX_DEPT_DEPTNO |     1 |       |     0   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID    | DEPT            |     1 |    22 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7782)
   8 - access("DEPT"."DEPTNO"="EMPS"."DEPTNO")


 
---写法1 （两个hint的有效区域都被严格控制在他们所在的查询块内）
 
with emps as (select /*+full(emp)*/ deptno,count(*) as cnt 
               from emp where empno in (7369,7782,7499)
              group by deptno)
select /*+full(dept)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;
执行计划
----------------------------------------------------------------------------
Plan hash value: 2415981340
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   144 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     3 |   144 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    78 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP  |     3 |    78 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | DEPT |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMPS"."DEPTNO")
   4 - filter("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7782)


---写法2（子查询模块可以不用写，用上了全局的hint,可以用子查询的别名加上点这个符号来引用）
with emps as (select  deptno,count(*) as cnt 
               from emp
               where empno in (7369,7782,7499)
              group by deptno)
select /*+full(dept) full(emps.emp)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;
 
 -----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   144 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     3 |   144 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    78 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP  |     3 |    78 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | DEPT |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMPS"."DEPTNO")
   4 - filter("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7782)
   
   
   
--写法3
/*
 有的时候SQL就写不出子查询的别名，比如WHERE条件中的子查询显然用不到别名，这时可以用qb_name定义的方式,
 其中qb_name(main)是固定必须写的，比如如下的full(@main dept)就是引用主表的。
*/
例子：
with emps as (select  /*+qb_name(sq)*/ deptno,count(*) as cnt 
               from emp
               where empno in (7369,7782,7499)
              group by deptno)
select /*+ full(@sq emp)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;
 例子：
with emps as (select  /*+qb_name(sq)*/ deptno,count(*) as cnt 
               from emp
               where empno in (7369,7782,7499)
              group by deptno)
select /*+qb_name(main) full(@main dept) full(@sq emp)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;
 
 执行计划
----------------------------------------------------------
Plan hash value: 2415981340
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     3 |   144 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     3 |   144 |     8  (25)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    78 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| EMP  |     3 |    78 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | DEPT |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPT"."DEPTNO"="EMPS"."DEPTNO")
   4 - filter("EMPNO"=7369 OR "EMPNO"=7499 OR "EMPNO"=7782)