Oracle面试稳了！这6大性能优化高频题，技术官最爱问

前言

年尾之际，新的一波面试大军又要开启了，被问到频次最高的或许就是性能优化，特别是数据库性能优化，这个面试题目不管是初级、中级还是高级工程师都会被问及。所以我认为下面31点ORACLE优化还是值得挑选几个用于面对面试。也为日后平常开发时进行数据库性能优化当作字典来查阅。这是一篇关于ORACLE的优化，Mysql的优化请留意我，在之前的文章当中有过介绍。

Oracle数据库的排名依然稳健第一。

以下将列出31条Oracle的优化习惯

1、SQL语句尽量用大写的；

之所以是这样，是由于oracle总是会先去解析SQL语句，接着会把小写的字母转换成为大写再来执行。

2、使用表的别名：

倘若在SQL语句里连接多个表之时，尽可能运用表的别名并且把别名置于每个列的前面。如此这般，便能够缩减解析的时间并且降低那些因列歧义引发的语法错误。

3、挑选出最具效率的表名顺序，该顺序唯独在基于规则的优化器，也就是RBO当中才会生效，：

ORACLE的解析器处理FROM子句中的表名时，顺序是从右到左，FROM子句里写在最后的表，也就是基础表，也叫驱动表，会最先被处理，在有多个表包含在FROM子句的情形下，必须挑选记录条数最少的表当作基础表，要是存在3个以上的表进行连接查询，那就得选择交叉表当作基础表，交叉表指的是那个被其他表引用的表。

4、WHERE子句中的连接顺序：

数据库管理系统ORACLE会以这种方式解析WHERE子句，即采用自下而上的顺序来解析 ，依据这样的原理 ，表之间的连接需要写在其他WHERE条件之前 ，而那些能够过滤掉最大数量记录的条件必须写在WHERE子句的末尾 。

5、SELECT子句中避免使用 * ：

在ORACLE开展解析的进程当中，会把'*'逐个转变为全部的列名，此项工作借助用于查询的数据字典来达成，这表明将会消耗更多的时长。

6、减少访问数据库的次数：

ORACLE于内部开展多样事项了，有解析SQL语句之举，有估算索引利用率之行，有绑定变量之为，还有读数据块之措等 。

7、整合简单、无关联的数据库访问：

假设有几个简单的数据库查询语句，它们之间不存在关联关系，却能够被整合归纳到一个查询里面 。

8、于SQL*Plus之中，在SQL*Forms当中，在Pro*C里面，重新去设置ARRAYSIZE参数，这般能够增加每一回数据库访问的时候的检索数据量，其建议值是200 。

9、删除重复记录：

最高效的删除重复记录方法 ：

从TEMP E中删除，条件是，E的ROWID大于，从TEMP1 X中查询最小ROWID所构成的子查询结果，这个子查询中，X的条件是TEMP_NO等于E的TEMP_NO 。

10、减少对表的查询：

在含有子查询的SQL语句中,要特别注意减少对表的查询。

从名为 TABLES 的表之中挑选出 TAB_NAME 这一项，条件是，存在这样的情况，即，(TAB_NAME,DB_VER) 等于，从 TAB_COLUMNS 这个表里面，通过 VERSION 等于 604 这个条件筛选出来的，那一组数据中的 TAB_NAME,DB_VER 所构成的组合 。

11、避免使用耗费资源的操作:

包含DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句，会开启SQL引擎，去执行耗费资源的排序，也就是SORT功能。DISTINCT所要进行的是一次排序操作，然而其他的那些，至少得执行两次排序。

通常，带有UNION的SQL语句，带有MINUS的SQL语句，带有INTERSECT的SQL语句，都能够以其他方式进行重写 。

倘若数据库之中的 SORT_AREA_SIZE 进行了妥善的调配，那么运用 UNION，对 MINUS 而言，INTERSECT 也是能够予以考虑的。

12、优化GROUP BY:

倘若要提升GROUP BY语句的效率，那么能够借助于在GROUP BY之前把不需要的记录给过滤掉这种方式 。

下面两个查询返回相同结果但明显第二个效率更高。

低效:

从 USER_TAB1 表 D 中，选取不同的 USER_ID，从 USER_TAB2 表 E 中选取不同的 BILL_ID，当 D 表中的 USER_ID 与 E 表中的 USER_ID 相等时 。

高效:

从 USER_TAB1 中选取 USER_ID 以及 BILL_ID，筛选条件是年龄等于 '20' 。 （原始语句是数据库查询语句，改写不是特别契合其专业性，只能尽量按要求拆分表述）。
UNION ALL 
从 USER_TAB2 中选取 USER_ID，选取 BILL_ID，条件是 AGE 等于 '二十' 。

13、根据需要用UNION ALL替换UNION：

在SQL语句里面，当出现需要UNION两个查询结果集合的情况时，那时候，这两个结果集合将会以UNION - ALL的方式去进行合并，。

然后，进行排序，在输出最终结果之前。要是用UNION ALL替代UNION，如此一来，这个排序便无需进行了。

这样一来，效率便会由此而获得提升。切要留意，UNION ALL可是会把两个结果集合里相同的记录重复输出的哟。所以呢。

从业务需求出发，考量使用 UNION ALL 的可行性，UNION 会对结果集合作排序，此项操作会动用 SORT_AREA_SIZE 这块内存，针对这块内存的优化，其重要性也颇高。

低效:

SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E WHERE D.USER_ID= E.USER_ID;

高效:

SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20' 
UNION ALL 
SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

14、用EXISTS替换DISTINCT：

当SQL存在一对多表的查询情况时，要防止在SELECT子句中运用DISTINCT，通常使用EXIST去进行替换，EXISTS。

因为RDBMS核心模块会在子查询条件一旦被满足之后，马上返回结果，所以查询更为迅速。

低效:

SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E WHERE D.USER_ID= E.USER_ID;

高效:

从 USER_TAB1 表中选择 WHERE 条件为存在 (从 USER_TAB2 表中选择 1 WHERE 条件为 USER_TAB2 表中的 USER_ID 等于 USER_TAB1 表对应行的 USER_ID) 的行作为结果 返回 USER_ID 和 BILL_ID 列 。

15、尽量多使用COMMIT：

但凡存在可能，于程序里尽可能多地运用COMMIT，如此这般程序的性能即可获得提升，需求也会鉴于COMMIT所释放的资源而有所减少。

COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息。

b. 被程序语句获得的锁。

c. redo log buffer 中的空间。

d. ORACLE为管理上述3种资源中的内部花销。

16、用Where子句替换HAVING子句：

防止运用HAVING子句，HAVING仅仅会在将所有记录都检索出来以后才针对结果集开展过滤，此处理需要。

进行排序操作，还要进行总计等相关操作，要依靠WHERE子句来对记录的数目加以限制，最好能够如此 。

不在oracle中的情况下，on、where、having这三个都能够添加条件的子句里面，on是最先被执行的，where是排在其次执行的，having是最后的那个被执行的。

a，关于on，在进行统计之前，是会先将那些不符合条件的记录给予滤除，借此，从中进行统计时，能够把中间运算所要处理的数据予以减少，如此一来，其速度在所有当中是最为快速的，是最快的 。

b、where比having快些，是由于它滤除数据后才实施sum，此操作是在两个表关联时通过on来进行的所在表为一个时，便只剩where与having作比较了单表查询统计情形下，若需过滤的条件未涉及要计算的字段，那么它们的结果相同，只是where能够运用rushmore技术，而having不行，故而在速度方面后者较慢了？要是涉及计算字段，那就意味着在未计算之时，这个字段的值是没法确定的，where的作用时间是在计算之前便已完成的，然而having却是在计算之后才开始起作用的，所以在这种情形下，两者的结果会不一样。在多表联接查询的时候，on比where更早发挥作用。系统先是依据各个表之间的关联条件，把多个表组合成一个临时表之后，再经由where进行过滤，接着再开展计算，计算完毕后再由having进行过滤。

17、用TRUNCATE替代DELETE：

只要进行表中记录的删除操作，在一般情形下，回滚段也就是rollback segments是用来放置能够被恢复的数据信息的。要是你没执行COMMIT事务，ORACLE就会把数据还原到没有进行删除操作的时候的状态，确切来讲是恢复到执行删除指令以前的状况。然而一旦使用TRUNCATE，回滚段就不再存储任何能够被恢复的数据信息了。当命令运行完毕之后，数据是无法被恢复的。所以被调用的资源非常少，执行所需的时间也会很短。

注意，TRUNCATE仅仅在用于删除整个表的时候才适用，TRUNCATE属于DDL，而不是DML 。

18、使用DECODE函数来减少处理时间：

借助DECODE函数，能够防止重复去扫描相同的记录，或者避免重复连接相同的表。

