文档章节

oracle数据库查询语句优化机制

c
 catlover
发布于 2016/07/14 10:36
字数 1716
阅读 6
收藏 0

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子句存放大量明细。

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

 

四、路径选择代价

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

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

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

© 著作权归作者所有

共有 人打赏支持
c
粉丝 1
博文 13
码字总数 7741
作品 0
浦东
项目经理
带您了解Oracle查询工作原理

假如,我们现在利用Select语句从数据库查询数据,Oracle数据库是如何运作的呢?下面就让我们来了解一下Oracle查询工作原理。 结合一条简单的select语句,看看Oracle数据库后台的运作机制。这对...

0o清风徐来o0
2012/10/26
0
1
认识MySQL---JC MySQL系列(一)

(一)认识MySQL MySQL数据库是开放源代码的关系型数据库。目前,它可以提供的功能有:支持sql语言、子查询、存储过程、触发器、视图、索引、事务、锁、外键约束和影像复制等。 同Oracle和S...

jk88
2013/05/24
0
0
Mysql 和 Postgresql 对比

Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql, 很多开源软件都是以Mysql 作为数据库标准,并且以Mysql 作为抽象基础的,但是具体使用过程中,发现Mysql 有很多问题...

宏哥
2010/12/14
32.8K
63
ORACLE将执行过的SQL语句存放在内存的共享池

Oracle SQL性能优化深入浅出 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执...

技术小甜
2017/11/09
0
0
从Select语句看Oracle查询原理

Select语句可以说是DBA和数据库开发者在工作中使用最多的语句之一,但这条语句是如何执行?在Oracle数据库中又是如何运作的呢?今天我们就从一条简单的Select语句开始,看看Oracle数据库后台的...

孟飞阳
2016/07/15
13
0

没有更多内容

加载失败,请刷新页面

加载更多

CMD命令行:查看 Windows 操作系统的安装时间

电脑越用越卡,计划以后每两个月重新安装一次系统。 那,怎么查看自己系统的安装日期? 问题抛出来了,其实很简单的。 cmd 中输入 systeminfo 命令,回车,等一会 …… 出来结果后,查找下面...

LivingInFHL
13分钟前
1
0
复习

10月19日任务 打印某行到某行之间的内容 sed转换大小写 sed在某一行最后添加一个数字 删除某行到最后一行 打印1到100行含某个字符串的行 一.打印某行到某行之间的内容 #sed -n '/\[abcfd\]/...

hhpuppy
14分钟前
1
0
精通Spring Boot——第十一篇:使用自定义配置

今天这篇文章给大家介绍自定义配置的两种方式 第一式: 使用@ConfigurationProperties,且看代码 package com.developlee.customconfig.config;import org.springframework.boot.context.p...

developlee的潇洒人生
20分钟前
1
0
python:pycharm启动出现异常:io.netty.channel.ChannelException.....

尝试用管理员权限启动终端, 输入: netsh winsock reset 重启电脑. 360的优化搞出来的幺蛾子........

Oh_really
29分钟前
1
0
设计模式学习与应用——策略模式

概念 策略模式定义了一系列的算法,并将每一个算法封装起来,而且使他们可以相互替换,让算法独立于使用它的客户而独立变化。 使用场景 1.在系统里面许多类,类之间区别仅在于方法行为,那么...

隔壁老余在这
33分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部