欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle统计sql优化方式和原理详解

程序员文章站 2022-06-04 10:07:23
简介:oracle统计类sql的优化,此篇只讲count sum avg max min orderby,主要还是要知道索引存储列值和rowid的特性,我的博客里也有常用索引(树状索引)的详解。 一...

简介:oracle统计类sql的优化,此篇只讲count sum avg max min orderby,主要还是要知道索引存储列值和rowid的特性,我的博客里也有常用索引(树状索引)的详解。

一.count(*)的优化

直接上例子(假设test表有10w数据,列有10列):

1.select count(*) from test?

count(*)我们都知道是统计有多少数量,通常我们建表都会有一个主键id,我们这条语句通过执行计划可以发现它是会走index fast full scan ,已经走了主键这个索引;如果没有主键的情况,会走table access full(全表扫描),不管是耗费io和逻辑读都会比走索引多,效率自然会慢。

原理:主键也是一个索引,而且不能为空,就意味着有一列包含id值和rowid 的索引块已经产生了,oracle在这一列上就能得到这个表的总数量,就不会选择全表扫描所有的数据块了,减少耗费的io和逻辑读,从而提高效率。

注意:使用count(*)走索引,不管你的数据里有没有null值,索引列必须不能为空,不然需要加上 field(列名)is not null 的条件才能让count(*) 走索引。

二.sum和avg的优化

1.select sum(amount),avg(amount) from test

假设经常要统计amount字段求和或者平均数,可以在amount字段上加一个索引(通常好像没有这个说法),但是从提升效率和索引角度来说,建立amount字段的索引,也可以只在amount索引块上做扫描计算,注意的是用sum和avg也必须字段不为空或者加上?field(列名)is not null 的条件。

原理同count。

三.max和min的优化

1.select max(amount) from test / select min(amount) from test

对于max和min两个,建立索引对优化效率来说尤其体现明显,可以自己看一下执行计划,走的是index full scan。

问题:为什么sum和count走的是 index fast full scan 呢,和 index full scan 有什么区别呢?

原理:理解了索引结构的都知道,索引是有序的,而且索引块的数据 是从左到右依次从小到大,所以min只要扫描最左边那一个索引块,max只要扫描最右边那个索引块,加起来只有2次io,所以max和min相对来说更快,耗费更小。

2.select max(amount),min(amount) from test

这样子写是不会走索引的,因为这样写还是先后 进行max和min运算,不管是先做max还是min运算,在同时数据变化的时候,不全表扫描是不能保证哪个是最大,哪个最小,oracle优化器也就会选择全表扫描了,所以可以改成这样子写,select max,min from (select max(amount) from test) a,(select min(amount) from test) b。