bitmap to rowid执行计划下的基数计算疑问

最新推荐文章于 2021-04-15 08:57:19 发布

转载最新推荐文章于 2021-04-15 08:57:19 发布 · 90 阅读

本文通过具体案例探讨了Oracle数据库中不同索引的基数计算问题，特别是针对ACTOR和TASK_ORGPATH字段的索引扫描结果进行了深入分析，并讨论了实际返回基数与预期之间的差异。

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

------------------------------------------------------------------------------
| 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-697874/