一个同事问起如果获取一个数据列最大取值所在的一行记录，感觉是一个有趣的问题。一般看起来很简单的问题，解法可能有多样，我们选择的路径可能有多种方式。
环境准备
原始问题是：获取数据表中指定一个数据列中最大的日期记录，一条即可。如此，我们利用dba_objects的last_ddl_time数据列来构建实验环境。
SQL> create table t (id number(10), da date);
Table created
SQL> insert into t select object_id, last_ddl_time from dba_objects;
50351 rows inserted
SQL> commit;
Commit complete
获取最大last_ddl_time列对应的记录，我们可以使用多种方法。
利用rownum筛选最大数据集合
想到只需要一条最大的记录，只管想到了rownum。利用rownum伪劣，可以进行删选结果的剔除。（这个问题在笔者之前的系列中有所涉及，可以参考http://space.itpub.net/17203031/viewspace-687124）
设计的查询和结果。
SQL> select * from (select * from t order by da desc) where rownum<2;
IDS DA
----------- -----------
53876 2011-3-11 2
此种方法的执行效率方面，是如果呢？我们通过autotrace工具进行查看。
SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from (select * from t order by da desc) where rownum<2;
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 3299198703
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 43 (5)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 50351 | 1081K| 43 (5)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 50351 | 590K| 43 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 50351 | 590K| 43 (5)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
3 - filter(ROWNUM<2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
157 consistent gets
0 physical reads
0 redo size
465 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
从执行计划中，我们看出几个问题。首先在没有索引的情况下，查询整体的效率还是可以接受的（5万余条纪录消耗0.07s）。这个显然是通过stopkey操作，将不必要的排序和返回列加以屏蔽的结果。性能最大的消耗是进行全表扫描和排序（存在一次memory内部的排序），同时存在一些逻辑读情况。
这种方法存在一些缺陷，是需要注意的。首先，如果da列存在重复值，而需求要求返回最大的多列时，这样rownum的取定就存在问题了。同时，方法中的FTS，将来也会是一个比较大的缺陷方面。即使在da列加入了索引，这部分FTS的负担也较难进行优化。
利用子查询实现功能
思路：先利用子查询获取到取值最大的具体值，再进行查询。这样的方法比较中规中矩，但是能够解决rownum方案的缺陷。
SQL> select * from t where da=(select max(da) from t );
IDS DA
----------- -----------
53876 2011-3-11 2
SQL> select * from t where da=(select max(da) from t );
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1287471683
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88 | 1056 | 86 (5)| 00:00:02 |
|* 1 | TABLE ACCESS FULL | T | 88 | 1056 | 43 (5)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 8 | | |
| 3 | TABLE ACCESS FULL| T | 50351 | 393K| 43 (5)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DA"= (SELECT MAX("DA") FROM "T" "T"))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
315 consistent gets
0 physical reads
0 redo size
465 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这种方法虽然没有用到很高级的语法和技巧，但是还是获取的很好的性能效率。首先，执行时间较使用rownum有所缩短（只有0.02s）。其次就是排序操作sort被避免，因为sort操作会大量消耗PGA的空间。
唯一缺点就是进行逻辑读的数量较高，达到了315。那么，有无优化的空间呢？可以借助索引进行优化。
//建立索引
SQL> create index idx_t_da on t(da);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
//进行查询
SQL> select * from t where da=(select max(da) from t );
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3678432891
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87 | 1044 | 46 (5)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 87 | 1044 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_DA | 87 | | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 8 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_T_DA | 50351 | 393K| | |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DA"= (SELECT MAX("DA") FROM "T" "T"))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
465 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在数据表T的da列上，我们加入了normal类型的索引idx_t_da。这样在进行max值获取的过程中，Oracle是有选择不进行FTS操作来获取最大值，而是直接读取索引树的两端叶节点。
从结果看，这是我们目前取得的最好执行路径。首先，执行时间缩短为0.01s，消除了没有索引方案时进行FTS的瓶颈。其次，没有进行sort操作占用过大的排序空间。最后是大幅度减少了逻辑读数量，只有5（没有索引时要消耗315），提升近60倍。
这里，我们注意到Oracle提供了一种操作“INDEX FULL SCAN (MIN/MAX)”，相当于只对于索引树进行两端搜索，获取到最大值/最小值的rowid之后，就立刻停止。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17203031/viewspace-689312/ ，如需转载，请注明出处，否则将追究法律责任。
