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