首页 > 其他分享 >1.基于规则的优化器

1.基于规则的优化器

时间:2023-04-01 14:35:19浏览次数:40  
标签:基于 temp 00 EMPNO mgr emp 规则 优化 Plan

1.创建测试环境

--建表
create table emp_temp as select * from emp;
create index idx_mgr_temp on emp_temp(mgr);
create index idx_deptno_temp on emp_temp(deptno);
--查询数据库
select * 
from emp_temp
where mgr > 100 and deptno > 10;

     EMPNO ENAME      JOB	       MGR HIREDATE		      SAL	COMM	 DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17 00:00:00	      800		     20
      7566 JONES      MANAGER	      7839 1981-04-02 00:00:00	     2975		     20
      7788 SCOTT      ANALYST	      7566 1987-04-19 00:00:00	     3000		     20
      7876 ADAMS      CLERK	      7788 1987-05-23 00:00:00	     1100		     20
      7902 FORD       ANALYST	      7566 1981-12-03 00:00:00	     3000		     20
      7499 ALLEN      SALESMAN	      7698 1981-02-20 00:00:00	     1600	 300	     30
      7521 WARD       SALESMAN	      7698 1981-02-22 00:00:00	     1250	 500	     30
      7654 MARTIN     SALESMAN	      7698 1981-09-28 00:00:00	     1250	1400	     30
      7698 BLAKE      MANAGER	      7839 1981-05-01 00:00:00	     2850		     30
      7844 TURNER     SALESMAN	      7698 1981-09-08 00:00:00	     1500	   0	     30
      7900 JAMES      CLERK	      7698 1981-12-03 00:00:00	      950		     30

11 rows selected.

2.测试RBO的执行计划

2.1.查看执行计划,执行SQL1:

select * from emp_temp  where mgr > 100 and deptno > 10;
scott@ORCLPDB01 2023-04-01 13:54:48> alter session set optimizer_mode = 'RULE';

Session altered.

Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-04-01 13:54:53> set autot trace exp
scott@ORCLPDB01 2023-04-01 13:54:56> select * from emp_temp  where mgr > 100 and deptno > 10;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1670750536

-------------------------------------------------------
| Id  | Operation		    | Name	      |
-------------------------------------------------------
|   0 | SELECT STATEMENT	    |		      |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP	      |
|*  2 |   INDEX RANGE SCAN	    | IDX_DEPTNO_TEMP |
-------------------------------------------------------

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

   1 - filter("MGR">100)
   2 - access("DEPTNO">10)

Note
-----
   - rule based optimizer used (consider using cbo)

2.2.通过等价改写SQL(加0或者空字符串的方式)修改执行计,划执行SQL2:

select * from emp_temp  where mgr > 100 and deptno + 0 > 10;
scott@ORCLPDB01 2023-04-01 13:55:12> select * from emp_temp  where mgr > 100 and deptno + 0 > 10;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2973289657

----------------------------------------------------
| Id  | Operation		    | Name	   |
----------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP	   |
|*  2 |   INDEX RANGE SCAN	    | IDX_MGR_TEMP |
----------------------------------------------------

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

   1 - filter("DEPTNO"+0>10)
   2 - access("MGR">100)

Note
-----
   - rule based optimizer used (consider using cbo)

 2.3.通过修改索引建立的顺序,调整执行计划(删除现有的索引,再新建索引,即是调整索引在数据字典中的顺序)

scott@ORCLPDB01 2023-04-01 13:59:44> drop index IDX_MGR_TEMP;

Index dropped.

Elapsed: 00:00:00.13
scott@ORCLPDB01 2023-04-01 14:05:45> create index idx_mgr_temp on emp_temp(mgr);

Index created.

Elapsed: 00:00:00.02
scott@ORCLPDB01 2023-04-01 14:06:02> select * from emp_temp  where mgr > 100 and deptno > 10;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2973289657

