[20120201][補充]函數索引與取max值的問題1.txt

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';

   MAX(ID)

----------

     99900

--确實可以!