Oracle - 如何提高SQL的查询效率（where条件优化）

在Oracle数据库中编写查询语句的时候，经常碰到的一个问题就是SQL优化，那么今天就总结一下Where之后，如何进行条件的优化

优化规则：

一、选择合适的基础表

Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
二、WHERE子句中的连接顺序

Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

（3）SELECT子句中避免使用‘*’

Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（4）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数

可以增加每次数据库访问的检索数据量 ,建议值为200。

（5）使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 

等多DECODE函数的用法，参考以下博客：

https://blog.csdn.net/qq_38941937/article/details/81625691

例如：

SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE　‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE　‘SMITH%’;
　你可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
　类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

（6）用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。（TRUNCATE只在删除全表适 用,TRUNCATE是DDL不是DML）。
 

（7）用EXISTS替代IN、用NOT EXISTS替代NOT IN

 

（8）识别‘低效执行’的SQL语句

（9）增加索引

（10）用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换， EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

例子：

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO

(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
 

（11）SQL语句用大写的

因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。 

（12）在Java代码中尽量少用连接符“＋”连接字符串

（13）避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描。

（14）避免在索引列上使用计算
 

（15）用UNION替换OR (适用于索引列)

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有 column没有被索引，查询效率可能会因为你没有选择OR而降低。

（16）用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在Oracle8i下，两者的执行路径似乎是相同的:　

低效:   SELECT… FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30；

高效： SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

（17）避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，Oracle将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为 空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。

举例：如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B 值为(123，null)， Oracle将不接受下一条具有相同A，B值（123,null）的记录(插入)。 然而如果所有的索引列都为空，Oracle将认为整个键值为空而空不等于空。因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

低效: (索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 

高效：(索引有效) 

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0; 
（18）避免改变索引列的类型

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

（19）需要当心的WHERE子句

某些SELECT 语句中的WHERE子句不使用索引。这里有一些例子：

‘!=' 将不使用索引。记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。
‘||'是字符连接函数。就象其他函数那样, 停用了索引。 
‘+'是数学函数。就象其他数学函数那样, 停用了索引。 
相同的索引列不能互相比较,这将会启用全表扫描。 

总结

以上优化，包括不限于Where条件语句优化，以及在where时索引如何使用，哪些失效以及避免产生的问题的总结。不仅仅适用于Oracle，本文仅根据Oracle为栗子。

 

IT操作手册】带你玩转JAVA开发，每天推送问题解决方案

 

一个朋友新做的公众号，帮忙宣传一下，会不定时推送一些开发中碰到的问题的解决方法，以及会分享一些开发视频。资料等。请大家关注一下谢谢：