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

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

20 rows selected.

很奇怪!如果access 没有使用函数,执行计划可以走INDEX RANGE SCAN (MIN/MAX),而如果查询使用nvl(flag,'1')='1',执行计划是INDEX RANGE SCAN.

3.做10053跟踪(注意为了要再次硬分析,我修改max=>Max)

SQL> alter session set events '10053 trace name context forever';
Session altered.

SQL> select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1';
   MAX(ID)
----------
     99900

SQL> alter session set events '10053 trace name context off';

结果如下:

***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1'
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
  fro(0): flg=0 bjn=180883 hint_alias="T"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 1581 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table:  T  Alias:  T
    #Rows: 100000  #Blks:  1597  AvgRowLen:  109.00
Index Stats::
  Index: IF_T_FLAG_ID  Col#: 4 1
    LVLS: 1  #LB: 250  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 1564.00
  Index: I_T_FLAG_ID  Col#: 2 1
    LVLS: 1  #LB: 250  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 1564.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#4): SYS_NC00004$(VARCHAR2)
    AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
  Column (#2): FLAG(CHARACTER)
    AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
  Table:  T  Alias: T
    Card: Original: 100000  Rounded: 50000  Computed: 50000.00  Non Adjusted: 50000.00
  Access Path: TableScan
    Cost:  352.86  Resp: 352.86  Degree: 0
      Cost_io: 351.00  Cost_cpu: 35372940
      Resp_io: 351.00  Resp_cpu: 35372940
  Access Path: index (index (FFS))
    Index: IF_T_FLAG_ID
    resc_io: 56.00  resc_cpu: 20780360
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  57.10  Resp: 57.10  Degree: 1
      Cost_io: 56.00  Cost_cpu: 20780360
      Resp_io: 56.00  Resp_cpu: 20780360
  Access Path: index (Min/Max)
    Index: IF_T_FLAG_ID
    resc_io: 2.00  resc_cpu: 14443
    ix_sel: 2.0000e-05  ix_sel_with_filters: 2.0000e-05
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: IF_T_FLAG_ID
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 50000.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:   T[T]#0
***********************
Best so far: Table#: 0  cost: 2.0008  card: 50000.0000  bytes: 350000
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
Final - All Rows Plan:  Best join order: 1
  Cost: 2.0008  Degree: 1  Card: 50000.0000  Bytes: 350000
  Resc: 2.0008  Resc_io: 2.0000  Resc_cpu: 14443
  Resp: 2.0008  Resp_io: 2.0000  Resc_cpu: 14443
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT MAX("T"."ID") "MAX(ID)" FROM "SCOTT"."T" "T" WHERE NVL("T"."FLAG",'1')='1'
kkoqbc-end
          : call(in-use=28984, alloc=32712), compile(in-use=42728, alloc=46360)
apadrv-end: call(in-use=28984, alloc=32712), compile(in-use=43512, alloc=46360)

sql_id=dh6rg0tp6jsvf.
Current SQL statement for this session:
select /*+ gather_plan_statistics */ Max(id) from t where nvl(flag,'1')='1'

============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id  | Operation          | Name        | Rows  | Bytes | Cost  | Time      |
-----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |             |       |       |     2 |           |
| 1   |  SORT AGGREGATE    |             |     1 |     7 |       |           |
| 2   |   INDEX RANGE SCAN | IF_T_FLAG_ID|   49K |  342K |     2 |  00:00:01 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T"."SYS_NC00004$"='1')

Content of other_xml column
===========================
  db_version     : 10.2.0.3
  parse_schema   : SCOTT
  plan_hash      : 2113784624
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" "IF_T_FLAG_ID")
    END_OUTLINE_DATA
  */

--感觉10g下这个版本有bug,选择的如下,cost=2 
  Access Path: index (Min/Max)
    Index: IF_T_FLAG_ID
    resc_io: 2.00  resc_cpu: 14443
    ix_sel: 2.0000e-05  ix_sel_with_filters: 2.0000e-05
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: IF_T_FLAG_ID
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 50000.00  Bytes: 0

但是执行计划却是INDEX RANGE SCAN,而不是INDEX RANGE SCAN (MIN/MAX).但是cost=2也说明问题.

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

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  |              |       |       |   129 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IF_T_FLAG_ID | 50492 |   345K|   129   (1)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00004$"='1')
20 rows selected.

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 /*+ 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 | 50440 |   344K|     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SYS_NC00004$"='1')
20 rows selected.

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 1
-------------------------------------
select /*+ gather_plan_statistics */ max(id) from t where nvl(flag,'1')='1'
Plan hash value: 4032029066
--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |       |       |    57 (100)|          |
|   1 |  SORT AGGREGATE       |              |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IF_T_ID_FLAG | 50000 |   341K|    57   (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."SYS_NC00004$"='1')
19 rows selected.

--可以发现虽然使用索引IF_T_ID_FLAG,但是走的是INDEX FAST FULL SCAN.
--看来这个版本有问题.

 

时间： 2024-10-23 18:22:58

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

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

1.建立测试例子:SQL> select * from v$version ; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - ProductionCORE    10.2.0.4.0      ProductionTNS

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

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