Oracle优化网上常见的5个错误观点
最近系统的研究了一下ORACLE SQL语句性能调优，在此大言不惭的得出一个观点——网上很多性能调优的结论都是错误的或者不周全的。
现在的DBA大牛些都太低调了，不出来斧正一下，小弟来借这个机会吐槽一下，说的不对，欢迎拍砖，特别是版本问题：
转入正题：
网络上大部分结论“可能”适用于ORACLE8或者以前版本（小弟出道晚没见过ORACLE
8），但是针对9i及以后的版本，很多结论都是欠周全的。
下面举几个最常见的问题：
错误观点1、什么时候用IN 什么时候用EXISTS？ 子查询数据量少用IN
量多用EXISTS
如果你说
半连接子查询返回数据少的时候用IN，返回数据多的时候用EXISTS，那么恭喜你，你错了 。
纠正：不要轻信网上是如何说的，学了ORACLE最大的感触就是不要记结论，自己实践了才知道，创建两张表练习一下，由于篇幅原因，我这里还是给一个结论——通过执行计划看到，大多数情况下IN
和EXISTS的效率是一模一样的，只是有时候EXISTS不能 SUBSTRING UNNESTING，导致执行计划走FILTER
，执行计划一旦走了FILTER，驱动表是改变不了的（12C
能不能我不清楚），想象一下，主表是1000W，子表返回20条，由于驱动表改变不了，很有可能是大表驱动小表了。
错误观点2、NOT IN 与 NOT EXISTS 子查询量少用NOT
IN 否则用NOT EXISTS
NOT
IN 与 NOT EXISTS也是一个道理，但是要注意NULL的情况，NULL容易导致无法使用索引，可以创建函数索引或与常量一起做一个组合索引。
错误观点3、WHERE条件有先后顺序，后面的先执行或前面的先执行
如果过滤数据量基本持平的话，两个不同的谓词过滤条件可能会因为脚本的编写而有先后顺序，但是不要因此推断出“WHERE条件有先后顺序，后面的先执行或前面的先执行”，哪个先执行时CBO根据统计信息分析之后说了算，下面两条语句的执行计划的逻辑读都是一样的
可以通过下面的列子来测试一下执行效果
- CREATE TABLE TEST02 AS SELECT * FROM DBA_OBJECTS;
- SELECT COUNT(*) FROM TEST02 A WHERE A.OWNER='SYS' AND A.OBJECT_ID=29 ;
- SELECT COUNT(*) FROM TEST02 A WHERE A.OBJECT_ID=29 AND A.OWNER='SYS' ;
错误观点4、FROM 语句有左右顺序 所以要注意书写顺序
同第3点一样，给个例子——下面两个语句的执行计划也是一样的，ORACLE知道哪个该做驱动表
，因此没有区别
- CREATE TABLE T1 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10000;--大表
- CREATE TABLE T2 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=10;--小表
- SELECT COUNT(1) FROM T1,T2;
- SELECT COUNT(1) FROM T2,T1;
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 4259280259
- ----------------------------------------------------------------------
- | Id | Operation | Name | Rows | Cost (%CPU)| Time |
- ----------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 60 (2)| 00:00:01 |
- | 1 | SORT AGGREGATE | | 1 | | |
- | 2 | MERGE JOIN CARTESIAN| | 100K| 60 (2)| 00:00:01 |
- | 3 | TABLE ACCESS FULL | T2 | 10 | 3 (0)| 00:00:01 |
- | 4 | BUFFER SORT | | 10000 | 57 (2)| 00:00:01 |
- | 5 | TABLE ACCESS FULL | T1 | 10000 | 6 (0)| 00:00:01 |
- ----------------------------------------------------------------------
- 统计信息
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 22 consistent gets
- 0 physical reads
- 0 redo size
- 527 bytes sent via SQL*Net to client
- 519 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
错误观点5、避免使用OR来连接条件，否则导致引擎放弃使用索引而进行全表扫描
如：SELECT
ID FROM T WHERE NUM=10 OR NUM=11 会全表扫描
下面来个例子，
- CREATE TABLE TEST02 AS SELECT * FROM DBA_OBJECTS;
- CREATE INDEX TEST_02_IDX_01 ON TEST02 (OBJECT_ID);
- ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';
- SELECT COUNT(*) FROM TEST02 A WHERE A.OBJECT_ID=28 OR A.OBJECT_ID=29 ;
查看执行计划
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3430686514
- ---------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost |
- ---------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 2 |
- | 1 | SORT AGGREGATE | | 1 | 5 | |
- | 2 | INLIST ITERATOR | | | | |
- |* 3 | INDEX RANGE SCAN| TEST_02_IDX_01 | 2 | 10 | 2 |
- ---------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("A"."OBJECT_ID"=28 OR "A"."OBJECT_ID"=29)
实际上使用了索引
总结：网上的经验可能是某些人看了某些文章，然后在特定的场景下测试了效果一样，甚至没有测试就以讹传讹，容易误导人，Oracle脚本中有多优化的观点不同的版本等场景下因为执行计划的不同 而导致结论不一样，因此要根据实际出发，最好再亲自测一下，DBA不会告诉你这么的，因为他们自己还要混饭吃的。
不过说不定我这盘文章中有些结论也是错的，自己测一遍才知道
不过说不定我这盘文章中有些结论也是错的，自己测一遍才知道
其实ORACLE优化还是有很多小细节需要注意的，也有很多的方法，有机会的话再与大家分享
