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

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

--确实可以!

上一篇:Edge浏览器的ChakraCore javaScript引擎已开源一周年


下一篇:无重复最长字串