http://space.itpub.net/267265/viewspace-715315
在10.2.0.3下oracle执行时并没有选择INDEX RANGE SCAN (MIN/MAX).
补充测试:
1.建立测试例子:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
DROP table T purge;
CREATE TABLE T AS
SELECT ROWNUM id, CASE
WHEN ROWNUM THEN '1'
ELSE '0'
END flag, LPAD ('a', 100, 'a') vc
FROM DUAL
CONNECT BY LEVEL
create index i_t_id_flag on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);
2.看看是否索引,走INDEX RANGE SCAN (MIN/MAX).
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
SQL> select /*+ gather_plan_statistics */ max(id) from t where flag='1';
MAX(ID)
----------
99900
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a98amhwysv462, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where flag='1'
Plan hash value: 3080205269
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | FIRST ROW | | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T_ID_FLAG | 1 | 7 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='1')
--确实可以!