这是一个关于：将 EXISTS 用于替换其中的 IN ，并且将 NOT EXISTS 用于替换 NOT IN 的相关内容 。

查询基于基础表时，常常需连接另一个表，这种情形下，用EXISTS(或NOTEXISTS)通常能提升查询效率，子查询里，NOT IN子句会执行内部排序与合并，无论何种状况，NOT IN都是最低效的(要对表执行全表遍历)，所以尽量把NOT IN改写成外连接(Outer Joins)或NOT EXISTS。

低效:

从那被称作TEMP（基础表）的表中选取A的所有内容，条件是A的年龄大于0并且A的ID在此所列，此列是从TEMP1表中选取，选取条件是其中NAME等于'TOM'的ID形成所得。

高效:

从TEMP（基础表）之中选取A的所有内容，其中A满足年龄大于0这个条件，并且存在这样一种情况，即从TEMP1里面选取1，是因为在TEMP1中A的ID等于ID且名字等于'TOM' 。

20、用索引提高效率：

索引，属于表的一个概念部分，其作用在于提高检索数据的效率，ORACLE运用了复杂的自平衡B - tree结构，。

构造，平常借由索引去查询数据相较于全表扫描而言会更快。当ORACLE寻觅到执行查询以及Update语句的最佳路径之际，ORACLE优化器会运用索引。同样地，在联结多个表的时候运用索引也能够提升效率。

还有一个好处是使用索引，它能提供主键也就是primary key的唯一性验证。对于那些LONG或LONG RAW数据类型而言，几乎所有的列都是可以索引的。一般在大型表当中使用索引是特别有效的。在扫描小表的时候，使用索引此情形下同样能够提高效率。使用索引尽管能够让查询效率得到提高，然而也必须要注意到它所涉及的代价。索引是需要空间来进行存储的，而且也需要定期去维护，每当在表中有记录进行增减或者索引列被修改的时候，索引本身也是会被修改的。INSERT，DELETE，UPDATE，针对每条记录而言，会因此额外产生4、5次的磁盘 I/O。索引，因其需要额外的存储空间以及处理过程，一些并非必要的索引，反而会致使查询反应时间变得更为缓慢 。

定期的重构索引:

ALTER INDEX  REBUILD 

21、用>=替代>：

区别在于，前者的DBMS会直接跳到首个ID等于4的记录，而后者，会先定位到ID等于3的记录，并且，向前扫描，直至找到第一个ID大于3的记录 。

低效:

从 USER_TAB1 中选取所有内容，条件是 USER_ID 等于 10 ，。
UNION 
在USER_TAB1当中进行全选，条件是年龄等于20 。

高效:

从TEMP中选取所有内容，当ID大于或等于4时，进行以上选取操作。

22、避免在索引列上使用NOT：

NOT所引发的效应，与在索引列之上运用函数而成的状况不尽一致。一旦ORACLE遭遇NOT，便会终止索引的运用，进而转向推行全表扫描。

23、避免在索引列上使用计算：

假如在WHERE子句当中，要是索引列属于函数的一部分，那么优化器会不采用索引而是运用全表扫描 。

低效:

