左右SQL执行计划妙招 ①——子查询的应用范围

已于 2025-05-20 12:28:37 修改
阅读量483 收藏 20
点赞数 23
分类专栏： 数据库拍案惊奇 文章标签： sql 数据库 执行计划 数据库优化 SQL优化 数据库设计 数据库开发
于 2025-05-20 09:49:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39534833/article/details/148079406
版权

梁敬彬梁敬弘兄弟出品

摘要

笔者将教会大家如何真正读懂执行计划，这个其实并不容易。在优化的大方向基本上都清晰后，剩下的就是具体的优化实施：

  1. 修改数据库及主机相关性能参数；
  2. 根据业务规则修改SQL代码；
  3. 重新收集统计信息获取更准确的执行计划等。

OK，让我们开始吧，先看看总体学习思路，如下图所示：

在这里插入图片描述

关键词

执行计划 HINT 子查询 SQL

1 控制执行计划的方法综述

在这里插入图片描述

1.1 控制执行计划的意义

前面已经讲过了，这里简单总结为两点：

  1. 可以临时在高峰期解决问题，避免因收集统计信息带来的开销；
  2. 有BUG导致执行计划一直不对，只好用人工控制来处理。

1.2 控制执行计划的思路

一般来说，环境的影响会改变SQL的执行计划，除此之外就是Hint 会强制让Oracle根据你的要求走对应的执行计划。
Hint的种类有多种，如下表所示：

在这里插入图片描述

此外写法差异也会带来执行计划的改变，比如with子句改造、分析函数改造、rownum的位置，等等。
还有一些设计的特性带来的执行计划的改变，比如普通表成为分区表就意味着执行计划从全表扫描要转换为分区扫描了。
这些写法改变和设计改造改变执行计划的例子很多，在本章的案例部分将会详细解说。

2.1 HINT的思路

1. 子查询应用范围

简单的SQL语句只有一个单独的查询块。当使用视图或类似子查询、内联视图、集合操作符等结构时，就会出现多个查询块（比如这个例子的查询就有两个查询块，第一个是引用了dept表的主查询，第二个是引用了emp表的子查询）。

之前我们总结了hint的分类，除了第一类初始化参数hint外，所有其他的hint都是仅针对单个查询块起作用。下面来看如何让各个模块的HINT生效的各种方法。
环境准备：


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的有效区域都被严格控制在它们所在的查询块内，脚本“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）用qb_name定义方式

有的时候SQL不写子查询的别名，比如WHERE条件中的子查询显然用不到别名，这时可以用qb_name定义方式，其中，qb_name(main)是固定必须写的，比如如下的full(@main dept)就是来引用主表的。
脚本“全局的hint的别名引用”代码和运行结果，如下：

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)

结论与最佳实践

通过以上案例，我们可以看到在复杂SQL语句中控制执行计划的三种主要方法：

  • 在各个查询块内单独添加HINT
  • 使用别名在主查询中引用子查询表
  • 使用qb_name显式定义查询块

每种方法各有适用场景：

  • 简单独立的优化可使用第一种方法
  • 需要统一控制多个查询块时，第二种方法更为简洁
  • 面对没有别名的子查询时，第三种方法是唯一选择

在实际应用中，应根据SQL结构特点和优化需求，灵活选择合适的HINT应用方法。
在这里插入图片描述

未完待续…
左右SQL执行计划妙招 ②——Hint无效原因

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列

