首页 > 数据库 >7.Oracle里的常见的执行计划

7.Oracle里的常见的执行计划

时间：2023-04-03 15:48:35浏览次数：41  
标签：02 00 04 常见 employee scott 2023 Oracle 执行

索引唯一扫描：index unique scan

scott@ORCLPDB01 2023-04-02 22:44:32> create table employee(gender varchar2(1),employee_id number);

Table created.

Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 22:45:05> insert into employee values('F','99');

1 row created.

Elapsed: 00:00:00.03
scott@ORCLPDB01 2023-04-02 22:46:56> insert into employee values('F','100');

1 row created.

Elapsed: 00:00:00.10
scott@ORCLPDB01 2023-04-02 22:47:02> insert into employee values('M','101');

1 row created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:09> insert into employee values('M','102');

1 row created.

Elapsed: 00:00:00.01
scott@ORCLPDB01 2023-04-02 22:47:12> insert into employee values('M','103');

1 row created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:15> insert into employee values('M','104');

1 row created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:17> insert into employee values('M','105');

1 row created.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:22> commit;

Commit complete.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:47:25> create unique index idx_uni_emp on employee(employee_id);

Index created.

Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 22:50:13> select * from employee where employee_id = 100;

G EMPLOYEE_ID
- -----------
F	  100

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-02 22:50:40> set autot trace;
scott@ORCLPDB01 2023-04-02 22:50:52> select * from employee where employee_id = 100;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1887894887

-------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		  |	1 |    15 |	1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE	  |	1 |    15 |	1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | IDX_UNI_EMP |	1 |	  |	0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  2  consistent gets
	  0  physical reads
	  0  redo size
	491  bytes sent via SQL*Net to client
	397  bytes received via SQL*Net from client
	  1  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

索引范围扫描：index range scan

scott@ORCLPDB01 2023-04-02 22:53:11> select * from employee where employee_id = 100;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3410127368

---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    15 |	2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE	  |	1 |    15 |	2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_UNI_EMP |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  9  recursive calls
	  0  db block gets
	 13  consistent gets
	  0  physical reads
	  0  redo size
	635  bytes sent via SQL*Net to client
	412  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

索引全扫描：index full scan

scott@ORCLPDB01 2023-04-02 22:53:13> truncate table employee;

Table truncated.

Elapsed: 00:00:00.04
scott@ORCLPDB01 2023-04-02 22:54:00> begin
  2  for i in 1..5000 loop
  3  insert into employee values('F',i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:54:42> begin
  2  for i in 5001..10000 loop
  3  insert into employee values('M',i);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
scott@ORCLPDB01 2023-04-02 22:55:45> select gender,count(*) from employee group by gender;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1624656943

-------------------------------------------------------------------------------
| Id  | Operation	   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	      | 10000 | 20000 |     8  (13)| 00:00:01 |
|   1 |  HASH GROUP BY	   |	      | 10000 | 20000 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE | 10000 | 20000 |     7	(0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
	 48  consistent gets
	  0  physical reads
	  0  redo size
	684  bytes sent via SQL*Net to client
	418  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  2  rows processed

scott@ORCLPDB01 2023-04-02 22:56:20> set autot off;
scott@ORCLPDB01 2023-04-02 22:56:30> select gender,count(*) from employee group by gender;

G   COUNT(*)
- ----------
M	5000
F	5000

Elapsed: 00:00:00.00
 22:58:37> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',estimate_percent=>100,cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
scott@ORCLPDB01 2023-04-02 22:58:48> set autot trace
scott@ORCLPDB01 2023-04-02 22:59:02> select employee_id from employee;

10000 rows selected.

Elapsed: 00:00:00.03

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 |
------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	689  consistent gets
	  0  physical reads
	  0  redo size
     179660  bytes sent via SQL*Net to client
       7724  bytes received via SQL*Net from client
	668  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      10000  rows processed

scott@ORCLPDB01 2023-04-02 22:59:19> select /*+ index(employee idx_uni_emp) */ employee_id from employee;

10000 rows selected.

Elapsed: 00:00:00.04

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 |
------------------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   1 -	SEL$1 / EMPLOYEE@SEL$1
	 U -  index(employee idx_uni_emp)


Statistics
----------------------------------------------------------
	 43  recursive calls
	  0  db block gets
	745  consistent gets
	  0  physical reads
	  0  redo size
     179660  bytes sent via SQL*Net to client
       7976  bytes received via SQL*Net from client
	668  SQL*Net roundtrips to/from client
	  5  sorts (memory)
	  0  sorts (disk)
      10000  rows processed

索引快速全扫描：index fast full scan

scott@ORCLPDB01 2023-04-02 23:01:08> alter table employee modify(employee_id not null);

Table altered.

Elapsed: 00:00:00.05
scott@ORCLPDB01 2023-04-02 23:03:53> select employee_id from employee;

10000 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 114952492

------------------------------------------------------------------------------------
| Id  | Operation	     | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		   | 10000 | 40000 |	 7   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX_UNI_EMP | 10000 | 40000 |	 7   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  8  recursive calls
	  0  db block gets
	709  consistent gets
	  0  physical reads
	  0  redo size
     179660  bytes sent via SQL*Net to client
       7724  bytes received via SQL*Net from client
	668  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
      10000  rows processed

scott@ORCLPDB01 2023-04-02 23:04:07> select /*+ index(employee idx_uni_emp) */ employee_id from employee;

10000 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3257444309

--------------------------------------------------------------------------------
| Id  | Operation	 | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	       | 10000 | 40000 |    20	 (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_UNI_EMP | 10000 | 40000 |    20	 (0)| 00:00:01 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	685  consistent gets
	  0  physical reads
	  0  redo size
     179660  bytes sent via SQL*Net to client
       7755  bytes received via SQL*Net from client
	668  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      10000  rows processed

索引跳跃式扫描：index skip scan

scott@ORCLPDB01 2023-04-02 23:05:32> create index idx_emp on employee(gender,employee_id);

Index created.

Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-02 23:06:15> select * from employee where employee_id = 101;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1432429342

----------------------------------------------------------------------------
| Id  | Operation	 | Name    | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	   |	 1 |	 6 |	 3   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_EMP |	 1 |	 6 |	 3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEE_ID"=101)
       filter("EMPLOYEE_ID"=101)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  6  consistent gets
	 13  physical reads
	  0  redo size
	632  bytes sent via SQL*Net to client
	412  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

标签：02,00,04,常见,employee,scott,2023,Oracle,执行
From： https://www.cnblogs.com/yuanzijian/p/17283216.html

相关文章

  • ORACLE PL/SQL 程序包的创建与应用
    原文地址：https://www.cnblogs.com/huyong/archive/2011/05/26/2057973.html本篇主要内容如下：第七章  程序包的创建和应用7.1  程序包简介7.2  程序包的定义7.3  包的开发步骤7.4  包定义的说明7.5  子程序重载7.6  加密实用程序7.7  删除包7.8  包的......
  • ORACLE之PACKAGE-包、存储过程、函数
    原文地址：https://www.cnblogs.com/hoaprox/p/5316444.html1，简单的包。创建包规范：createorreplacepackagepack_test1is--定义过程1procedurep_test1(p_1invarchar2);--定义函数1functionf_test1(p_1invarchar2)returnvarchar2;endpack_test1;......
  • win10计划任务程序库实现定时任务的自动执行程序及问题解决。
    win10计划任务程序库可以实现按照规则频率执行脚本的功能。现在将设置方法记录如下：创建任务步骤1、右键点击我的电脑，选择管理，依次点击：系统工具-》任务计划程序-》任务计划程序库。 2、点击最右侧操作中的创建基本任务，打开下面的弹窗。 3、创建任务的基本信息，下一步选择......
  • 常见的几种排序
    1.冒泡排序$tarr=[4,2,3,1,5,0];functionsort_arr($arr){for($i=0;$i<count($arr);$i++){for($j=$i+1;$j<count($arr);$j++){if($arr[$i]>$arr[$j]){$temp=$arr[$i];$arr[$i]=$arr[$......
  • 【测试】主流数据库存储过程编写样例（Oracle、MySQL、SQL Server）
    这个...做测试其实有时候还是需要先弄点数据才好针对某些功能进行测试的（相信做过开发的都应该深有体会）。一般像我这种老油条都推荐使用存储过程来做的初始化数据，一来脚本不会骗人，二来可以通过另一种方式验证逻辑关系。下面将整理了三个主流数据库（Oracle、MySQL和SQLServer）的“单表......
  • Linux|--sh脚本|--文件执行报错::Error response from daemon: invalid reference for
    前言由于Linux中的docker镜像和容器需要批量处理,所以搞一个脚本,直接一下处理了,1.我在Windows10的本机电脑上新建了一个"test.sh"文件2.将"test.sh"文件上传到Linux环境中3.在Linux中执行"shtest.sh"4.报错了...第一次报错信息[root@VM-4-3-centostest_api]#shdock......
  • jquery加载页面的方法(页面加载完成就执行)
    jquery加载页面的方法(页面加载完成就执行) 转自 http://www.jb51.net/article/27444.htm jquery加载页面的方法(页面加载完成就执行),建议大家看下windows.onload与$(document).ready之间的区别。 1、$(function(){ $("#a").click(function(){ //addin......
  • 20.常见的安全设备
    常见的安全设备一、网络安全设备​ 网络安全设备是一个有软件和硬件设备组合而成、在内部网和外部网之间、专用网与公共网之间的界面上构造的保护屏障，针对不同的应用场景有不同的作用，常见的安全设备有防火墙，态势感知，IDS，IPS，全流量分析，漏洞扫描，蜜罐，安全邮件，EDR等等。二、态势......
  • ubuntu下配置supervisor 运行golang打包后的可执行文件
    aptupdateaptinstallsupervisorvim/etc/supervisor/supervisord.conf添加如下配置：command配置成可执行文件的路径，directory为文件所在目录[program:tempupService]command=/data/goServicedirectory=/dataautostart=trueautorestart=truestartsecs=10stdout_lo......
  • 5.函数6.数组7.操作符8.常见关键字9.#define定义的常量和宏
    在我们学习的数学里面，函数的概念例子比如f(x)=2*x+1;  f(x,y)=x+y;在c语言也是同样的样子比如，我举例一条要相加的例子#definr_#include<stdio.h>intAdd(intx,inty)//int是他的返回类型是个整形，所以要加int//这就是一个函数add是自己创建的一个函数名，括号里面叫做函数的......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99