----------------------------------------------------
| Id  | Operation		    | Name	   |
----------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP	   |
|*  2 |   INDEX RANGE SCAN	    | IDX_MGR_TEMP |
----------------------------------------------------

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

   1 - filter("DEPTNO">10)
   2 - access("MGR">100)

Note
-----
   - rule based optimizer used (consider using cbo)

 2.4.验证改变目标SQL中涉及的对象在该SQL文本中出现的先后顺序,改变执行计划,划执行SQL3和SQL4:

--SQL3
scott@ORCLPDB01 2023-04-01 14:09:47> select t1.mgr,t2.deptno
  2  from emp_temp t1, emp_temp1 t2
  3  where t1.empno = t2.empno;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1323777565

-----------------------------------------
| Id  | Operation	    | Name	|
-----------------------------------------
|   0 | SELECT STATEMENT    |		|
|   1 |  MERGE JOIN	    |		|
|   2 |   SORT JOIN	    |		|
|   3 |    TABLE ACCESS FULL| EMP_TEMP1 |
|*  4 |   SORT JOIN	    |		|
|   5 |    TABLE ACCESS FULL| EMP_TEMP	|
-----------------------------------------

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

   4 - access("T1"."EMPNO"="T2"."EMPNO")
       filter("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

--SQL4
scott@ORCLPDB01 2023-04-01 14:10:27> select t1.mgr,t2.deptno
  2  from emp_temp1 t2, emp_temp t1
  3  where t1.empno = t2.empno;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2135683657

-----------------------------------------
| Id  | Operation	    | Name	|
-----------------------------------------
|   0 | SELECT STATEMENT    |		|
|   1 |  MERGE JOIN	    |		|
|   2 |   SORT JOIN	    |		|
|   3 |    TABLE ACCESS FULL| EMP_TEMP	|
|*  4 |   SORT JOIN	    |		|
|   5 |    TABLE ACCESS FULL| EMP_TEMP1 |
-----------------------------------------

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

   4 - access("T1"."EMPNO"="T2"."EMPNO")
       filter("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

2.5.通过修改执行路径等级制的大小选择执行计划(emp表empno列存在主键)执行SQL5和SQL6

scott@ORCLPDB01 2023-04-01 14:12:29> select t1.mgr,t2.deptno
  2  from emp t1, emp_temp t2
  3  where t1.empno = t2.empno;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 367190759

-------------------------------------------------
| Id  | Operation		     | Name	|
-------------------------------------------------
|   0 | SELECT STATEMENT	     |		|
|   1 |  NESTED LOOPS		     |		|
|   2 |   NESTED LOOPS		     |		|
|   3 |    TABLE ACCESS FULL	     | EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN	     | PK_EMP	|
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP	|
-------------------------------------------------

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

   4 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

scott@ORCLPDB01 2023-04-01 14:18:34> select t1.mgr,t2.deptno
  2  from  emp_temp t2,emp t1
  3  where t1.empno = t2.empno;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 367190759

-------------------------------------------------
| Id  | Operation		     | Name	|
-------------------------------------------------
|   0 | SELECT STATEMENT	     |		|
|   1 |  NESTED LOOPS		     |		|
|   2 |   NESTED LOOPS		     |		|
|   3 |    TABLE ACCESS FULL	     | EMP_TEMP |
|*  4 |    INDEX UNIQUE SCAN	     | PK_EMP	|
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP	|
-------------------------------------------------

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

   4 - access("T1"."EMPNO"="T2"."EMPNO")

Note
-----
   - rule based optimizer used (consider using cbo)

注意:如果RBO仅凭目标SQL各执行路径等级值的大小就可以选择执行计划,无论怎么调整位置,对于该SQL的最终执行计划没有任何影响。 

标签:基于,temp,00,EMPNO,mgr,emp,规则,优化,Plan
From: https://www.cnblogs.com/yuanzijian/p/17278578.html

相关文章

  • 通过 OG 标签优化网站在社交媒体上的显示效果
    介绍OG标签是OpenGraphProtocol的缩写,是一种由Facebook所定义和推广的元数据协议,用于在社交媒体上显示网页的预览内容。OG标签允许网站管理员控制他们网站页面在Facebook、Twitter、LinkedIn等社交媒体上的展示形式,可以设置网页的标题、描述、图像、URL等元素......
  • 基于zynq的OV5640摄像头的sobel算子边缘检测
    最近鸽了挺久的,因为最近要做课设,再加上被这个工程的调试给难到了。在做该工程的时候,有一个良好的项目管理习惯会让开发的时候不会让人那么的高血压。特别要注意的是,异步FIFO的读写时钟的速率匹配问题,这个问题卡了我好久。1、sobel算子Sobel算法是像素图像边缘检测中最重要的......
  • makefile 编写规则
    (一)makefile规则 一般开头都是Tab,不能空格,include前面不能是Tab;1、如果没编译过,将所有的 (.c)文件编译并且链接;2、如果有其中的(.c)文件改变,编译并链接改变的文件;3、如果(.h)文件被修改,编译引用相应的(.c)文件,链接;4、在随意修改时间的情况下,会导致编......
  • 如何基于AI视觉识别技术实现明厨亮灶可视化监管?
    为保障食品安全,近年来各地市场监督管理局以云计算、大数据、人工智能为技术支撑,叠加视频监控技术,全力打造“互联网+明厨亮灶”智慧食安监管服务体系。实现对后厨的透明化、规范化、智能化管理。今天我们着重介绍下,如何基于AI视觉识别技术实现对餐饮服务单位可视化监管。SkeyeVSS综......
  • 基于Spring的AOP(注解方式)
    面向切面编程:基于Spring的AOP(注解方式)1-配置:pom文件:<packaging>jar</packaging><dependencies><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactI......
  • DSL5.变量定义区的权限规则
    当事人如果当事人被初始化,将定义一个初始化函数实现当事人的赋值,合约部署者可以对当事人参数进行修改如果当事人被is定义,该当事人将无法修改资产如果资产被当事人拥有,该资产如果被初始化,仅当事人可以修改该资产如果资产未被当事人拥有,该资产处于未定义状态,合约部署者......
  • DSL5.变量定义区的权限规则
    当事人如果当事人被初始化,将定义一个初始化函数实现当事人的赋值,合约部署者可以对当事人参数进行修改如果当事人被is定义,该当事人将无法修改资产如果资产被当事人拥有,该资产如果被初始化,仅当事人可以修改该资产如果资产未被当事人拥有,该资产处于未定义状态,合约部署者......
  • 基于凸集上投影(POCS)的聚类算法
    POCS:ProjectionsontoConvexSets。在数学中,凸集是指其中任意两点间的线段均在该集合内的集合。而投影则是将某个点映射到另一个空间中的某个子空间上的操作。给定一个凸集合和一个点,可以通过找到该点在该凸集合上的投影来进行操作。该投影是离该点最近的凸集内的点,可以通过最小......
  • 基于matlab的高精度信号峰值检测算法
    1.算法描述       峰值检验是示波表中数据采集方式之一,这种技术起源于存储深度不能满足捕获毛刺的需要。如果用模拟示波器去观察,只有当毛刺信号是重复性的并且和主信号同步时,才能看到毛刺信号。由于毛刺源于其他电路系统,所以这些毛刺只是偶尔发生,并且和主信号......
  • m基于强化学习的PID控制器simulink仿真,对比PI控制器和变结构PI控制器
    1.算法描述       PID控制器,即控制器的控制方式为P比例调整,I积分调整以及D微分调整三个部分构成,PID控制器是目前为止应用最为广泛的控制方式。PID控制器具有结构简单,性能稳定,参数设置简单等优势。PID控制器适用于各种控制对象无法进行测量获得系统参数的情况,其根据控制对......