阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle里常见的执行计划

204次阅读
没有评论

共计 12539 个字符，预计需要花费 32 分钟才能阅读完成。

本文介绍了 Oracle 数据库里常见的执行计划，使用的 Oracle 数据库版本为 11.2.0.1。

1、与表访问相关的执行计划

Oracle数据库里与表访问有关的两种方法：全表扫描和 ROWID 扫描。反映在执行计划上，与全表扫描对应的执行计划中的关键字是“TABLE ACCESS FULL”，与 ROWID 扫描对应的执行计划中关键字是“TABLE ACCESS BY USER ROWID”或“TABLE ACCESS BY INDEX ROWID”。

scott@MYDB>select empno,ename,rowid from emp where ename='SCOTT';
 
     EMPNO ENAME                          ROWID
---------- ------------------------------ ------------------
      7788 SCOTT                          AAAR3xAAEAAAACXAAH

Oracle 里常见的执行计划

scott@MYDB>select empno,ename,rowid from emp where rowid='AAAR3xAAEAAAACXAAH';
 
     EMPNO ENAME                          ROWID
---------- ------------------------------ ------------------
      7788 SCOTT                          AAAR3xAAEAAAACXAAH

Oracle 里常见的执行计划

scott@MYDB>select empno,ename,rowid from emp where empno=7788;
 
     EMPNO ENAME                          ROWID
---------- ------------------------------ ------------------
      7788 SCOTT                          AAAR3xAAEAAAACXAAH

Oracle 里常见的执行计划

从实验中可以看出，第一个 SQL 执行计划走的是对表 EMP 的全表扫描，对应的关键字就是“TABLE ACCESS FULL”；第二个 SQL 的执行计划走的是对表 EMP 的ROWID扫描，对应的关键字是“TABLE ACCESS BY USER ROWID”；第三个 SQL 的执行计划走的是对表 EMP 的ROWID扫描，对应的关键字是“TABLE ACCESS BY INDEX ROWID”。注意如果 ROWID 来源于用户手工指定则对应的是“TABLE ACCESS BY USER ROWID”；如果 ROWID 是来源于索引，则对应的是“TABLE ACCESS BY INDEX ROWID”。

2 与 B*Tree 索引相关的执行计划

Oracle数据库里常见的与 B*Tree 索引访问相关的方法，包括索引唯一扫描、索引范围扫描、索引全扫描、索引快速全扫描和索引跳跃式扫描，反映在执行计划上分别对应 INDEX UNIQUE SCAN、INDEX RANGE SCAN、INDEX FULL SCAN、INDEX FAST FULL SCAN 和INDEX SKIP SCAN。

用实验查看相关执行计划

zx@MYDB>create table employee (gender varchar2(1),employee_id number);
 
Table created.
 
zx@MYDB>insert into employee values('F',99);
 
1 row created.
 
zx@MYDB>insert into employee values('F',100);
 
1 row created.
 
zx@MYDB>insert into employee values('M',101);
 
1 row created.
 
zx@MYDB>insert into employee values('M',102);
 
1 row created.
 
zx@MYDB>insert into employee values('M',103);
 
1 row created.
 
zx@MYDB>insert into employee values('M',104);
 
1 row created.
 
zx@MYDB>insert into employee values('M',105);
 
1 row created.
 
zx@MYDB>create unique index idx_uni_emp on employee(employee_id);
 
Index created.
 
zx@MYDB>select * from employee where employee_id=100;
 
GEN EMPLOYEE_ID
--- -----------
F           100

Oracle 里常见的执行计划

第一个 SQL 的执行计划走的是对索引 IDX_UNI_EMP 的索引唯一扫描，关键字是“INDEX UNIQUE SCAN”。

zx@MYDB>drop index idx_uni_emp;
 
Index dropped.
 
zx@MYDB>create index idx_emp_1 on employee(employee_id);
 
Index created.
 
zx@MYDB>select * from employee where employee_id=100;
 
GEN EMPLOYEE_ID
--- -----------
F           100

Oracle 里常见的执行计划

现在 SQL 的执行计划是对索引 IDX_EMP_1 的索引范围扫描，关键字是“INDEX RANGE SCAN”。

