同时查询最大最小值的效率问题
有时候我们需要同时查询一部分数据中的最大和最小值，如何才能最快取得我们要的结果呢？
我们知道索引已经对数据进行了排序，所以这时走索引效率是最高的，下面看一个例子
--在一个有将近两百万数据的表中创建日期索引，需要同时取出日期的最大值和最小值：
SQL> SELECT MIN, MAX 2 FROM (SELECT MIN(created) AS MIN FROM user_objects_tmp) a, 3 (SELECT MAX(created) AS MAX FROM user_objects_tmp) b;Elapsed: 00:00:00.10Execution Plan----------------------Plan hash value: 4210122108-------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 18 | 3437 (4)| 00:00:42 || 1 | NESTED LOOPS | | 1 | 18 | 3437 (4)| 00:00:42 || 2 | VIEW | | 1 | 9 | 1718 (4)| 00:00:21 || 3 | SORT AGGREGATE | | 1 | 9 | | || 4 | INDEX FULL SCAN (MIN/MAX)| USER_OBJECTS_CREATE_DT | 1600K| 13M| | || 5 | VIEW | | 1 | 9 | 1718 (4)| 00:00:21 || 6 | SORT AGGREGATE | | 1 | 9 | | || 7 | INDEX FULL SCAN (MIN/MAX)| USER_OBJECTS_CREATE_DT | 1600K| 13M| | |-------------------------------------------------------------------Note----- - dynamic sampling used for this statementStatistics---------------------- 195 recursive calls 0 db block gets 159 consistent gets 72 physical reads 0 redo size 472 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed--可见，这种情况效率是最高的SELECT MIN, MAXFROM (SELECT MIN(created) AS MIN FROM user_objects_tmp) a, (SELECT MAX(created) AS MAX FROM user_objects_tmp) b;
