bitmap to rowid執行計劃下的基數計算疑問

wei-xh發表於2011-06-14

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章