ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120201][补充]函数索引与取max值的问题1.txt

[20120201][补充]函数索引与取max值的问题1.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-01 08:25:42 0 删除 编辑
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')

--确实可以!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-715390/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2349
  • 访问量
    6091483