a few days ago,jianxia call me to help her a strange execute plan.

The sql statement is like that:

select count(*)

  from par_autoupdown_log a

 where QiyeBianma = '000000000005912310001121'

 and LogTime >= TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

 and logtime<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss');

talbe “par_autoupdown_log” is partition table.ervry partition has 300W-400W rows.and the partition key is “logtime”.the distinct number of logtime in a partition is 200W-300W.

table “par_autoupdown_log” has been analyzed.so as we know,the statement above must be scand by index. But ,actually,it is scand by table access full.so ,why?

First,let’s see the explan plan for the statement:

----------------------------------------------------------------------------------------

SQL_ID  5njbp2nx1bhtb, child number 0

-------------------------------------

select /*+ gather_plan_statistics*/count(*) from par_autoupdown_log where      QiyeBianma =

'000000000005912310001121' and                     LogTime >= TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd

hh24:mi:ss')                    and logtime<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

Plan hash value: 4139055448

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

-----------------------------------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE         |                    |      1 |      1 |      1 |00:00:01.14 |    7419 |   5095 |

|   2 |   PARTITION RANGE SINGLE|                    |      1 |   1652 |  14444 |00:00:00.06 |    7419 |   5095 |

|*  3 |    TABLE ACCESS FULL    | PAR_AUTOUPDOWN_LOG |      1 |   1652 |  14444 |00:00:00.04 |    7419 |   5095 |

-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("QIYEBIANMA"='000000000005912310001121' AND "LOGTIME"<=TO_DATE('2010-10-20 00:00:00',

              'yyyy-mm-dd hh24:mi:ss') AND "LOGTIME">=TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))

       ------------------------------------------------------------------------------------------

And ,if we enforce it using index by hint.the explan plan will be like that:

------------------------------------------------------------------------------------------------

SQL_ID  8s123p6mfacu9, child number 0

-------------------------------------

select /*+ gather_plan_statistics index(a IDX_AUTOUPDOWNLOG_LOGTIME)*/count(*) from par_autoupdown_log a where    

 QiyeBianma = '000000000005912310001121' and                     LogTime >= TO_DATE('2010-10-19 00:00:00',

'yyyy-mm-dd hh24:mi:ss')                    and logtime<=TO_DATE('2010-10-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

Plan hash value: 2653469445

---------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE                     |                           |      1 |      1 |      1 |00:00:00.14 |   11537 |

|   2 |   PARTITION RANGE SINGLE            |                           |      1 |   1652 |  14444 |00:00:00.27 |   11537 |

|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| PAR_AUTOUPDOWN_LOG        |      1 |   1652 |  14444 |00:00:00.25 |   11537 |

|*  4 |     INDEX RANGE SCAN                | IDX_AUTOUPDOWNLOG_LOGTIME |      1 |  19170 |  37571 |00:00:00.04 |     212 |

---------------------------------------------------------------------------------------------------------------------------

 19170

 29848

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("QIYEBIANMA"='000000000005912310001121')

   4 - access("LOGTIME">=TO_DATE('2010-10-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND

You see that “table access full ” consume only 7419 buffers compared whih 11537 “index range scan”.that’s why cbo use “table access full”. But,according to the data distribution of the logtime,we know that “index range scan” must be more better.

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: PAR_AUTOUPDOWN_LOG  Alias:  A  Partition [2]

    #Rows: 746204  #Blks:  7535  AvgRowLen:  66.00

    #Rows: 746204  #Blks:  7535  AvgRowLen:  66.00

Index Stats::

  Index: CON_AUTOUPDOWNLOG_ID  Col#: 1

    LVLS: 2  #LB: 4965  #DK: 1606749  LB/K: 1.00  DB/K: 1.00  CLUF: 401697.00

  Index: IDX_AUTOUPDOWNLOG_LOGTIME  Col#: 3  PARTITION [2]

    LVLS: 2  #LB: 4120  #DK: 379660  LB/K: 1.00  DB/K: 1.00  CLUF: 204455.00

    LVLS: 2  #LB: 4120  #DK: 379660  LB/K: 1.00  DB/K: 1.00  CLUF: 204455.00

***************************************

SINGLE TABLE ACCESS PATH

  Column (#4): QIYEBIANMA(VARCHAR2)  Part#: 2

    AvgLen: 24.00 NDV: 25 Nulls: 0 Density: 0.04

  Column (#4): QIYEBIANMA(VARCHAR2)

    AvgLen: 24.00 NDV: 25 Nulls: 0 Density: 0.04

  Column (#3): LOGTIME(DATE)  Part#: 2

    AvgLen: 8.00 NDV: 379660 Nulls: 0 Density: 3.7598e-006 Min: 2455471 Max: 2455502

    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255

  Column (#3): LOGTIME(DATE)

    AvgLen: 8.00 NDV: 379660 Nulls: 0 Density: 3.7598e-006 Min: 2455471 Max: 2455502

    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255

  Table: PAR_AUTOUPDOWN_LOG  Alias: A    

    Card: Original: 746204  Rounded: 1313  Computed: 1312.78  Non Adjusted: 1312.78

  Access Path: TableScan

    Cost:  1692.91  Resp: 1692.91  Degree: 0

      Cost_io: 1650.00  Cost_cpu: 249695308

      Resp_io: 1650.00  Resp_cpu: 249695308

  Access Path: index (RangeScan)

    Index: IDX_AUTOUPDOWNLOG_LOGTIME

    resc_io: 9177.00  resc_cpu: 56876023

    ix_sel: 0.043982  ix_sel_with_filters: 0.043982

    Cost: 9186.77  Resp: 9186.77  Degree: 1

  Best:: AccessPath: TableScan

According to the <>,we know that the algorithm of “cost” by “index range scan” is like that:

Cost=LVLS+ ix_sel* LB(leaf block)+ ix_sel_with_filters* CLUF

So ,why  “index range scan” is more inefficient than “table access full”.the answer is clus of the index.

So ,I suggest jianxia to recreate partition by ordering by the logtime column.and the culs will low down.
    ------------------------------------------------------------------

英文写的有点 不尽如人意 的地方，很多话没办法说清楚，故用中文补充点：

我们知道，以  logtime 的数据分布来说（包括列的信息和直方图的信息），是一定需要走索引的，那分析过后，为什么还没走索引呢？这时，其实我们就需要考虑到是否是 索引的 CLUF 过高导致的，当时我以为 执行计划 在执行和通过 explain plan 时是不一样的，所以，拼命地 通过  /*+ gather_plan_statistics*/ 去看真实的执行计划，结果还是一样。