oracle select max min 优化,select min(id),max(id) from table 该如何优化 ~

SQL>  select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

OracleDatabase11gEnterprise Edition Release 11.2.0.1.0 - Production

SQL>  create table test as select * from dba_objects;

Table created.

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

顺便打个广告，大量的SQL优化案例：

http://blog.csdn.net/robinson1988/article/category/621201