文章转自 ：https://blog.csdn.net/oChangWen/article/details/52728637，

案例引用：https://blog.csdn.net/weixin_39452806/article/details/79045946??

好文要顶，感谢博主们分享！

1.什么是执行计划
? ? ? ? ? oracle数据库在执行sql语句时，oracle的优化器会根据一定的规则确定sql语句的执行路径，以确保sql语句能以最优性能执行。在oracle数据库系统中为了执行sql语句，oracle可能需要实现多个步骤，这些步骤中的每一步可能是从数据库中物理检索数据行，或者用某种方法准备数据行，让编写sql语句的用户使用，oracle用来执行语句的这些步骤的组合被称为执行计划。

当执行一个sql语句时oracle经过了4个步骤：
? ①.解析sql语句：主要在共享池中查询相同的sql语句，检查安全性和sql语法与语义。
? ②.创建执行计划及执行：包括创建sql语句的执行计划及对表数据的实际获取。
? ③.显示结果集：对字段数据执行所有必要的排序，转换和重新格式化。
? ④.转换字段数据：对已通过内置函数进行转换的字段进行重新格式化处理和转换.

2.查看执行计划
? ? ? ? ? ?查看sql语句的执行计划，比如一些第三方工具? 需要先执行utlxplan.sql脚本创建explain_plan表

在创建表后，在SQL*Plus中就可以使用set autotrace语句来显示执行计划及统计信息。常用的语句与作用如下：

比如要执行SQL且显示执行计划，可以使用如下的语句：

上面不一定可以执行成功，使用这个：explain plan for sql语句

?

?PL/SQL Developer提供了一个执行计划窗口，如果在SQL Windows的窗口，按F8是执行该sql,按F5会显示该sql的执行计划。

3.理解执行计划
? ? ? 1.全表扫描(full table scans)：这种方式会读取表中的每一条记录，顺序地读取每一个数据块直到结尾标志，对于一个大的数据表来说，使用全表扫描会降低性能，但有些时候，比如查询的结果占全表的数据量的比例比较高时，全表扫描相对于索引选择又是一种较好的办法。
? ? ? 2.通过ROWID值获取(table access by rowid)：行的rowid指出了该行所在的数据文件，数据块及行在该块中的位置，所以通过rowid来存取数据可以快速定位到目标数据上，是oracle存取单行数据的最快方法。
? ? ? 3.索引扫描(index scan)：先通过索引找到对象的rowid值，然后通过rowid值直接从表中找到具体的数据，能大大提高查找的效率。

? 默认情况下，优化器会使用all_rows优化方式，也就是基于成本的优化器CBO生成执行计划，CBO方式会根据统计信息来产生执行计划。
? 统计信息给出表的大小，多少行，每行的长度等信息，这些统计信息起初在库内是没有的，是做analyzee后才发现的，很多时候过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息。
? 在CBO模式下，当对多个表进行连接查询时，oracle分析器会按照从右到左的顺序处理from子句中的表名。例如：

? 在执行时，oracle会先查询右边dept表，根据dept表查询的行作为数据源串行连接emp表继续执行，因此dept表又称为基础表或驱动表。由于连接的顺序对于查询的效率有非常大的影响。因此在处理多表连接时，必须选择记录条数较少的表作为基础表，oracle会使用排序与合并的方式进行连接。比如先扫描dept表，然后对dept表进行排序，再扫描emp表，最后将所有检索出来的记录与第一个表中的记录进行合并。
? 如果有3个以上的表连接查询，就需要选择交叉表作为基础表。交叉表是指那个被其他表所引用的表，由于emp_log是dept与emp表中的交叉表，既包含dept的内容又包含emp的内容。

在查询表时，where子句中条件的顺序往往影响了执行的性能。默认情况下，oracle采用自下而上的顺序解析where子句，因此在处理多表查询时，表之间的连接必须写在其他的where条件之前，但是过滤数据记录的条件则必须写在where子句的尾部，以便在过滤了数据之后再进行连接处理，这样可以提升sql语句的性能。

?有时我们习惯使用 ?* ? 符号，如

? Oracle在遇到*符号时，会去查询数据字典表中获取所有的列信息，然后依次转换成所有的列名，这将耗费较长的执行时间，因此尽量避免使用*符号获取所有的列信息

?

? 是Oracle才具有的一个功能强大的函数，?比如统计emp表中部门编号为20和部门编号为30的员工的人数和薪资汇总，如果不使用decode那么就必须用两条sql语句。

? 通过Union将两条SQL语句进行合并，实际上通过执行计划可以看到，SQL优化器对emp进行了两次全表扫描
通过decode语句，可以再一个sql查询中获取到相同的结果，并且将两行结果显示为单行。

