explain plan for
select count(*) list_count,
        (case
          when a.node_category is null then
           a.node_name
          else
           a.node_category
        end) node_name,
        a.col2 name_alias,
        a.node_category
   from vodka.bpm_actinst a
  where a.is_deleted = 'n'
    and a.STATE not in (:1)
    and a.TASK_ORGPATH = :2
    and a.NODE_TYPE in (:3)
    AND (a.actor = :4)
    AND (arrange_type != :5 or arrange_type is null)
  group by a.col2, a.node_name, a.node_category;
-----------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |     1 |
|   1 |  HASH GROUP BY                    |                               |     1 |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | BPM_ACTINST                   |     1 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                               |       |
|   4 |     BITMAP AND                    |                               |       |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                               |       |
|*  6 |       INDEX RANGE SCAN            | BPM_ACT_INST_ACTOR_IND        |  1322 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|                               |       |
|*  8 |       INDEX RANGE SCAN            | BPM_ACT_INST_TASK_ORGPATH_IND |  1322 |
-----------------------------------------------------------------------------------

看到两个索引返回的基数都是1322.

对与actor上确实应该返回1322.

但是对于TASK_ORGPATH,不应该是这个基数。

------------actor

ops$adminCRMG>select 15283200-5450300 from dual;

15283200-5450300
----------------
         9832900

ops$adminCRMG>select 9832900/7436 from dual;

9832900/7436
------------
  1322.33728

------------TASK_ORGPATH

ops$adminCRMG>select 15283200-7307700 from dual;

15283200-7307700
----------------
         7975500

ops$adminCRMG>select 7975500/2004 from dual;

7975500/2004
------------
  3979.79042

explain plan for
 select count(*) list_count,
        (case
          when a.node_category is null then
           a.node_name
          else
           a.node_category
        end) node_name,
        a.col2 name_alias,
        a.node_category
   from vodka.bpm_actinst a
  where a.is_deleted = 'n'
    and a.STATE not in (:1)
    and a.TASK_ORGPATH = :2
    and a.NODE_TYPE in (:3)
    AND (arrange_type != :5 or arrange_type is null)
  group by a.col2, a.node_name, a.node_category;

------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |   147 |
|   1 |  HASH GROUP BY               |                               |   147 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| BPM_ACTINST                   |   147 |
|*  3 |    INDEX RANGE SCAN          | BPM_ACT_INST_TASK_ORGPATH_IND |  3980 |
------------------------------------------------------------------------------

单独去看，是对的。ORACLE越来越诡异的让我看不懂了

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