查询表中最新纪录-减少表扫描量

 今天开发给了一条sql，查询表中最近更新的一条记录：

       select id,
       type,
       object_id,
       nick,
       isv_id,
       next_auditor_role,
       status,
       apply_parameters,
       comments,
       gmt_create,
       gmt_modified
      from (select id,
                type,
                object_id,
                nick,
                isv_id,
                next_auditor_role,
                status,
                apply_parameters,
                comments,
                gmt_create,
                gmt_modified
          from tops_apply
          where object_id = 552
          and status = 1
          order by gmt_create desc)
 where rownum = 1

一看这条sql就有优化的余地，内层查询查询了大量无用的数据。

2.创建索引：
17:02:07 SQL> create index ind_tops_apply_oid_sts_create1 on tops_apply(object_id,status,gmt_create)
17:02:30   2  tablespace tbs_top_ind
17:02:30   3  compute statistics;

3.执行sql
17:02:31 SQL> select id,
17:02:42   2         type,
17:02:42   3         object_id,
17:02:42   4         nick,
17:02:42   5         isv_id,
17:02:42   6         next_auditor_role,
17:02:42   7         status,
17:02:42   8         apply_parameters,
17:02:42   9         comments,
17:02:42  10         gmt_create,
17:02:42  11         gmt_modified
17:02:42  12    from (select id,
17:02:42  13                 type,
17:02:42  14                 object_id,
17:02:42  15                 nick,
17:02:42  16                 isv_id,
17:02:42  17                 next_auditor_role,
17:02:42  18                 status,
17:02:42  19                 apply_parameters,
17:02:42  20                 comments,
17:02:42  21                 gmt_create,
17:02:42  22                 gmt_modified
17:02:42  23            from tops_apply
17:02:42  24           where object_id = 552
17:02:42  25             and status = 1
17:02:42  26           order by gmt_create desc)
17:02:42  27   where rownum = 1;
 
         13  consistent gets
  
4.查询有13个逻辑读,改写sql，换成rowid形式来取结果：
17:02:44 SQL>  select id,
17:02:59   2          type,
17:02:59   3          object_id,
17:02:59   4          nick,
17:02:59   5          isv_id,
17:02:59   6          next_auditor_role,
17:02:59   7          status,
17:02:59   8          apply_parameters,
17:02:59   9          comments,
17:02:59  10          gmt_create,
17:02:59  11          gmt_modified
17:02:59  12     from tops_apply t, (select rid from (select rowid rid
17:02:59  13             from tops_apply
17:02:59  14            where object_id = 552
17:02:59  15              and status = 1
17:02:59  16            order by gmt_create desc) where rownum<2)tt where tt.rid=t.rowid;

 
          3  consistent gets

5.改写查询后，只有3个逻辑读，但是一个很简单的查询，但写出来的sql有些复杂，能不能有办法改进一下；在执行计划中看到了：
 INDEX RANGE SCAN DESCENDING| IND_TOPS_APPLY_OID_STS_CREATE1
 oracle很聪明的在order by gmt_create desc的时候对索引进行了descending扫描，在仔细想一想这条sql的目的是找出表中的最新记录， 而索引在创建的时候是有序的，这样就可以利用索引的有序性来查找出结果，oracle也只需要扫描索引的第一行（descending建索引）或者索引最后一行（默认 ASCEDING建索引 ），改写sql：

A.select id,
               type,
               object_id,
               nick,
               isv_id,
               next_auditor_role,
               status,
               apply_parameters,
               comments,
               gmt_create,
               gmt_modified
          from tops_apply
          where object_id = 552
          and status = 1 and rownum<2 order by gmt_create desc ;
         (index(object_id,status,gmt_create))
          执行计划:INDEX RANGE SCAN DESCENDING)
B.select id,
               type,
               object_id,
               nick,
               isv_id,
               next_auditor_role,
               status,
               apply_parameters,
               comments,
               gmt_create,
               gmt_modified
    from tops_apply
    where object_id = 552 and status = 1  and rownum<2;
    (index(object_id,status,gmt_create desc))
    执行计划:INDEX RANGE SCAN)