本文对影响SQL及PL/SQL代码效率的一些因素进行了简短的介绍。
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
检查统计信息
基于成本的优化(CBO)使用统计信息来选择执行计划。如果统计信息不正确,则CBO可能选择错误的执行计划。由于这个原因,确认统计信息正常刷新是很重要的。这个会在以后的文章中专门介绍。
为什么查询不使用索引?
在下列情况下,列上的索引不被使用:
1.优化器判定不使用索引效率更高的情况。比如:如果一个查询要返回表中的大部分数据,那么全表扫描将是更高效的方式。
2.在索引列上使用函数,比如:WHERE UPPER(name) = 'JONES'.此时需要使用函数索引(Function-Based Index)
3.在索引列上使用数学运算,比如: WHERE salary + 1 = 10001
4.在索引列上使用连接符“||”,比如:WHERE firstname || ' ' || lastname = 'JOHN JONES'
5.WHERE子句中不使用联合索引的第一列。注:在Oracle 9i及以后的版本中,由于引入INDEX SKIP SCAN,此条不在适用。
6.使用OR。此时可通过INDEX HINT强制使用索引。
EXISTS还是IN?
对于这两个关键字的差异和用法可以不必费神去记忆。一是,在新版本的ORACLE中,它们的差别越来越小;二是,在需要选择的时候,两个都试一下,看哪个效率更高。
存在性检查
当要检查特定记录是否存在时,优先选择一下方法:
1当不确定记录是否存在时,使用MERGE;
2执行INSERT并捕捉DUP_VAL_ON_INDEX 异常;
3执行UPDATE,并使用SQL%ROWCOUNT取得修改行数;
如果相同的数据有多条,则存在性检查没有必要将所有数据查询出来。此时可以使用下面的方法。
SELECT COUNT(*)
INTO v_count
FROM items
WHERE item_size = 'SMALL'
AND rownum = 1;
IF v_count = 0 THEN
-- Do processing related to no small items present
END IF;
或者:
SELECT COUNT(*)
INTO v_count
FROM dual
WHERE EXISTS (SELECT 1
FROM items
WHERE item_size = 'SMALL');
IF v_count = 0 THEN
-- Do processing related to no small items present
END IF;
不等式比较
使用不等式(item_no > 100)时,优化器会首先估计结果行数,然后再确定查询路径。而通常,这个估计并不准确。因此,如果预知数据的分布,就可以使用优化提示来执行或避免全表扫描以调高效率。
如果列上有可以用于范围扫描索引,那么>=的效率高于>。如果条件为 item_no > 100,则查询会执行全表扫描;如果查询改为item_no >= 101,ORACLE会直接找到ITEM_NO为101的记录,然后以此为起点进行范围扫描。当索引很大时,可以大大减少需要读取的块。
当事情看上去不怎么妙时!
1找出导致问题的查询。可以使用SQL TRACE,也可以启用SET TIMING ON并在SQL*Plus中单个执行查询;
2查看解释计划,查找可疑的全表扫描。注意:对于小表而言,全表扫描更高效。
3试着增加索引,减少全表扫描。通常外键会用来关联,所以应该索引。需要记住的是,过多的索引会降低插入、更新和删除效率。
主驱动表(Driving Tables)
可以通过裁剪FROM和WHERE子句来提高效率。ORACLE在执行查询时,会从FROM子句的表中选择一个表作为主驱动表,在按要求提取了该表的数据后,再以此表为基础提取下一个表(以下成为从驱动表)的数据,依次类推。FROM子句中表的顺序及WHERE子句中条件的出现顺序并不能决定哪个表为主驱动表,但可以影响ORACLE选择主驱动表及确定从驱动表顺序的行为。基于此,查询语句宜遵从以下规则:
1将返回结果集小的表置于FROM子句的最右侧,不妨叫做意向主驱动表。ORACLE不一定会选择该表作为主驱动表,但更可能选择该表。
2返回结果集越大的从驱动表越靠近FROM。
3 WHERE子句中,与主驱动表相关的条件宜出现在最上面,即离FROM最近,与顺序最靠近FROM的从驱动表相关的条件宜在最下面出现,即离FROM最远。
下面的例子对上面的规则进行了说明。
对规则的说明:
FROM d, c, b, a
WHERE a.join_column = 12345
AND a.join_column = b.join_column
AND b.join_column = c.join_column
AND c.join_column = d.join_column;
如果此时需要对D表进行过滤,则有:
FROM d, c, b, a
WHEREa.join_column = 12345
AND a.join_column = b.join_column
AND b.join_column = c.join_column
AND c.join_column = d.join_column
AND d.name = 'JONES';
根据结果集大小及索引情况,此时ORACLE可能会选择D作为主驱动表,所以最好将上述写法改成下面的样子:
FROM c, b, a, d
WHERE d.name = 'JONES'
AND d.join_column = 12345
AND d.join_column = a.join_column
AND a.join_column = b.join_column
AND b.join_column = c.join_column
注意条件d.join_column = 12345与a.join_column = 12345是等价的。
再次说明,这种结构的调整并不能替ORACLE选择主驱动表及确定从驱动表顺序,但可以给予优化器一个很好的提示。
缓存表(Caching Tables)
对于经常进行全表扫描的小表,可以考虑通过以下语句将其缓存。
ALTER TABLE employees CACHE;
但这可能影响查询的执行计划,宜谨慎使用。
提高解析速度
使用绑定变量和表别名。
该贴由system转至本版2014-11-19 9:35:01