zx@MYDB>truncate table employee;
 
Table truncated.
 
zx@MYDB>begin
  2  for i in 1..10000 loop
  3  insert into employee select decode(mod(i,2),0,'M','F'),i from dual;
  4  end loop;
  5  end;
  6  /
 
 
PL/SQL procedure successfully completed.
 
zx@MYDB>zx@MYDB>commit;
 
Commit complete.
 
zx@MYDB>select gender,count(*) from employee group by gender;
 
GEN   COUNT(*)
--- ----------
M         5000
F         5000
 
zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,no_invalidate=>false,method_opt=>'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
zx@MYDB>set autotrace traceonly
zx@MYDB>select employee_id from employee;
 
10000 rows selected.
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 10000 | 40000 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------
... 省略部分输出

明明可以扫描索引 IDX_EMP_1 得到结果，却选择了全表扫描，就算使用 Hint 强制让 Oracle 扫描索引 IDX_EMP_1，结果却是Hint 失效了。

zx@MYDB>select /* +index(employee idx_emp_1) */employee_id from employee;
 
10000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2119105728
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 10000 | 40000 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------
... 省略部分输出

出现这个现象的原因是 Oracle 无论如何总会保证目标 SQL 结果的正确性，可能会得到错误结果的执行路径 Oracle 是不会考虑的。对于索引 IDX_EMP_1 而言，它是一个单键值的 B*Tree 索引，所以 NULL 值不会存储在其中，那么一量 EMPLOYEE_ID 出现了 NULL 值(虽然这里实际上并没有 NULL 值)，则扫描索引的结果就是漏掉那些 EMPLOYEE_ID 为NULL值的记录，这也就意味着如果 Oracle 在执行上述 SQL 时选择了扫描 IDX_EMP_1，那么执行结果就有可能是不准的。在这种情况下，Oracle 当然不会考虑扫描索引，即使我们使用了Hint。

如果想让 Oracle 在执行上述 SQL 时扫描索引 IDX_EMP_1，则必须将列EMPLOYEE_ID 的属性修改为 NOT NULL。这就相当于告诉Oracle，这里列EMPLOYEE_ID 上不会有 NULL 值，你就放心地扫描索引 IDX_EMP_1 吧。

zx@MYDB>alter table employee modify employee_id not null;
 
Table altered.
 
zx@MYDB>select employee_id from employee;
 
10000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3918702848
 
----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           | 10000 | 40000 |     7   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX_EMP_1 | 10000 | 40000 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------------
... 省略部分输出

从上面的输出可以看出，现在 SQL 的执行计划走的是对索引 IDX_EMP_1 的索引快速全扫描，对应的是“INDEX FAST FULL SCAN”。

现在加上强制走索引 IDX_EMP_1 的Hint，再次执行该SQL

zx@MYDB>select /*+index(employee idx_emp_1) */employee_id from employee;
 
10000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 438557521
 
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           | 10000 | 40000 |    20   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 |    20   (0)| 00:00:01 |
------------------------------------------------------------------------------
... 省略部分输出

可以看到 现在 SQL 的执行计划走的是对索引 IDX_EMP_1 的索引快速全扫描INDEX FULL SCAN(如果是在 11.2.0.4 版本上执行上以 SQL 可以以看到还是INDEX FAST FULL SCAN)

zx@MYDB>drop index idx_emp_1;
 
Index dropped.
 
zx@MYDB>create index idx_emp_2 on employee(gender,employee_id);
 
Index created.
 
zx@MYDB>select * from employee where employee_id=101;

Oracle 里常见的执行计划

从上面输出可以看出，SQL的执行计划走的是对索引 IDX_EMP_2 的索引跳跃式扫描，对应“INDEXSKIP SCAN”。

3、与表连接相关的执行计划

Oracle数据库里常见的与表连接相关的一些方法：排序合并连接、嵌套循环连接、哈希连接等以及反连接和半连接

zx@MYDB>create table t1(col1 number,col2 varchar2(1));
 
Table created.
 
zx@MYDB>create table t2(col2 varchar2(1),col3 varchar2(2));
 
