SQL语句的执行计划和暗示

我真的感觉写SQL语句是锻炼智商的一个好方法. 当你用一条语句实现了一个复杂的功能.或通过一个小技巧让执行速度成百倍的提高时,那种感觉比嗑了药还爽.

前几天才了解到SQL语句的执行计划这个东东.大概意思就是一条SQL语句按照怎样的步骤去查询数据的.在PL/SQL developer中的explain plan window中写入语句,执行,就可以看到该语句的执行计划(或在某编辑窗口按F5).很有用,还没有全部搞懂,但想先写写.

一条简单的语句

select * from table 或select * from table where name='123'

查看它的执行计划,如果name不是索引的话,都应该是:table access full.是啊,这两条语句都需要依次检索整个表才能检索出想要的信息.

而select  *  from  table where id = '123'

如果id是索引的话.此时再看执行计划,就是table access by index rowid,并会显示出根据哪个index检索的.根据索引来查当然会快很多.

现实中的SQL语句大多不会这么简单.有很多条件,还会并联别的表.这时你就会发现,SQL的执行计划有时很笨.

select * from table wher name='123' and id is null(id是索引)

上面语句的执行计划还是table access full(执行计划根据你的表结构和索引有关,上面只是个例子).也就是说它还是依次在表中检索数据,先看第一条数据的name是多少,id是不是空,再看第二条...直到检索到最后一条.仔细看下条件,想想,查ID比较快一些吧,而且ID中为空的应该不多吧,把ID为空的数据先找出来.再从这些数据中全扫描查找name为123的,是不是比一条一条的检索快.那么怎么让SQL语句按照我们的想法来检索呢.那就要用暗示了,如下:

/*+ index(t,index_name)*/

/*+ */是使用暗示的语法

index()是暗示的一个函数,用于指定使用某个索引.当然还有别的函数,比如在多表关联时用于指定哪两个表先关联的函数.等

t 是表的别名,必须使用别名.

index_name是使用的索引的名字.注意t 和index_name之间是逗号不是点.

把暗示加到select 后

select /*+ index(t,id)*/ * from table t wher t.name='123' and t.id is null

这时再看执行计划,就是table access by index rowid.既先按照id检索.

这个例子还是比较简单,我遇到的真实情况大概是这样的.

select a.*,b.* from table_a a,table_b b where a.id = b.id and a.date>to_date('2007-01-01','yyyy-mm-dd')

其中a,b 的id和a 的date都是索引.最初的执行计划是a 和b先根据id关联,再从关联的数据中检索大于2007-01-01的数据.a,b表是基表,相关联的数据量很大,再加上还有别的条件,所以速度很慢,花了180秒.后经高手指导加了这样一个暗示

/*+ index(a,date)*/这样执行计划就变成了:先找a 表中大于2007-01-01的数据,再将这些数据与b表相关联.因为虽然a,b表关联的数据量大,但a表中date>2007-01-01的数要少很多.用这部分少量的数据来关联,速度马上就上去了,只花了2秒时间就查出结果.快了90倍啊.

到现在我还不太会看执行计划,当查询条件多了,关联的表多的时候,执行计划一个嵌一个,好麻烦的.而且暗示也不是随便用的.并不是任意指定一个索引就好使,要抓住关键点,分析各个条件间的关系,数据的主要集中范围,等等.有时如果没用好,指定了索引后还不如全表扫描快.