优化一个rownum=1的sql

因为一直身体不适，挺长时间没写文章了，今天先写个简单的吧。

这是某个客户的BOSS系统中的一个top sql，SQL代码如下：

SELECT

t1.seq AS sequence

,t1.TemplateID AS templateId ,t1.parameter AS parameter

,t1.SERVICE_NO AS serviceNo ,trim(t1.phone_no) AS phoneNo

,to_char(t1.INSERT_TIME, 'yyyymmddhh24miss') AS insertTime

,t1.OP_CODE AS op_code ,t1.LOGIN_NO AS login_no

,t1.SERV_NO AS servNo ,t1.SERV_NAME AS servName

,to_char(t1.send_time, 'yyyymmddhh24mi') AS sendTime

,t1.SUB_PHONE_SEQ AS subPhoneNo

,t1.hold1 AS hold1 ,t1.hold2 AS hold2

,t1.hold3 AS hold3 ,t1.hold4 AS hold4

,t1.hold5 AS hold5

FROM PUSH_1_8610 t1

WHERE rownum <= : 1;

非常简单的一个SQL，每执行一次要消耗非常高的buffer gets，见下图：

执行计划是全表扫描：

-----------------------------------------------------------------------------------

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 1680 (100)| |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| PUSH_1_8610 | 1 | 2513 | 1680 (2)| 00:00:09 |

-----------------------------------------------------------------------------------

正常来讲，随便从一张表取一条记录，如果表的每个block都有数据，只需要读一次就可以返回了。但是，如果表的blocks数很多，但开始部分是空的（delete了），或者整张表的记录都被delete了，那么就需要把整张表都扫描完才行。

这张表就是这样的，16471个blocks，0条记录。

针对这种sql，可以借助index的hint一招搞定，但是有个要求就是，表上这个index用到的字段必须有一个定义是not null，符合这样的索引随便找一个就行。 这样就不用走全表扫描了。

这个表有个主键：PK_PUSH_1_8610，那优化后的SQL就变成了：

SELECT /*+ index(t1 PK_PUSH_1_8610) */

t1.seq AS sequence

,t1.TemplateID AS templateId ,t1.parameter AS parameter

,t1.SERVICE_NO AS serviceNo ,trim(t1.phone_no) AS phoneNo

,to_char(t1.INSERT_TIME, 'yyyymmddhh24miss') AS insertTime

,t1.OP_CODE AS op_code ,t1.LOGIN_NO AS login_no

,t1.SERV_NO AS servNo ,t1.SERV_NAME AS servName

,to_char(t1.send_time, 'yyyymmddhh24mi') AS sendTime

,t1.SUB_PHONE_SEQ AS subPhoneNo

,t1.hold1 AS hold1 ,t1.hold2 AS hold2

,t1.hold3 AS hold3 ,t1.hold4 AS hold4

,t1.hold5 AS hold5

FROM PUSH_1_8610 t1

WHERE rownum <= : 1;

加了这个hint后，不管表的数据如何变化，都会在几毫秒内返回结果。

今天刚刚看到公司内一位老前辈写的一篇文章，讲到慎用hint。大方向是对的，但是，有些sql还是需要hint来进行优化，这个SQL就是个例子。

我之前的一些公众号文章也有一些SQL是需要hint来优化的。尤其在很多谓词条件复杂或者关联条件复杂的情况下，靠统计信息是没有办法对行源进行准确估值的，这个时候就必须借助hint来帮助优化器制定一个良好的执行计划。作为一个高级DBA或是开发人员，hint还是需要掌握的。