开发高效的SQL语句

本节介绍了提高SQL语句效率的方法：

■验证优化程序统计信息

■审查执行计划

■重构SQL语句

■重组索引

■修改或禁用触发器和约束

■重组数据

■随着时间的推移维护执行计划

■尽可能少地访问数据

验证优化程序统计信息

查询优化器在确定最佳执行计划时使用在表和索引上收集的统计信息。如果尚未收集这些统计信息，或者统计信息不再代表数据库中存储的数据，则优化程序没有足够的信息来生成最佳计划。

要检查的事项：

■如果收集数据库中某些表的统计信息，则最好收集所有表的统计信息。如果您的应用程序包含执行连接的SQL语句，则尤其如此。

■如果数据字典中的优化程序统计信息不再代表表和索引中的数据，则收集新的统计信息。检查字典统计信息是否过时的一种方法是将表的实际基数（行计数）与DBA_TABLES.NUM_ROWS的值进行比较。此外，如果谓词列上存在严重的数据偏差，请考虑使用直方图。

审查执行计划

在OLTP环境中调优（或写入）SQL语句时，目标是从具有最具选择性的过滤器的表中驱动。这意味着传递到下一步的行数较少。如果下一步是连接，则表示连接的行数较少。检查访问路径是否最佳。

检查优化程序执行计划时，请查找以下内容：

■驱动表具有最佳过滤器。

■每个步骤中的连接顺序将最少的行数返回到下一步（即，连接顺序应尽可能反映到最佳尚未使用的过滤器）。

■join方法适用于返回的行数。例如，当语句返回许多行时，通过索引的嵌套循环连接可能不是最佳的。

■数据库有效地使用视图。查看SELECT列表以查看是否需要访问视图。

■有任何无意义的笛卡尔结果（即使是小表）。

注意：

本节中描述的准则面向生成频繁执行的SQL。这里不鼓励使用的大多数技术可以合理地用于临时语句或在性能不重要的情况下不经常运行的应用程序中。

■有效访问每个表：

考虑SQL语句中的谓词和表中的行数。 查找可疑活动，例如对具有大量行的表进行全表扫描，这些行在where子句中具有谓词。 确定索引未用于此类选择性谓词的原因。

全表扫描并不意味着效率低下。 在小型表上执行全表扫描或执行全表扫描以针对返回的行数利用更好的连接方法（例如，hash_join）可能更有效。

如果这些条件中的任何一个不是最佳的，那么考虑重构SQL语句或表上可用的索引。

重构SQL语句

通常，重写低效的SQL语句比修改它更容易。 如果您了解给定语句的用途，那么您可以快速轻松地编写满足要求的新语句。

使用AND和=撰写谓词

要提高SQL效率，请尽可能使用等值连接。 在未转换的列值上执行等值连接的语句是最容易调整的。

避免在WHERE子句中转换列

使用未转换的列值。 例如，使用：

WHERE.order_no = b.order_no

而不是：

WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

= TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

不要在谓词子句或WHERE子句中使用SQL函数。任何使用列的表达式，例如以列为参数的函数，都会导致优化器忽略在该列上使用索引的可能性，甚至是唯一索引，除非定义了基于函数的索引，数据库可以使用。

避免使用混合模式表达式，并注意隐式类型转换。如果要在VARCHAR2列charcol上使用索引，但WHERE子句如下所示：

AND charcol = numexpr

其中numexpr是数字类型的表达式（例如，1，USERENV（'SESSIONID'），numcol，numcol + 0，...），Oracle数据库将该表达式转换为：

AND TO_NUMBER（charcol）=