ITPub博客

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

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

原创 Linux操作系统 作者:lfree 时间:2012-01-31 15:52:02 0 删除 编辑
[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 <= 99900
                 THEN '1'
              ELSE '0'
           END flag, LPAD ('a', 100, 'a') vc
      FROM DUAL
CONNECT BY LEVEL <= 100000;

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.
--看来这个版本有问题.

 

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

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

注册时间:2008-01-03

  • 博文量
    2349
  • 访问量
    6092006