高效SQL语句必杀技
作者：网络转载 发布时间：[ 2013/6/18 10:28:10 ] 推荐标签：
15）使用表连接替换EXISTS
一般情况下，使用表连接比EXISTS更高效
-->低效：
SELECT *
FROM employees e
WHERE EXISTS
(SELECT 1
FROM departments
WHERE department_id = e.department_id AND department_name = 'IT');
-->高效：
-->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
SELECT *
FROM employees e INNER JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name = 'IT';
16）用EXISTS替换DISTINCT
对于一对多关系表信息查询时（如部门表和雇员表），应避免在select 子句中使用distinct，而使用exists来替换
-->低效：
SELECT DISTINCT e.department_id， d.department_name
FROM departments d INNER JOIN employees e ON d.department_id = e.department_id;
-->高效：
SELECT d.department_id，department_name
from departments d
WHERE EXISTS
(SELECT 1
FROM employees e
WHERE d.department_id=e.department_id);
EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果
-->经测试此写法SQLplus下比上面的写法多一次逻辑读，而在Toad下两者结果一致
17）使用 UNION ALL 替换 UNION（如果有可能的话）
当SQL语句需要UNION两个查询结果集时，这两个结果集合会以UNION-ALL的方式被合并， 然后在输出终结果前进行排序。
如果用UNION ALL替代UNION， 这样排序不是必要了。 效率会因此得到提高。
注意：
UNION ALL会输出所有的结果集，而UNION则过滤掉重复记录并对其进行排序.因此在使用时应考虑业务逻辑是否允许当前的结果集存在重复现象
寻找低效的SQL语句
-->下面的语句主要适用于从视图v$sqlarea中获得当前运行下且耗用buffer_gets较多的SQL语句
SELECT executions
， disk_reads
， buffer_gets
， ROUND( ( buffer_gets
- disk_reads )
/ buffer_gets， 2 )
hit_ratio
， ROUND( disk_reads / executions， 2 ) reads_per_run
， sql_text
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND ( buffer_gets
- disk_reads )
/ buffer_gets < 0.80
ORDER BY 4 DESC;
18）尽可能避免使用函数，函数会导致更多的 recursive calls
二、合理使用索引以提高性能
索引依赖于表而存在，是真实表的一个缩影，类似于一本书的目录，通过目录以更快获得所需的结果。Oracle使用了一个复杂的自平衡B数据结构。即任意记录的DML操作将打破索引的平衡，而定期重构索引使得索引重新获得平衡。通常，通过索引查找数据比全表扫描更高效。
任意的DQL或DML操作，SQL优化引擎优先使用索引来计算当前操作的成本以生成佳的执行计划。一旦使用索引操出参数optimizer_index_cost_adj设定的值才使用全表扫描。同样对于多表连接使用索引也可以提高效率。同时索引也提供主键（primary key）的性验证。
除了那些LONG或LONG RAW数据类型，你可以索引几乎所有的列.通常，在大型表中使用索引特别有效.当然，你也会发现，在扫描小表时，使用索引同样能提高效率。
虽然使用索引能得到查询效率的提高，但是索引需要空间来存储，需要定期维护.尤其是在有大量DML操作的表上，任意的DML操作都将引起索引的变更这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。
DML操作使用索引上存在碎片而失去高度均衡，因此定期的重构索引是有必要的。
1）避免基于索引列的计算
where 子句中的谓词上存在索引，而此时基于该列的计算将使得索引失效
-->低效：
SELECT employee_id， first_name
FROM employees
WHERE employee_id + 10 > 150; -->索引列上使用了计算，因此索引失效，走全表扫描方式
-->高效：
SELECT employee_id， first_name
FROM employees
WHERE employee_id > 160; -->走索引范围扫描方式
例外情形
上述规则不适用于SQL中的MIN和MAX函数
hr@CNMMBO> SELECT MAX( employee_id ) max_id
2 FROM employees
3 WHERE employee_id
4 + 10 > 150;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1481384439
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 5 | 20 | 1 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK | 5 | 20 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
sales@spasvo.com