Oracle之hint研究查询生效范围

最新推荐文章于 2024-06-04 21:40:08 发布

原创最新推荐文章于 2024-06-04 21:40:08 发布 · 507 阅读

1 ·

CC 4.0 BY-SA版权

Oracle之hints 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何在SQL查询中使用Hint来指定查询优化器的行为。通过三种不同方式展示如何让Hint只作用于特定的查询模块，包括直接在子查询中添加Hint、使用子查询别名以及利用qb_name定义。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*
简单的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)