高效SQL_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2258 | 回复: 0   主题: 高效SQL        下一篇 
    本主题由 Administrator 于 2014-11-19 9:35:01 移动
Xuziwen
注册用户
等级:上尉
经验:721
发帖:66
精华:0
注册:2012-7-4
状态:离线
发送短消息息给Xuziwen 加好友    发送短消息息给Xuziwen 发消息
发表于: IP:您无权察看 2012-8-1 14:49:18 | [全部帖] [楼主帖] 楼主

本文对影响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




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论