Table created.
 
zx@MYDB>insert into t1 values(1,'A');
 
1 row created.
 
zx@MYDB>insert into t1 values(2,'B');
 
1 row created.
 
zx@MYDB>insert into t1 values(3,'C');
 
1 row created.
 
zx@MYDB>insert into t1 values(4,'D');
 
1 row created.
 
zx@MYDB>insert into t1 values(5,'E');
 
1 row created.
 
zx@MYDB>insert into t2 values('A','A2');
 
1 row created.
 
zx@MYDB>insert into t2 values('B','B2');
 
1 row created.
 
zx@MYDB>insert into t2 values('D','D2');
 
1 row created.
 
zx@MYDB>insert into t2 values('E','E2');
 
1 row created.
 
zx@MYDB>
zx@MYDB>commit;
 
Commit complete.
 
zx@MYDB>select * from t1;
 
      COL1 COL
---------- ---
         1 A
         2 B
         3 C
         4 D
         5 E
 
zx@MYDB>select * from t2;
 
COL COL3
--- ------
A   A2
B   B2
D   D2
E   E2
 
zx@MYDB>select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;
 
      COL1 COL COL3
---------- --- ------
         1 A   A2
         2 B   B2
         4 D   D2
         5 E   E2

Oracle 里常见的执行计划

从上面的输出可以看出，SQL的执行计划走的是对表 T1 和T2的哈希连接，连接条件是t1.col2=t2.col2，对应的关键字是“HASH JOIN”。

使用强制走排序合并连接的 Hint 后再次执行SQL

zx@MYDB>select /*+use_merge(t1,t2) */t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;
 
      COL1 COL COL3
---------- --- ------
         1 A   A2
         2 B   B2
         4 D   D2
         5 E   E2

Oracle 里常见的执行计划

从上面的输出可以看出现在 SQL 的执行计划走的是对表 T1 和T2的排序合并连接，对应的关键字是“MERGEJOIN”和“SORT JOIN”。

接着使用强制走嵌套循环连接的 Hint 后再次执行SQL

zx@MYDB>select /*+use_nl(t1,t2) */t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;
 
      COL1 COL COL3
---------- --- ------
         1 A   A2
         2 B   B2
         4 D   D2
         5 E   E2

Oracle 里常见的执行计划

从上面的输出可以看出现在 SQL 的执行计划走的是对表 T1 和T2的嵌套循环连接，对应的关键字是“NESTEDLOOPS”

嵌套循环连接的驱动表是可以变的，我们使用 Hint 将上述 SQL 的驱动表改为 T1 再将执行SQL

zx@MYDB>select /*+ ordered use_nl(t1,t2) */t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;
 
      COL1 COL COL3
---------- --- ------
         1 A   A2
         2 B   B2
         4 D   D2
         5 E   E2

Oracle 里常见的执行计划

从结果中可以看到，嵌套循环连接的驱动表确实已经变为T1

再看反连接的例子。首先将表 T1 和T2的连接列 col2 改为NOT NULL，以便能走出我们想要的反连接的执行计划

zx@MYDB>alter table t1 modify col2 not null;
 
Table altered.
 
zx@MYDB>alter table t2 modify col2 not null;
 
Table altered.
 
zx@MYDB>select * from t1 where col2 not in (select col2 from t2 where col3='A2');
 
      COL1 COL
---------- ---
         5 E
         4 D
         2 B
         3 C

Oracle 里常见的执行计划

从输出内容上可以看出，SQL的执行计划走的是对表 T1 和T2的哈希反连接，反连接在执行计划中对应的关键字是“ANTI”，哈希反连接对应的就是“HASH JOIN ANTI”。

反连接的具体连接方法是可变的，这里使用 Hint 将SQL的反连接改为排序合并反连接

zx@MYDB>select * from t1 where col2 not in (select /*+ MERGE_AJ */ col2 from t2 where col3='A2');
 
      COL1 COL
---------- ---
         2 B
         3 C
         4 D
         5 E

Oracle 里常见的执行计划

从输出内容可以看出，SQL的执行计划走的是对表 T1 和T2的排序合并反连接，对应的关键字是“MERGE JOIN ANTI”。

