[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 <= 99900
                 THEN '1'
              ELSE '0'
           END flag, LPAD ('a', 100, 'a') vc
      FROM DUAL
CONNECT BY LEVEL <= 100000;

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

--確實可以!