联系：QQ(5163721)
标题：让“select min(xxx),max(xxx) from xxx”走索引
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
首先，做一个测试表：
[oracle@lunar /]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 7 21:29:52 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Welcome Lunar's oracle world! Love you , baby ! Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options SYS@lunar>conn lunar/lunar Welcome Lunar's oracle world! Love you , baby ! Connected. LUNAR@lunar>create table lunar as select * from dba_extents; Table created. Elapsed: 00:00:04.61
在extent_id列上创建非唯一索引：
LUNAR@lunar>create index idx_lunar_extent_id on lunar(extent_id) ; Index created. Elapsed: 00:00:00.02 LUNAR@lunar>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LUNAR',TABNAME => 'LUNAR',DEGREE => 5, 2 CASCADE => TRUE,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE); PL/SQL procedure successfully completed. Elapsed: 00:00:01.45
下面的语句没有走索引：
LUNAR@lunar>select min(extent_id),max(extent_id) from lunar;
MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
0 61
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 291688323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| LUNAR | 5223 | 15669 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
51 consistent gets
0 physical reads
0 redo size
610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
手工指定IDX_LUNAR_EXTENT_ID时，也不走索引：
LUNAR@lunar>select /*+ index(lunar IDX_LUNAR_EXTENT_ID) */ min(extent_id),max(extent_id) from lunar;
MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
0 61
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 291688323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| LUNAR | 5223 | 15669 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
50 consistent gets
0 physical reads
0 redo size
610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
指定该列为非空时，可以走索引（Oracle的索引不保存空值）：
LUNAR@lunar>alter table lunar modify extent_id not null; Table altered. Elapsed: 00:00:00.24 LUNAR@lunar>
可以看到，无论是否使用hint都走索引了：
LUNAR@lunar>set autotrace on
LUNAR@lunar>select /*+ index(lunar IDX_LUNAR_EXTENT_ID) */ min(extent_id),max(extent_id) from lunar;
MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
0 61
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1897601393
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN| IDX_LUNAR_EXTENT_ID | 5223 | 15669 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
注意： 使用hint时，走的是“INDEX FULL SCAN”
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>select min(extent_id),max(extent_id) from lunar;
MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
0 61
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2695027278
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FAST FULL SCAN| IDX_LUNAR_EXTENT_ID | 5223 | 15669 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
注意： 不使用hint时，走的是“INDEX FAST FULL SCAN”
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
再次验证，依然这个结果：
LUNAR@lunar>alter table lunar modify extent_id null;
Table altered.
Elapsed: 00:00:00.01
LUNAR@lunar>select min(extent_id),max(extent_id) from lunar;
MIN(EXTENT_ID) MAX(EXTENT_ID)
-------------- --------------
0 61
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 291688323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| LUNAR | 5223 | 15669 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
75 consistent gets
0 physical reads
0 redo size
610 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
单独查询最小值或者最大值时，也可以走索引，采用“INDEX FULL SCAN (MIN/MAX)”：
LUNAR@lunar>select min(extent_id) from lunar;
MIN(EXTENT_ID)
--------------
0
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4158629481
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID | 1 | 3 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>select max(extent_id) from lunar;
MAX(EXTENT_ID)
--------------
61
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4158629481
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID | 1 | 3 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
532 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
假设，在不能修改表结构的情况下，怎么才能让“select min(extent_id),max(extent_id) from lunar”走索引呢？
参考了网上的一些建议，改写sql如下：
LUNAR@lunar>select
2 (select min(extent_id) from lunar) as min_extent_id,
3 (select max(extent_id) from lunar) as max_extent_id
4 from dual;
MIN_EXTENT_ID MAX_EXTENT_ID
------------- -------------
0 61
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 778639039
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID | 1 | 3 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 3 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_LUNAR_EXTENT_ID | 1 | 3 | 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
608 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
