SQL优化经验
1、选择最高效的表名顺序（只在基于规则的优化器中有效）
ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名。 FROM子句中写入的最后一个表（基本表驱动表）会被优先处理，FROM子句中包含多个表。 在 的情况下，必须选择记录数最少的表作为基表。 如果查询连接的表超过3个，则需要选择交集表作为基表。 交集表是指被其他表引用的表。
2. WHERE子句中的join顺序
ORACLE 使用自下而上的顺序来解析 WHERE 子句。 根据这个原则，表之间的连接必须写在其他WHERE条件之前，那些能过滤出最大记录数的条件必须写在WHERE子句的末尾。
3.避免在SELECT子句中使用'*'
在解析过程中，ORACLE会将'*'依次转换为所有的列名。 这项工作是通过查询数据字典来完成的，这意味着它会消耗更多的时间。
4.减少访问数据库的次数
ORACLE在内部执行很多工作：解析SQL语句、估计索引利用率、绑定变量、读取数据块等。
5. 重置SQL*Plus、SQL*Forms 和Pro*C 中的ARRAYSIZE 参数可以增加每次数据库访问的检索数据量。 推荐值为 200
6.使用DECODE功能减少处理时间
使用 DECODE 函数可以避免重复扫描相同的记录或重复连接到相同的表。
7. 集成简单、无关联的数据库访问
如果你有几个简单的数据库查询语句，你可以将它们组合成一个查询（即使它们之间没有关系）
8.删除重复记录
删除重复记录的最有效方法（因为使用了ROWID）示例
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)9. 使用 TRUNCATE 而不是 DELETE
在删除表中的记录时，一般情况下，回滚段（rollback segments）用于存储可以恢复的信息。 如果没有COMMIT事务，ORACLE会将数据恢复到删除前的状态（准确的恢复到执行删除命令前的状态），当使用TRUNCATE时，回滚段不再存储任何可以恢复的信息. 运行该命令后，数据无法恢复。 因此，调用的资源很少，执行时间会缩短很短。 （译者注：TRUNCATE只适用于删除整张表，TRUNCATE是DDL不是DML）
10.尽可能使用COMMIT
只要有可能，在程序中尽量使用COMMIT，这样程序的性能会得到提升，而需求也会因为COMMIT释放的资源而减少。
COMMIT 释放的资源：
11.用WHERE子句替换HAVING子句
避免使用 HAVING 子句。 HAVING 只会在检索到所有记录后过滤结果集。 这个处理需要排序、合计等，如果能通过WHERE子句限制记录数，就可以减少这个开销。 （oracle中没有）on、where、having这三个可以加条件的子句中，on是最先执行的，其次是where、last，因为on是过滤不满足条件的记录在继续之前。 统计，可以减少中间操作要处理的数据。 按理说速度应该是最快的，where应该比having快，因为它在sum之前先过滤数据，只有在两表join的时候才用on。 ，所以在一张表中，只有 where 与 having 比较。
在单表查询统计的情况下，如果要过滤的条件不涉及要计算的字段，那么他们的结果是一样的，但是where可以用rushmore技术，having不行，后者比较慢速度方面。 如果涉及计算字段，则说明该字段的值在计算之前是不确定的。 按照工作流程，where的动作时间在计算之前完成，而having只在计算之后才起作用。 那么在这种情况下，两者的结果就会不同。 多表连接查询时，on比where早工作。 系统首先根据表之间的join条件，将多个表组合成一个临时表，然后按where过滤，然后计算，计算完再用having过滤。 可见，要让过滤条件发挥正确的作用，首先要了解条件应该在什么时候起作用，然后再决定放在哪里。
12.减少对表的查询
在包含子查询的SQL语句中，要特别注意减少对表的查询。
例子：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
13、通过内在函数提高SQL效率
复杂的 SQL 往往会牺牲执行效率。 能够在实际工作中掌握以上使用函数解决问题的方法是非常有意义的。
14.使用表别名（Alias）
在一个SQL语句中连接多个表时，请使用表的别名，并在每个Column前加上别名。 这样可以减少解析时间，减少Column歧义引起的语法错误。
15. 用 EXISTS 代替 IN，用 NOT EXISTS 代替 NOT IN
在很多基于基本表的查询中，为了满足一个条件，往往需要连接另一个表。 在这种情况下，使用 EXISTS（或 NOT EXISTS）通常会提高查询效率。 在子查询中，NOT IN 子句将执行内部排序和合并。 无论哪种情况，NOT IN 都是效率最低的（因为它对子查询中的表执行全表遍历）。 为了避免使用 NOT IN ，我们可以将其重写为 Outer Join 或 NOT EXISTS。
例子：
（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')16. 识别“执行效率低下”的 SQL 语句
尽管用于SQL优化的各种图形化工具层出不穷，但是自己编写SQL工具解决问题永远是最好的方式：
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
17.用索引提高效率
索引是表的概念部分，用于提高检索数据的效率。 ORACLE 使用复杂的自平衡 B 树结构。 通常，通过索引查询数据比扫描全表更快。 当ORACLE发现执行查询并更新时，ORACLE优化器会在为语句选择最佳路径时使用索引。 使用索引还可以在连接多个表时提高效率。 使用索引的另一个好处是它提供了主键（primary key）的唯一验证。
对于那些 LONG 或 LONG RAW 数据类型，您几乎可以索引所有列。 通常，使用索引在大型表中特别有效。 当然你也会发现在扫描小表的时候使用索引也可以提高效率。 虽然使用索引可以提高查询效率，但是我们也要注意它的成本。 索引需要存储空间和定期维护。 每当在表中添加或删除记录或修改索引列时，索引本身也会被修改。 这意味着每条记录的 INSERT 、 DELETE 、 UPDATE 将支付 4、5 倍的磁盘 I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引实际上会减慢查询响应时间。 定期重建索引是必要的：
ALTER INDEX REBUILD
18. 用 EXISTS 替换 DISTINCT
当提交包含一对多表信息（如部门表和员工表）的查询时，避免在 SELECT 子句中使用 DISTINCT。 一般可以考虑用EXIST代替。 EXISTS使得查询速度更快，因为RDBMS的核心模块会在子查询的条件一旦满足，就立即返回结果。
19、sql语句使用大写； 因为oracle总是先解析sql语句，将小写字母转换成大写字母再执行
20、java代码中尽量少用连接符“+”连接字符串！
21. 避免在索引列上使用NOT 一般来说，我们应该避免在索引列上使用NOT，NOT 会和在索引列上使用函数产生同样的效果。 当ORACLE“遇到”NOT时oracle修改字段名，他会停止使用索引，而是进行全表扫描。
22. 避免在索引列上使用计算。
在 WHERE 子句中，如果索引列是函数的一部分。 优化器不会使用索引而是全表扫描。
23. 使用 >= 而不是 >
高效：两者的区别在于，前者DBMS会直接跳转到DEPT等于4的第一条记录，而后者会先定位到DEPTNO=3的记录，然后向前扫描到DEPT大于3的第一条记录。
24. 将 OR 替换为 UNION（用于索引列）
通常，将 WHERE 子句中的 OR 替换为 UNION 会有更好的效果。 在索引列上使用 OR 将导致全表扫描。 请注意，上述规则仅对多个索引列有效。 如果有列不是Index，查询效率可能会因为不选择OR而降低。 在以下示例中，索引同时建立在 LOC_ID 和 REGION 上。
高效：如果坚持使用OR，需要先返回记录最少的索引列。
25. 将 OR 替换为 IN
这是一条简单易记的规则，但实际执行效果有待检验。 在ORACLE8i下，两者的执行路径好像是一样的。
26. 避免在索引列上使用 IS 和 IS NOT
避免在索引中使用任何可为空的列，ORACLE 将无法使用索引。 对于单列索引，如果该列包含空值，则该记录将不存在于索引中。 对于复合索引，如果每列都为空，则该记录也不存在于索引中。 如果至少一列不为空，则该记录将存在于索引中。
例：如果在表的A列和B列上建立唯一索引，表中有一条A、B值为(123,)的记录，ORACLE将不接受下一条A、B值相同的记录(123,) 但是，如果所有的索引列都是空的，ORACLE会认为整个键值都是空的，空不等于空。 所以你可以插入1000条key值相同的记录oracle修改字段名，当然都是空的！ 因为索引列中不存在空值，所以在WHERE子句中对索引列进行空值比较会导致ORACLE禁用索引。
效率低下：（索引失效）
27.总是使用索引的第一列
如果索引建立在多个列上，优化器将选择仅在其第一列（前导列）被 where 子句引用时使用该索引。 这也是一个简单而重要的规则，当只引用前导列索引第二列时，优化器使用全表扫描而忽略索引。
28. 将 UNION 替换为 UNION-ALL（如果可能）
当SQL语句需要对两个查询结果集进行UNION时，会将两个结果集以UNION-ALL的形式进行合并，然后进行排序，输出最终结果。 如果使用 UNION ALL 而不是 UNION，则不需要这样的排序。 效率将因此得到提高。
需要注意的是：UNION ALL会在两个结果集中重复输出相同的记录。 所以还是需要从业务需求上分析使用UNION ALL的可行性。 UNION 会对结果集进行排序，这个操作会使用 SORT_AREA_SIZE 的内存。 这个内存的优化也很重要。 下面的SQL可以用来查询排序的消耗情况。
29. 使用 WHERE 而不是 ORDER BY
示例：表 DEPT 包含以下列
效率低下：（未使用索引）
30.避免改变索引列类型
在比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。
假设 EMPNO 是一个数值类型的索引列
SELECT … FROM EMP WHERE EMPNO = ‘123’
其实ORACLE类型转换后，语句就变成了
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123’)
幸运的是，索引列没有发生类型转换，索引的用途没有改变。
现在，假设 EMP_TYPE 是字符类型的索引列
SELECT … FROM EMP WHERE EMP_TYPE = 123
该语句由 ORACLE 转换为
SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部类型转换，这个索引不会被使用！ 为了避免 Oracle 对你的 SQL 进行隐式类型转换，最好显式地表达类型转换。 注意，比较字符和数字时，ORACLE优先将数字类型转换为字符类型。
31. 注意 WHERE 子句
SELECT 语句中的一些 WHERE 子句不使用索引，这里有一些例子。
在下面的例子中
32、如果检索数据量超过表记录数的30%，使用索引不会有明显的效率提升。
在某些情况下，使用索引可能比全表扫描慢，但这是同一个数量级的差异。 一般情况下，使用索引比全表扫描快几倍甚至几千倍！
33.避免资源密集型操作
带有 DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 的 SQL 语句启动 SQL 引擎。
执行资源密集型排序 (SORT) 功能。 DISTINCT 需要一种排序操作，而其他的至少需要两种排序。 一般来说，带有UNION、MINUS、INTERSECT的SQL语句都可以改写成其他方式。 如果你的数据库的SORT_AREA_SIZE调整的好，你也可以考虑使用UNION，MINUS，INTERSECT，毕竟它们的可读性很强。
34. 优化 GROUP BY
为了提高GROUP BY语句的效率，可以在GROUP BY之前过滤掉不需要的记录。 以下两个查询返回相同的结果，但第二个查询要快得多。
admin