oracle数据库查询语句优化机制
博客专区 > catlover 的博客 > 博客详情
oracle数据库查询语句优化机制
catlover 发表于1年前
oracle数据库查询语句优化机制
  • 发表于 1年前
  • 阅读 3
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

摘要: 新工作是跟数据仓库和数据分析相关的,感觉以后的发展路线可以逐渐确定了,开始新的学习了,从DBA开始,一步步来。 以下东西均从网上搜索而来,搜索的内容也是经过很多人转发,故将这些内容进行整理,方便自己以后需要时查询。

1、 名词解释

状态统计(Statistics):表相关列存储值的规模、分布、密度等。

选中率(Selectivity):限定语句起作用时,将被访问的行数占总行数的百分比。

耗费(Cost):访问行时,所花费的CPU时间、I/O吞吐量、网络流量。

2、 优化机制

选择使总代价{规模×选中率×耗费}达到最小的访问路径。

Oracle在处理时,对索引引起的代价没有记入到访问路径选择中来,文档中指出可以进行用户定义。

参见《Data Cartridge Developer’s Guide—8.Query Optimization》。

在单表访问的时候,该机制很容易理解。即若存在索引,则选择命中率最小的索引进行访问。

若两表联接,代价为{本表规模×本表选中率×本表耗费×联接表基于联接的选中率×联接表的耗费}。在多表联合访问时,先前访问的估算结果集作为后继访问中的状态统计,进而计算其后继访问代价。对按不同访问路径计算的累计代价进行比较,选择使代价最小路径进行访问。

当存在偏差时,后继代价的估算会受到较大的影响。最终的访问路径未必是最优的。

对CPU和I/O的平衡,在多表联合访问时,势必要求尽可能首先选择中间结果集小的优先访问。

查询操作主要有数据提取、数据筛选(表内限定与表间联接)、语义规整、运算、输出几个步骤。后三步,没有太大的优化空间。优化主要在前两步,数据筛选的顺序又直接影响了数据提取量,是关键的一步。

3、 非常规语句分析

一、WITH子句

With v_pol as

( select * from t_pol where app_date>=to_date’20040101’,’yyyymmdd’)

)

select * from v_pol;

该子句需要创建缓冲区以容纳中间结果,对中间结果集较少的可以采用,反之,建议不要使用。若中间结果集被反复多次用到,采用该语句效果较好。

 

二、ORDER子句

select * from t_pol where app_date>=to_date’20040101’,’yyyymmdd’ order by polno

该子句一般需要缓冲区以进行排序,当order by建立在已排序的索引基础上,并且也正好使用了该访问路径,则不需要额外的缓冲区、也不需要做排序处理。未参考文档。

 

三、Window类函数

select polno,count(*) over(partition by agentno) agent_pol_num

from t_pol where app_date>=to_date’20040101’,’yyyymmdd’

该语句需要缓冲区以进行排序,大部分窗口函数内建排序机制,所需缓冲区的大小视窗口的规模而定。当窗口函数激活了排序功能时,若中间结果集过大,应慎重处理。

 

四、CONNECT BY

Select deptno,parentno,level

from t_dept

start with parentno is null

connect by parentno=prior deptno

该语句不能与ORDER子句联合使用。本语句采用中序遍历方式访问森林。当存在多表连接时,首先进行连接;否则,首先处理自连接,后执行Where子句。

 

五、GROUPING SETS

Select deptno,agentno,month,count(*) pol_num

From t_pol

Group by grouping sets     ( (deptno,agentno),

                                        (deptno,month),

                                        ()

                                       )

同多次执行简单的group by子句相比,其降低了细节数据访问的次数,若聚类函数支持Merge功能,那么()分组的汇总可以由(deptno,agentno)分组的汇总结果集计算得到,降低了计算量。

 

六、DISTINCT子句

该语句需要缓冲区以进行排序,当中间结果集较大时,应慎重处理。

 

七、UNION语句

该语句等价于DISTINCT语句,是建立在两个数据源上的。

UNION ALL只是简单的数据源归并。不需要建立中间结果集。

4、 优化处理

一、建立索引

建立索引的目的为降低访问耗费。

被用来联接的列必须建立索引,且联接列不能在运算后参与联接。

当存在多个可用索引时,系统会自动选择代价较小的索引优先使用。可以指定索引参与代价估算。

Select /*+ index idx_agentno*/

From t_pol where agentno=1

其中/*+ */中间的内容,指示访问选择器的处理方式。

同一索引键值下存在记录数越多,则访问耗费越高;没有索引的表访问耗费最高。对于同一索引,不同键值下的记录数分布不理想,会造成非优路径的选择。

当列在计算后参与联接或选择时,该列不参与含该列的索引的耗费估算中。即该列仅作为普通列处理而不是索引列。应慎重处理。

 

二、指定联接顺序

在没有特别说明下,系统采用基于代价的方式选择路径。允许采用说明的方式来指定联接顺序。

Select /*+ ordered */

From t_pol a,t_agent b,t_dept c

Where a.agentno=1

 And a.agentno=b.agentno

 And b.deptno=c.deptno

 And a.deptno=c.deptno

该语句由t_pol表开始逐次联接t_agent、t_dept。以表出现的先后次序作为联接次序而不是以Where联接语句为准。

可以按照选中率由小到大,依次联接各表。

 

三、估算中间结果集

对于大型应用来说,应考虑中间结果集的大小。若中间结果集较大,又不可避免,则可以通过汇总方式来降低结果集的规模。

避免使用具有ORDER性质的子句。可以通过建立合适的索引并启用索引来达到排序的目的。索引值是已排序的。

避免使用WITH子句存放大量明细。

避免选择不需要的列,不参与后继运算的列不需要读取。

 

四、路径选择代价

对于查询语句的分析需要一定的代价,联接的表越多、可用的索引越多、则分析的代价越高,准确度越低。语句的复杂程度由可选访问路径数决定。

避免多表联接,不可避免时,建立有效的索引、指定索引、指定联接顺序达到最优化路径选择。

在路径选择分析时,需要对统计状态进行访问,并且相关的记录项进行锁定,处理结束后解锁。若分析时失败且未能解锁,则系统将处于瘫痪状态。未参考文档。

共有 人打赏支持
粉丝 2
博文 12
码字总数 7741
×
catlover
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: