Oracle性能优化（一）理解SQL的执行方式，优化可执行的SQL

原创于 2019-03-07 15:05:31 发布 · 360 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

本文总结了提升Oracle数据库SQL查询性能的26项实用技巧，包括理解表访问方式、优化SQL语句、合理使用索引、减少数据库访问次数、利用内部函数等，旨在帮助开发者编写更高效的SQL语句。

此帖参考网上多种性能优化的资料，分析，整理，去重，纠错，加上自己的理解和认知汇总而成。转载请写明出处，谢谢

1. 理解访问Table的方式

全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
通过ROWID访问表
你可以采用基于ROWID的访问方式情况,提高访问表的效率，ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

2. Oracle优化器
CBO, Cost-BasedOptimizer

3. 共享SQL语句
ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system globalarea)的共享池(shared bufferpool)中的内存可以被所有的数据库用户共享。

4. 表名顺序
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理，如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。

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

6. SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 ‘’ 是一个方便的方法.不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将’’依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

7. 减少访问数据库的次数
能合并一条指令的尽量合并成一条（一次查询将相关的数据写入到临时表中，后续的查询从临时表中取出相应的数据）

8. 使用DECODE函数来减少处理时间
低效
SQL> SELECT COUNT(*), SUM(SAL) FROM EMP WHERE DEPT_NO=‘0020’ AND ENAME LIKE ‘SMITH%’;
SQL> SELECT COUNT(*), SUM(SAL) FROM EMP WHERE DEPT_NO=‘0030’ AND ENAME LIKE ‘SMITH%’;
高效
SQL> 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 NAME LIKE ‘SMITH%’;

9. 整合简单，无关联的数据库访问
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)。

10. 删除重复记录
使用ORACLE的ROWID删除重复记录
SQL> DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO=E.EMP_NO;

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

12. 尽量多使用COMMIT
在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为
COMMIT所释放的资源而减少：

回滚段上用于恢复数据的信息；
被程序语句获得的锁；
redo log buffer中的空间；
ORACLE为管理上述3种资源中的内部花费。

13. 计算记录条数
COUNT(*)比COUNT(1)稍快，对索引列的计数是最快的。同时需要注意的是，如果列上有NULL值，ORACLE不会计入COUNT中。

14. 用Where子句替换HAVING子句
低效
SQL> SELECT REGION, AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY’ AND REGION != ‘PERTH’;
高效
SQL> SELECT REGION, AVG(LOG_SIZE) FROM LOCATION WHERE REGION !=‘SYDNEY’ AND REGION !=PERTH’ GROUP BY REGION;

15. 减少对表的查询
低效
SQL> SELECT TAB_NAME FROM TABLES WHERE TAB_NAME=(SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION=604) AND DB_VER=(SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION=604);
高效
SQL> SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME, DB_VER)=(SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION=604);
低效
SQL> UPDATE EMP SET EMP_CAT=(SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE=(SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT=‘0020’;
高效
SQL> UPDATE EMP SET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY), MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT=‘0020’;

16. 通过内部函数提高SQL效率
复杂的SQL往往牺牲了执行效率.，能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。

17. 使用表的别名(Alias)
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

18. 用EXISTS替代IN
低效
SQL> SELECT * FROM EMP WHERE EMPNO>0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC=‘MELB’);
高效
SQL> SELECT * FROM EMP WHERE EMPNO>0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO AND LOC=‘MELB’);

19. 用NOT EXISTS替代NOT IN
NOT IN是强列不推荐使用的，因为它不能应用表的索引。
低效
SQL> SELECT * FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=‘A’);
高效
SQL> SELECT * FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO=E.DEPT_NO AND DEPT_CAT=‘A’);

20. 用表连接替换EXISTS
低效
SQL> SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO=E.DEPT_NO AND DEPT_CAT=‘A’);
高效
SQL> SELECT ENAME FROM DEPT D, EMP E WHERE E.DEPT_NO=D.DEPT_NO AND DEPT_CAT=‘A’;

21. 用EXISTS替换DISTINCT
低效
SQL> SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO=E.DEPT_NO;
高效
SQL> SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO= D.DEPT_NO);

22. 用UNION-ALL替换UNION
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，样排序就不是必要了.效率就会因此得到提高。
低效
SQL> SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’;
高效
SQL> SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’;

需要注意的是，UNION ALL将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用UNION ALL的可行性.。UNION将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也是相当重要的。下面的SQL可以用来查询排序的消耗量。
SQL> SELECT SUBSTR(NAME,1,25) “SORT AREA NAME”, SUBSTR(VALUE,1,15) “VALUE” FROM V$SYSSTAT WHERE NAME LIKE 'SORT%'

23. 用WHERE替代ORDER BY
ORDER BY子句只在两种严格的条件下使用索引。
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。
ORDER BY中所有的列必须定义为非空。
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。

24. 用< >代替IS NULL或IS NOT NULL
判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。
用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’'等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

25. LIKE操作符的正确使用
LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。
一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

26. SQL系统函数的优化
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)，优化处理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
ss_df+20>50，优化处理：ss_df>30
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
qc_bh || kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’