再使用 Hint 将SQL的反连接方法改为嵌套循环反连接

zx@MYDB>select * from t1 where col2 not in (select /*+ NL_AJ */ col2 from t2 where col3='A2');
 
      COL1 COL
---------- ---
         2 B
         3 C
         4 D
         5 E

Oracle 里常见的执行计划

再看半连接的例子。

zx@MYDB>insert into t2 values('E','E3');
 
1 row created.
 
zx@MYDB>commit;
 
Commit complete.
 
zx@MYDB>select * from t1 where exists(select * from t2 where t1.col2=t2.col2 and col3>'D2');
 
      COL1 COL
---------- ---
         5 E

Oracle 里常见的执行计划

从输出可以看出，SQL的执行计划走的是对表 T1 和T2的哈希半连接，半连接在执行计划中对应的关键字是“SEMI”，哈希半连接在执行计划中对应的关键字是“HASH JOIN SEMI”。

半连接的具体连接方法是可变的，使用 Hint 将SQL的半连接方法改为排序合并半连接：

zx@MYDB>select * from t1 where exists(select /*+ MERGE_SJ */* from t2 where t1.col2=t2.col2 and col3>'D2');
 
      COL1 COL
---------- ---
         5 E

Oracle 里常见的执行计划

从输出内容可以看出，SQL的执行计划走的是对表 T1 和T2的排序合并半连接，对应的关键字是“MERGE JOIN SEMI”。

再使用 Hint 把SQL的半连接方法改为嵌套循环半连接：

 

zx@MYDB>select * from t1 where exists(select /*+ NL_SJ */* from t2 where t1.col2=t2.col2 and col3>'D2');
 
      COL1 COL
---------- ---
         5 E

 

Oracle 里常见的执行计划

从输出内容可以看出，SQL的执行计划走的是对表 T1 和T2的嵌套循环半连接，对应的关键字是“NESTED LOOPS SEMI”

4、关于位图索引相关的执行计划

Oracle数据库里常见的与位图索引访问相关的方法包括如下这些类型：位图索引单键值扫描、位图索引范围扫描、位图索引全扫描、位图索引快速全扫描、位图按位与、位图按位或、位图按位减等。

Oracle在使用完位图索引后通常会将最后的位图运算结果转化为ROWID，这一步转换过程对应的执行计划中的“BITMAP CONVERSION TO ROWIDS”。

zx@MYDB>create table customer
  2  (
  3  customer# number,
  4  marital_status varchar2(10),
  5  region varchar2(10),
  6  gender varchar2(10),
  7  income_level varchar2(10)
  8  );
 
Table created.
 
zx@MYDB>insert into customer values(101,'single','east','male','bracket_1');
 
1 row created.
 
zx@MYDB>insert into customer values(102,'married','central','female','bracket_4');
 
1 row created.
 
zx@MYDB>insert into customer values(103,'married','west','female','bracket_2');
 
1 row created.
 
zx@MYDB>insert into customer values(104,'divorced','west','male','bracket_4');
 
1 row created.
 
zx@MYDB>insert into customer values(105,'single','central','female','bracket_2');
 
1 row created.
 
zx@MYDB>insert into customer values(106,'married','central','female','bracket_3');
 
1 row created.
 
zx@MYDB>commit;
 
Commit complete.
 
zx@MYDB>create bitmap index idx_b_region on customer(region);
 
Index created.
 
zx@MYDB>create bitmap index idx_b_maritalstatus on customer(marital_status);
 
Index created.
 
zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'CUSTOMER',estimate_percent=>100,cascade=>true);
 
PL/SQL procedure successfully completed.
 
zx@MYDB>select /*+ index(customer idx_b_region) */ customer# from customer where region='east';
 
 CUSTOMER#
----------
       101

Oracle 里常见的执行计划从上面的输出内容可以看出，SQL的执行计划走的是对位图索引 IDX_B_REGION 的位图索引单键值扫描，对就的关键字是“BITMAP INDEX SINGLE VALUE”。

把 SQL 改写为范围查询后再次执行

