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