select id, email, status
from test a
where a.id = #Id#
and a.gmt_created = (select max(b.gmt_created)
from test b
where b.id = #Id#);

这个sql对于开发工程师来说，是比较常见的，意思是求最近的一个记录，该索引创建成(id,gmt_created)组合索引，但这样写有两个问题，一是对于索引来讲，需要扫描两次，一次子查询中的聚合扫描，一次范围扫描，第二个问题比较严重，有可能两条记录的gmt_created是一样的，结果集可能是多条，那程序可能就会出错。。。

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 92 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 |
| 3 | SORT AGGREGATE | | 1 | 27 | |
| 4 | FIRST ROW | | 1 | 27 | 1 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| IDX_TEST_ID |1 |
--------------------------------------------------------------------------

改进后的sql如下所示：

select *
from (select id, email, status
from test a
where a.id = :1
order by a.gmt_created desc)
where rownum = 1;

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 83 | 1 |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | 83 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID | TEST | 1 | 92 | 1 |
|* 4 | INDEX RANGE SCAN DESCENDING| IDX_TEST_ID |
--------------------------------------------------------------------------

利用索引逆向扫描，即避免了2次索引扫描，也避免了结果集多于2条的情况。。。