zx@MYDB>select /*+index(customer idx_b_region) */ customer# from customer where region between 'east' and 'west';
 
 CUSTOMER#
----------
       101
       103
       104

Oracle 里常见的执行计划从输出内容可以看出 SQL 走的执行计划是对位图索引 IDX_B_REGION 的位图索引范围扫描，对应的关键字是“BITMAP INDEX RANGE SCAN”。

去掉 where 条件，并且只查询位图索引 IDX_B_REGION 的索引键值列：

zx@MYDB>select region from customer;
 
REGION
------------------------------
central
central
central
east
west
west

Oracle 里常见的执行计划从输出可以看出 SQL 走的执行计划是对位图索引 IDX_B_REGION 的位图索引快速全扫描，对应的关键字是“BIT INDEX FAST FULL SCAN”。

执行如下SQL：

zx@MYDB>select count(*) from customer where marital_status='married' and region in ('central','west');
 
  COUNT(*)
----------
         3

Oracle 里常见的执行计划

从输出内容可以看出 SQL 走的执行计划中，用到了位图按位与操作，对应的关键字是“BITMAP AND”和位图按位或操作，对应的关键字是“BITMAP OR”。

再构造位图按位减的执行计划，SQL如下：

zx@MYDB>select /*+index(customer idx_b_maritalstatus) index(customer idx_b_region) */ customer# from customer where marital_status='married' and region!='central';
 
 CUSTOMER#
----------
       103

Oracle 里常见的执行计划从输出的执行计划中，位图按位减的执行计划对应的关键字是“BITMAP MINUX”。 

参考《基于 Oracle 的 SQL 优化》pdf 下载见 http://www.linuxidc.com/Linux/2017-02/140521.htm

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-02/141090.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL NoSQL Oracle Oracle执行计划 Oracle教程 Oracle数据库 Oracle里常见的执行计划 PostgreSQL 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计12539字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
MySQL 8.0.4 RC:使用 auth_socket用户要小心！
MySQL反连接的优化总结
CentOS 7.4下MySQL 5.7.20主从环境搭建（M-S）
MySQL插入数据成功但是报[Err] 1055
MySQL单表查询
关于 Redis慢日志
innodb_flush_method 深入理解
MySQL5.7的组提交与并行复制
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3592627
评论数
4
文章搜索
热门文章
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

实验环境：CentOS Linux release 7.3.1611 (Core) 内核版本：Linux ve...
介绍ansible的Ad-hoc与commands模块

介绍ansible的Ad-hoc与commands模块

Ad-Hoc 是指 ansible 下临时执行的一条命令，并且不需要保存的命令，对于复杂的命令后面会说 pla...
Linux安装使用pidstat命令以对进程数据进行监控

Linux安装使用pidstat命令以对进程数据进行监控

导读 Linux 系统监控的命令其实有很多，pidstat 命令就是其中的一种，使用 pidstat 命令可以...
linux下使用tree命令以树形结构显示文件目录结构

linux下使用tree命令以树形结构显示文件目录结构

导读 今天小编来给分享 Linux 系统下一个非常有用的命令的使用：tree 命令可以以树形结构显示文件目录结...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
了解ansible架构与工作原理

了解ansible架构与工作原理

Ansible 是一个模型驱动的配置管理器，支持多节点发布、远程任务执行。默认使用 SSH 进行远程连接。无需...
如何在linux中查看cpu信息、机器硬件型号

如何在linux中查看cpu信息、机器硬件型号

# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c...
云计算岗位40个面试题

云计算岗位40个面试题

1）使用云计算有哪些优点？ 使用云计算有下列优点： a）备份数据和存储数据 b）强大的服务器功能 c）SaaS...
第一节：Bash编程易犯的错误

第一节：Bash编程易犯的错误

前一段时间发现一个很好的 wiki 站点，上面有很多优秀的 Bash 文章。最近挑了一篇介绍 Bash 编程容...
在Linux系统下制作U盘启动盘

在Linux系统下制作U盘启动盘

导读 我的当前的系统是 Linux(版本 Xubuntu)，之前是通过软通牒 (UltraISO) 在 Win...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中