跟同事闲聊时,说到从存放了100万行数据的表中,返回10万行数据时,是使用索引扫描速度快还是全表扫描速度快?

基于这个话题,以个人理解描述一下oracle到底是否会如上所说.

oracle 10g已经不存在基于rule的优化器了,全部都是CBO了.之所以先说这个,是因为oracle这个举动明显提示了我们,要勤做统计分析.

首先设计一下实验场景,因为oracle会根据查询的行数,表的总行数,数据的直方图(数据分布)等来选择执行计划,这里只考虑最简单的一种情况:要查询的数据是连续分布的,且占总量的比重较大(10万:100万),数据表是一个瘦表(列很少,且列不宽).其他场景会花费很多时间在构造数据上,偷个懒,略之.

•创建表,索引和sequence
 

代码
drop sequence S_S_DEPART;drop index s_depart_key;drop table S_Depart;create table S_Depart  (   DepartId             INT                             not null,   DepartName           NVARCHAR2(40)                   not null,   DepartOrder          INT                            default 0,   constraint PK_S_DEPART primary key (DepartId));create index s_depart_key on s_depart(DepartName);create sequence S_S_DEPARTminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1nocache;

•插入数据,可用pl/sql developer的SQL窗口直接执行 
•更新数据,让其中10万行数据符合查询条件
update s_depart a set a.departname = '1' where rownum <= 100000;

•执行查询,打开sqlplus,执行如下查询语句,返回10万行数据
select  * from S_DEPART a WHERE a.DepartName = '1';                   //默认范围索引扫描,从执行计划中也可以看到

alter system set events = 'immediate trace name flush_cache';          //清除缓存,否则全部缓冲到SGA里,都不需要物理读了

select /*+FULL(a)*/ * from S_DEPART a WHERE a.departname = '1';  //强制执行全表扫描

•查询结果分析
默认执行时会使用索引,why?当然是因为没有统计分析咯,开发人员很少会想到做这个事情

基于范围索引的扫描花费了5.66秒,其中自然包括autotrace的时间,但是相对非常小,可以忽略

 
强行指定使用全表扫描

 
可以看出,强制使用全表扫描花费了13.1秒,明显性能比索引扫描差很多.
OK,至此是否就可以得出结果,使用索引扫描更好????
NO! 最开头处,我便标明了ORACLE目前只有CBO.这说明什么?要想发挥ORACLE优化器的威力,当然要了解CBO.而CBO优化的基础之一就是统计分析结果.上述实验,压根都没创建统计分析结果,这种情况基本不可能出现在生产环境.

•执行统计分析,需要使用DBA角色,可用pl/sql developer的SQL窗口直接执行
以上只是简单的执行一下我的schema下全部表的统计分析,很多参数没有优化,因为我不是DBA......

•再次执行查询
这次先看默认情况下,oracle的执行计划

可以看到,ORACEL已经改变了初衷,改选全表扫描为默认的执行计划,再看这时全表扫描的执行结果: 
可以看到,全表扫描从13.1秒降低到了3.95秒.当然每次执行SQL之前都要清除缓存

而现在看看基于范围索引扫描的执行计划:
可以看出,现在基于范围索引的执行成本已经比全表扫描要高了.

•从以上实验结果可以得出以下结论:
1.当从100万行数据的表里返回10万行时,如果数据是连续分布的,正常情况下(使用了统计分析),全表扫描会优于索引扫描.这点从ORACLE自己选择的执行计划中也可以看出
2.全表扫描时的物理读明显多于索引扫描,这也会导致CPU成本的上升,但是oracle是可以并行读取的.这类开销也会随着SGA缓冲的数据块增加而降低.
3.在做了统计分析后,其实全表扫描和范围索引扫描的性能非常接近,这是因为我上述的实验是连续分布的大量数据,连续的数据有很大可能是会保存在同一个数据块中的,所以使用索引范围扫描的效率也很高.如果表中有多个索引或者组合索引,那数据的分布极有可能不会是正好按某个索引的顺序连续保存在数据块中的,那使用索引扫描的性能将会比全表扫描慢的多.
4.设想一个极端的不连续情况:每个数据块8K,只能保存10行数据.那一共需要10万个数据块.而要查询的数据很分散,正好每个数据块里都保存有一行.这种很离散的情况,不用做什么实验也一目了然,全表扫描必然要比索引扫描快.因为全表扫描也就是10万个物理读,而索引扫描则要先去索引中找到所有rowid,发现还是要加载所有数据块.....结果只是比全表扫描多浪费了IO而已.
5.再设想一种实际很可能出现的情况:一个表100万行,一共就1千个数据块.而从索引中找到10万行数据的rowid,再去执行10万次的TABLE  ACCESS  BY  ROWID,也就是要处理10万个数据块,虽然会缓存避免物理读.但是,全表扫描一共只处理1000个数据块....这个差距一目了然.
6.

select a.index_name,          b.num_rows,b.blocks,a.clustering_factorfrom user_indexes a, user_tables bwhere  index_name ='S_DEPART_KEY'and a.table_name = b.table_name;
 

      通过执行上述语句,可以看到试验中表上索引'S_DEPART_KEY'所对应的clustering_factor(blocks=11627,clustering_factor=26436)，clustering_factor和blocks比例并不大,所以在统计分析后,全表扫描并不会比索引扫描体现出很明显的优势.

以上测试,只是最简单的一种数据分布情况.对于不同的数据值和分布情况,到底是全表扫描快还是索引扫描快,都是需要根据具体情况分析的,没有通吃的定论.

根据TOMAS书中的提示,一般在瘦表中返回数据只占总行数的百分之几(小于5%)或胖表中不超过25%时,使用索引更快的可能性非常大.上述实验中瘦表返回数据已经是10%了.

当然如果使用了索引组织表,或者要返回的数据正好就在索引中,那当然是索引更快.

代码
begindbms_stats.gather_schema_stats(ownname => 'XXX',                        //这里是我的用户名,个人自己实验时需要更换options => 'GATHER AUTO',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size skewonly',degree => 15);end;

beginfor i in 1..1000000 loopinsert into S_Depart(departId,Departname,Departorder)values(S_S_Depart.Nextval,'12345saasdasdoiuweoruvsdfserwerwer',1);end loop;end;