? ? 通过灵活的运用decode函数，可以得到很多意想不到的结果，比如在group by 或order by子句中使用decode函数，或者在decode块中嵌套另一个decode块。

? where子句和having子句都可以过滤数据，但是where子句不能使用聚合函数，如count 、max 、min 、avg 、sum等函数。因此通常将Having子句与Group By子句一起使用。
注意：当利用Group By进行分组时，可以没有Having子句。但Having出现时，一定会有Group By！

? ? ?需要了解的是，WHERE语句是在GROUP BY语句之前筛选出记录，而HAVING是在各种记录都筛选之后再进行过滤。也就是说HAVING子句是在从数据库中提取数据之后进行筛选的。因此在编写SQL语句时，尽量在筛选之前将数据使用WHERE子句进行过滤，因此执行的顺序应该总是这样：
? ? ? ①.使用WHERE子句查询符合条件的数据
? ? ? ②.使用GROUP BY子句对数据进行分组。
? ? ? ③.在GROUP BY分组的基础上运行聚合函数计算每一组的值
? ? ? ④.用HAVING子句去掉不符合条件的组。

例子：查询部门20和30的员工薪资总数大于1000的员工信息

在having子句中，过滤出部门编号为20或30的记录，实际上这将导致查询取出所有部门的员工记录，在进行分组计算，最后才根据分组的结果过滤出部门 20和30的记录，这非常低效。

好的算法是先使用where子句取出部门编号为20和30的记录，再进行过滤。修改如下：

? 如果要进行OR运算的两个列都是索引列，可以考虑使用union来提升性能。
? 例子：比如emp表中，empno和ename都创建了索引列，当需要在empno和ename之间进行OR操作查询时，可以考虑将这两个查询更改为union来提升性能。

使用UNION

但这种方式要确保两个列都是索引列，否则还不如OR语句。

? ? ? ? 如果坚持使用OR语句，①.需要记住尽量将返回记录最少的索引列写在最前面，这样能获得较好的性能，例如empno > 7500 返回的记录要少于对ename的查询，因此在OR语句中将其放到前面能获得较好的性能。②.对单个字段值进行OR计算的时候使用IN来代替

上面的SQL如果修改为使用in，性能更好。

? 比如查询位于 'CHICAGO' ?的所有员工列表可以考虑使用in

? ? 对于内表和外表同样数量级来说exists和in的效率差别不是很大。但对于内表特别大的sql，我们用in的效率就很底下，替换成exists可以获取更好的查询性能

? 同样的替换页发生在not in 和not exists之间，not in 子句将执行一个内部的排序和合并，实际上它对子查询中的表执行了一次全表扫描，因此效率低，在需要使用NOT IN的场合，因而总是考虑把它更改成外连接或NOT EXISTS。

为了提高较好的性能，可以使用连接查询，这是最有效率的的一种办法

也可以考虑使用NOT EXIST

PLSQL在处理逻辑表达式值的时候，使用的是短路的计算方式。

? 首先对第一个条件进行判断，如果v_sal大于5000，就不会再对v_job条件进行判断，灵活的运用这种短路计算方式可以提升性能。应该总是将开销较低的判断语句放在前面，这样当前面的判断失败时，就不会再执行后面的具有较高开销的语句，能提升PL/SQL应用程序的性能。
? 举个例子，对于and逻辑运算符来说，只有左右两边的运算为真，结果才为真。如果前面的结果第一个运算时false值，就不会进行第二个运算：

这段代码有一个性能隐患，check_sal涉及一些业务逻辑的检查，如果让check_sal函数的调用放在前面，这个函数总是被调用，因此处于性能方面的考虑，应该总是将v_job的判断放到and语句的前面。

当比较不同数据类型的数据时，?ORACLE自动对列进行简单的类型转换。例如 ：EMPNO是一个数值类型的索引列

实际上，经过ORACLE类型转换，语句转化为：

注意：当类型转换发生在索引列上时，索引的用途可能会被改变。例如：EMP_TYPE是一个字符类型的索引列

这个语句被ORACLE转换为：

因为内部发生的类型转换，?这个索引将不会被用到。为了避免ORACLE对SQL进行隐式的类型转换，最好把类型转换用显式表现出来。注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。

我们再看一个实际的案例，如下图：

我们看到SQL执行时，第二个表是全表扫描，没有走索引。查看索引字段的类型：

我们发现两个表的userid 字段类型不一样：一个 number，一个 varchar2， 发生了隐式转换。?这就是当我们sql 字段类型不一样时会使用隐式和转换。

优化方案很简单，我们把两个表的字段设置成同一个类型就好了。

设置成同样的类型后就都走索引了 ，当数据量特别大时，我们sql得优势才会显现出来了。