SELECT MIN(ID),MAX(ID) FROM TABLE 优化问题-优快云博客

2011/07/20到支付宝面试，被问及一个问题 SELECT MIN(ID),MAX(ID) FROM TABLE 如何优化

好的现在来做个实验：

SQL> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL> create table test as select * from dba_objects; Table created. SQL> alter table test modify object_id not null; Table altered.

SQL> create index i_object_id on test(object_id); Index created.SQL> select max(object_id),min(object_id) from test; MAX(OBJECT_ID) MIN(OBJECT_ID) -------------- -------------- 74644 2 Elapsed: 00:00:00.34 Execution Plan ---------------------------------------------------------- Plan hash value: 1751978921 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 37 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FAST FULL SCAN| I_OBJECT_ID | 72860 | 355K| 37 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 167 consistent gets 145 physical reads 0 redo size 501 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 可以看到CBO选择了 INDEX FAST FULL SCAN,有145个物理读，167个逻辑读，现在改写SQL如下

SQL> select (select min(object_id) min_id from test a),(select max(object_id) max_id from test b) from dual;

(SELECTMIN(OBJECT_ID)MIN_IDFROMTESTA) (SELECTMAX(OBJECT_ID)MAX_IDFROMTESTB)
------------------------------------- -------------------------------------
                                    2                                 74644
                                    
Execution Plan
----------------------------------------------------------
Plan hash value: 4224666897

------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |       |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |             |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_OBJECT_ID |     1 |     5 |     2   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE            |             |     1 |     5 |            |          |
|   4 |   INDEX FULL SCAN (MIN/MAX)| I_OBJECT_ID |     1 |     5 |     2   (0)| 00:00:01 |
|   5 |  FAST DUAL                 |             |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        547  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到逻辑读降低为4，大家看看这个是不是最优化的方法？还有更进一步的方法优化吗？支付宝说这个案例很经典，可能是我水平太低了，只能想出这个方法

----update,我能想到的另外一种方法，不过逻辑读也是4---------------------SQL> select (select /*+ index_asc(test i_object_id) */ object_id from test where rownum=1) min ,(select /*+ index_desc(test i_object_id) */ object_id from test where rownum=1) max from dual; 2 MIN MAX ---------- ---------- 2 74644 Execution Plan ---------------------------------------------------------- Plan hash value: 91314419 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | INDEX FULL SCAN | I_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 | |* 3 | COUNT STOPKEY | | | | | | | 4 | INDEX FULL SCAN DESCENDING| I_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 3 - filter(ROWNUM=1) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 479 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed