[20120131]函数索引与取max值的问题2.txt

1.建立测试例子:
SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

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 if_t_flag_id on t(nvl(flag,'1'),id);
create index i_t_flag_id on t(flag,id);
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);

2.执行测试命令:
SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';
   MAX(ID)
----------
     99900

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'

Plan hash value: 2113784624

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 |   341K|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00004$"='1')

20 rows selected.

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: 3307844215

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |             |     1 |     7 |            |          |
|   2 |   FIRST ROW                  |             | 50000 |   341K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| I_T_FLAG_ID | 50000 |   341K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FLAG"='1')

同样情况与10.2.0.3相同!如果access 没有使用函数,执行计划可以走INDEX RANGE SCAN (MIN/MAX),而如果查询使用nvl(flag,'1')='1',执行计划是INDEX RANGE SCAN.

3.做10053跟踪(注意为了要再次硬分析,我修改max=>Max)
--忽略.cost=2也说明分析成本的时候是走INDEX RANGE SCAN (MIN/MAX),而实际的执行计划是INDEX RANGE SCAN.

如果查询使用count(id),也能说明问题,在这样的情况下,同样走INDEX RANGE SCAN,cost=63.

SQL> select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from t where nvl(flag,'1')='1';

 COUNT(ID)
----------
     99900

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bqqa1y80prhwn, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t,if_t_flag_id) */ count(id) from
t where nvl(flag,'1')='1'

Plan hash value: 2113784624

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |    63 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 |   341K|    63   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00004$"='1')

4.在10g下如何优化上述sql语句呢?可以把索引反过来建立.

create index if_t_id_flag on t(id,nvl(flag,'1'));
exec SYS.DBMS_STATS.GATHER_TABLE_STATS (NULL,'T',Method_Opt=> 'FOR ALL COLUMNS SIZE 1 ',Cascade=> TRUE);

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where
nvl(flag,'1')='1'

Plan hash value: 2113784624

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IF_T_FLAG_ID | 50000 |   341K|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00004$"='1')

--情况依旧,删除索引在测试if_t_flag_id.(或者使用hint).

SQL> drop index if_t_flag_id;
Index dropped.

SQL> select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1';

   MAX(ID)
----------
     99900

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'cost')) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  a22py0tjbmutp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1'

Plan hash value: 2133598614

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE             |              |     1 |     7 |            |          |
|   2 |   FIRST ROW                 |              | 50036 |   342K|     2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50036 |   342K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T"."SYS_NC00004$"='1')

--对比10.2.0.3可以发现:
--使用索引IF_T_ID_FLAG,但是走的是INDEX FULL SCAN (MIN/MAX),实际上这个查询是从id的最大值开始只要满足nvl(flag,'1')='1'的条件停止.

--如果nvl(flag,'1')='1'的最大id很小,以上情况最坏的是扫描整个索引.

update t set flag='0' where id>=11;
commit;

SQL> set autot traceonly
SQL> select /*+ gather_plan_statistics index(t,if_t_id_flag) */ max(id) from t where nvl(flag,'1')='1';

执行计划
----------------------------------------------------------
Plan hash value: 2133598614

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |              |     1 |     7 |            |          |
|   2 |   FIRST ROW                 |              | 50000 |   341K|     2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| IF_T_ID_FLAG | 50000 |   341K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(NVL("FLAG",'1')='1')

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        378  consistent gets
          0  physical reads
       9220  redo size
        335  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--可以发现逻辑读很多达到了378. 看来这个方法仅仅在满足条件nvl(flag,'1')='1'最大id很大的情况下比较好.
--当然如果应该经常查询id=:b 的情况下,并且查询以上max(id)的逻辑读很小,不失为一个好的选择.

时间： 2024-10-13 11:32:06

[20120131]函数索引与取max值的问题2.txt的相关文章

[20120131]函数索引与取max值的问题3.txt

1.建立测试例子: SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE    11.

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

[20120130]函数索引与取max值的问题1.txt 1.建立测试例子:SQL> select * from v$version ;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64biPL/SQL Release 10.2.0.3.0 - ProductionCORE    10

[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 Editi

C++函数参数取默认值的深入详解_C 语言

一般情况下,在函数调用时形参从实参那里取得值,因此实参的个数应与形参相同.有时多次调用同一函数时用同样的实参,C++提供简单的处理办法,给形参一个默认值,这样形参就不必一定要从实参取值了.如有一函数声明float area(float r=6.5);指定r的默认值为6.5,如果在调用此函数时,确认r的值为6.5,则可以不必给出实参的值,如area( );  //相当于area(6.5);如果不想使形参取此默认值,则通过实参另行给出.如area(7.5); //形参得到的值为7.5,而不是6.5这

怎样用java实现 c#中Datarow[索引] 的取值的方式 ？

问题描述 怎样用java实现 c#中Datarow[索引] 的取值的方式 ? 怎样用java实现 c#中Datarow[索引] 的取值的方式 ?比如我有定义一个这样的数组:HashMap map=new HashMap<>();如何实现如下取值?map[""key""]=""值""; 解决方案 java不支持索引器,所以给出的解决方案就是使用方法代替(其实C#的索引器也是方法,只是写法上简化了点)比如map.get

Oracle之函数索引

Oracle之函数索引 在Oracle中,有一类特殊的索引,称为函数索引(Function-Based Indexes,FBI),它基于对表中列进行计算后的结果创建索引.函数索引在不修改应用程序的逻辑基础上提高了查询性能.如果没有函数索引,那么任何在列上执行了函数的查询都不能使用这个列的索引.当在查询中包含该函数时,数据库才会使用该函数索引.函数索引可以是一个B-Tree索引或位图索引. 用于生成索引的函数可以是算术表达式,也可以是一个包含SQL函数.用户定义PL/SQL函数.包函数,或C调用的

PHP函数索引（3）

函数|索引 mcrypt_ecb: 使用 ECB 将资料加/解密. mcrypt_get_block_size: 取得编码方式的区块大小. mcrypt_get_cipher_name: 取得编码方式的名称. mcrypt_get_key_size: 取得编码钥匙大小. mcrypt_ofb: 使用 OFB 将资料加/解密. md5: 计算字符串的 MD5 哈稀. mhash: 计算哈稀值. mhash_count: 取得哈稀 ID 的最大值. mhash_get_block_size: 取得哈

在Informix中创建并使用函数索引

随着数据量以惊人速度不断增长,数据库管理系统将继续关注性能问题.本文主要介绍一种名为函数索引(functional index)的性能调优技术.根据数据库使用情况的统计信息创建并使用函数索引,可以显著提升SELECT 查询的性能.通过本文了解如何在IBM Informix Dynamic Server 中创建和使用函数索引并最大限度提升查询性能. 简介 在选择数据库管理系统(DBMS)时,性能是一个关键的考虑因素.在执行SELECT.INSERT.UPDATE 和 DELETE 操作时,很多因素

JS取request值以及自动执行使用示例

 在网页中JS函数自动执行常用三种方法,下面为大家详细介绍下JS取request值以及自动执行使用,需要的朋友可以参考下 在网页中JS函数自动执行常用三种方法  在HTML中的Head区域中,有如下函数:  代码如下: <SCRIPT LANGUAGE="JavaScript">  functionn MyAutoRun()  {  //以下是您的函数的代码,请自行修改先!  alert("函数自动执行哦!");  }  </SCRIPT>