请明确下改写要求，比如将其中的英文单词替换成相近意思的中文表述等，这样我才能更准确地按照要求改写。仅这 “SELECT … FROM (TEMP) WHERE (SAL。

高效:

做出选择，从临时表中选取，当薪资大于，两万五除以十二的时候标点符号。

24、用UNION替换OR(适用于索引列):

通过UNION替换WHERE子句里的OR，这会带来较好的效果。对索引列运用OR，会致使全表扫描。留意以上规则仅针对多个索引列是有效的。要是有column未被索引涵盖时，没选择OR的话，查询效率或许会降低。

低效:

从USER_TAB1中选取所有内容，条件是USER_ID等于10，或者AGE等于20 。

高效:

SELECT * FROM USER_TAB1 WHERE USER_ID = 10 
UNION 
SELECT * FROM USER_TAB1 WHERE AGE = 20;

25、要防止在索引列那儿使用IS NULL以及IS NOT NULL 。

要不就在索引里摒弃任何能够为空掉的列，不然的话，ORACLE就难以运用那个索引了。

对于单列索引，如果列包含空值，索引中将不存在此记录。

拿复合索引来说，要是每个列都是空的情况，那么索引里相同的也不会有这条记录。要是最少有一个列并非是空的，那记录就在索引里存在着。

要是唯一性索引构建于表的A列以及B列之上，而且表里头存有一条记录，其A、B值是(123,null)，那么ORACLE就不会接纳下一条有着相同A、B值（123,null）的记录（进行插入操作）。然而要是所有的索引列均为空，ORACLE会认定整个键值为空，可空并不等于空。所以你能够插入10000条拥有相同键值的记录，当然这些记录都是空的！由于空值并不存在于索引列当中，所以在WHERE子句里针对索引列进行空 。

低效: (索引失效)

选择……从DEPARTMENT当中，在DEPT_CODE不为NULL的情况下进行挑选，。

高效: (索引有效)

SELECT … FROM TEMP WHERE SAL > 25000/12;

26、总是使用索引的第一个列：

要是索引是于多个列之上构建的，那么只有当它的首个列，即在where子句里被引用的那个首个列，优化器才会去选择运用该索引。

当引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

27、用WHERE替代ORDER BY：

ORDER BY 子句只在以下两种严格的条件下使用索引：

ORDER BY里的所有列，都得包含于相同索引内，且要维持在索引里的排列顺序。

(2)ORDER BY中所有的列必须定义为非空。

用于WHERE子句的索引，与用于ORDER BY子句的索引，二者不能处于并列状态，举例来说：

 USER_ID PK NOT NULL 
 USER_DESC NOT NULL 
 USER_TYPE NULL 
低效: (索引不被使用) 
从用户表中选取用户标识，按照用户类型进行排序，以这样一种方式，即从用户表当中，挑选涉及用户标识的信息。 ，， 依照用户类型的顺序来排定 。 ，，。
高效: (使用索引) 

28、避免改变索引列的类型:

进行不同数据类型的数据比较时，ORACLE会自动针对列施行简单的类型转换。

假设 USER_ID 是一个数值类型的索引列。

实际上,经过ORACLE类型转换, 语句转化为:

首先进行“SELECT … FROM USER_TAB”操作，接着在“WHERE USER_ID = TO_NUMBER(' 。 123 '，这里的单引号变为了中文引号，然后执行等于操作，这里的等于操作变为了中文的等于，最后执行“);”操作，这里的分号变为了。

幸运的是，类型转而并未处在被应用于索引的那一列为上，索引所具备的用途并未出现被予以改变的情况，。

现在,假设USER_TYPE是一个字符类型的索引列。

选取出自用户表的数据，在用户类型等于123的条件下进行筛选！

这个语句被ORACLE转换为:

SELECT … FROM USER_TAB WHERE TO_NUMBER(USER_TYPE)=123;

这个索引不会被用到，是由于内部所发生的那种类型转换，而为了防止ORACLE针对你所写的SQL去进行隐式的类型转换，则最好将类型转换以显式的方式呈现出来。

请注意，在字符与数值进行比较这个情况的时候，ORACLE会优先去做将数值类型转换为字符类型这件事情。

SELECT … FROM USER_TAB WHERE TO_NUMBER(USER_TYPE)=123;

29、WHERE子句:

某些SELECT 语句中的WHERE子句不使用索引。

“!=”不会走索引，索引能够告诉我们的，是表中存在着什么，而无法告知我们表中不存在什么。

(2)'||'成为字符连接函数，如同其他函数那般，表示停用索引。

(3)，这个‘+’，它属于数学函数之中，如同跟其他那些数学函数的情况一样，索引被停用哟。

(4)相同的索引列不能互相比较,这将会启用全表扫描。

30、使用索引

a.要是检索的数据量超出了占比30%的那个表之中所包含的记录数量，运用索引的话，并不会存在明显的效率提升情况。

B，把它放在特定的情形之下，运用索引这种方式，有可能反而会比进行全表扫描的速度更慢，不过呢，这所体现出的差别的确是处在同一个数量级范围之内的 。

而通常情况下,使用索引比全表扫描要块几倍乃至几千倍。

31、查'低效执行'的SQL语句：

把（缓冲区获取量减去磁盘读取量）除以缓冲区获取量的结果，进行四舍五入保留两位小数，得到命中率，记作HIT_RADIO,。
ROUND函数，其参数为DISK_READS除以EXECUTIONS的结果，保留两位小数，得到Reads_per_run，同时包含SQL_TEXT 。
 FROM V$SQLAREA 
存在这样的情况，执行次数大于零，并且缓冲区获取次数大于零 。
并且，（缓冲区获取量减去磁盘读取量）除以缓冲区获取量 。< 0.8 
 ORDER BY 4 DESC;