ITPub博客

首页 > Linux操作系统 > Linux操作系统 > bitmap to rowid执行计划下的基数计算疑问

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

原创 Linux操作系统 作者:wei-xh 时间:2011-06-14 12:09:08 0 删除 编辑

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

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315051