Oracle SQL执行计划（6）——获取、阅读及分析方法（2）
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
11-10 787
本文结合实例对Oracle中SQL语句执行计划的所有获取方法进行了详尽讲解，同时，简明扼要的讲解了Oracle中SQL语句执行计划的阅读分析方法和宗旨。
SQL优化——执行计划
码说芯语的博客
04-19 5497
获取执行计划的常用方法、定制执行计划、通过查看执行计划建立索引、通过光标移动阅读执行计划
来自灵魂的拷问——知道什么是SQL执行计划吗？
牧小农
08-06 2671
面试官说：工作这么久了，应该知道sql执行计划吧，讲讲Sql的执行计划吧！ 看了看面试官手臂上纹的大花臂和一串看不懂的韩文，吞了吞口水，暗示自己镇定点，整理了一下思绪缓缓的对面试官说：我不会 面试官：。。。。，回去等通知吧 我：%^&%$!@# 一、前言 当我们工作到了一定的年限之后，一些应该掌握的知识点，我们是必须需要去了解的，比如今天面试官问的SQL执行计划 当我们执行一条SQL的时候，可以直接对应的结果，但是你并不晓得，它会经历多深远黑暗的隧道，通过连接器、查询缓存、分析器、优化器、执行器重.
SQL系列五——子查询（SELECT）和组合查询（UNION）
热门推荐
xiao1_1bing的博客
07-19 6万+
1、子查询： 为什么要子查询？ 现有一数据表如下： 根据之前的知识我们可以查出每门科目的最高分，但是要想查出取得最高分的学生信息就做不到了。这时就需要用到子查询来取得完整的信息。 什么是子查询？子查询就是嵌套在主查询中的查询。 子查询可以嵌套在主查询中所有位置，包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。 但并不是每个位置嵌套子查询都是...
SQL入门之第十八讲——相关子查询
henry_rhy的博客
05-14 5469
在相关子查询中，子查询是在主查询每一条记录层面上一次进行的，子查询依赖主查询。 相关子查询比非关联查询执行起来要慢一点，但是这种有更多的实际应用。 实例1：选出成绩表中，学生编号在1-6之间的记录，找出那些超过相同科目平均成绩的学生成绩。 select * from scores as s -- 将主查询的字段加上别名 where score > ( select avg(score) from scores where cid = s.cid --获取当前主查询的课程编号.
面试笔记——MySQL（优化篇：定位慢查询、SQL执行计划、索引、SQL优化）
Z__XY_的博客
03-21 1568
因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大。是指一个索引包含了查询语句所需的所有列，从而使得查询可以直接从索引中获取所需的数据，而无需回表查询。覆盖索引通常用于查询语句的列与索引列完全匹配的情况。最左前缀法则（或最左前缀匹配）指的是当一个查询包含了多列的复合索引时，查询条件查询从索引的最左前列开始，并且不跳过索引中的列。
T-SQL执行内幕（1）——简介
MVP黄钊吉（發糞塗牆）
03-23 2731
本文属于SQL Server T-SQL执行内幕系列前言：    本文主体内容来自于：http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/但是经常打不开，本人又在：https://www.codeproject.com/Articles/630346/Understanding-how-SQL-Ser...
MySQL高级篇——关联查询和子查询优化
种一棵树最好的时间是十年前，其次是现在
05-22 3721
左外连接：优先右表创建索引，连接字段类型要一致、内连接：驱动表由数据量和索引决定、 join语句原理、子查询优化：拆开查询或优化成连接查询
SQL查询——子查询（SELECT）和组合查询（UNION）
CCblibo的博客
12-01 3725
1、子查询： 为什么要子查询？ 现有一数据表如下： 根据之前的知识我们可以查出每门科目的最高分，但是要想查出取得最高分的学生信息就做不到了。这时就需要用到子查询来取得完整的信息。 什么是子查询？子查询就是嵌套在主查询中的查询。 子查询可以嵌套在主查询中所有位置，包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。 但并不是每个位置嵌套子查询都是有意义并实用的...
sqlserver 执行计划
weixin_41048746的博客
05-31 4633
一个很好的手册分享，执行计划里的属性解释官方文档：https://docs.microsoft.com/zh-cn/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017 想复杂的事情简单说，在看执行计划的其他文章的时候，发现直接上很复杂的DDL脚本来讲解，这样...
数据库连接池调优——可视化查询慢SQL利器
Cool
11-25 2583
Druid 是阿里巴巴开源平台上的一个数据库连接池实现，它为开发人员提供了高效、稳定且功能丰富的数据库连接管理方案。除了基础的连接池功能外，Druid 还具备强大的监控和扩展能力，能够帮助开发人员和运维人员深入了解数据库的使用情况，及时发现并解决潜在的性能问题，尤其是在定位和分析慢 SQL 语句方面表现突出。Druid 作为一款优秀的数据库连接池，凭借其高效的连接池管理、强大的监控功能以及在可视化查询慢 SQL 等方面的出色表现，为开发和运维人员在数据库性能优化工作中提供了有力的工具支持。
使用Microsoft SQL Server 2000全文搜索功能——构建Web搜索应用程序.pdf
09-19
Microsoft SQL Server 2000的全文搜索功能正是在这种需求下应运而生，它允许用户对非结构化文本数据执行复杂且高效的查询。全文检索技术能够通过索引来提升搜索的速度和精度。 全文搜索的核心是Microsoft Search...
官方 Elasticsearch SQL 和 NLPChina Elasticsearch SQL 的区别
jifgnie的博客
05-16 464
官方的可以在kibana 控制台上进行查询：POST /_sql。
数据库第二次作业--SQL的单表查询与多表查询
2302_80908433的博客
05-19 216
【代码】数据库第二次作业--SQL的单表查询与多表查询。
SAP BC 表结构查询
最新发布
GAVIN
05-20 227
该ABAP程序ZRPT_BC_TABFLDS用于从SAP数据字典中提取表及其字段的详细信息，并在ALV网格中显示。程序首先定义选择屏幕，允许用户输入表名、字段名、关键字等筛选条件。通过GET_DATA子程序，程序从DD02L、DD03L、DD04L等数据字典表中获取表结构、字段属性及描述信息，并将结果存储在内部表GT_DATA中。随后，BUILD_FIELDCAT子程序构建ALV字段目录，DISPLAY_DATA子程序调用REUSE_ALV_GRID_DISPLAY_LVC函数在ALV网格中显示数据。程序还
SQLMesh 增量模型从入门到精通：5步实现高效数据处理
neweastsun的专栏
05-16 943
本文深入解析 SQLMesh 中的增量时间范围模型，介绍其核心原理、配置方法及高级特性。通过实际案例说明如何利用该模型提升数据加载效率，降低计算资源消耗，并提供配置示例与最佳实践建议，帮助读者在实际项目中有效应用这一强大功能。
PostgreSQL pgrowlocks 扩展详解
king_harry的专栏
05-15 965
pgrowlocks 是 PostgreSQL 官方提供的扩展模块，用于查看指定表中每一行当前的。加锁事务对应的后端进程 PID 数组（可结合 pg_stat_activity 使用）在template1模版数据库中安装扩展，随后新建数据库自带pgrowlocks。锁模式（如 'For Update'、'For Share' 等）持锁事务的Transaction ID（主事务）多事务下所有持锁事务的事务 ID 数组。是否是多事务锁（即多事务持有此行锁）支持显示多事务共持一行锁的情况。
SQL里where条件的顺序影响索引使用吗？
java1234的博客
05-16 639
SQL里where条件的顺序影响索引使用吗？
PL/SQL 安装配置与使用
是萝卜干呀的博客
05-20 674
PL/SQL 的安装配置、基础设置和使用；包和包体、存储过程的使用；存储过程的断点调试
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

收获不止数据库

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值