sys@SMART>explain plan for
2 select /*+ index(wxh_tbd) */ object_name from wxh_tbd where object_id =:1;
2 select /*+ index(wxh_tbd) */ object_name from wxh_tbd where object_id =:1;
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3726906094
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3726906094
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3533 | 74193 | 212 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| WXH_TBD | 3533 | 74193 | 212 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | T_I | 3533 | | 32 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3533 | 74193 | 212 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| WXH_TBD | 3533 | 74193 | 212 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | T_I | 3533 | | 32 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:1))
14 rows selected.
sys@SMART>explain plan for
2 select /*+ index(wxh_tbd) */ object_name from wxh_tbd where object_id in (:1,:2,:3);
2 select /*+ index(wxh_tbd) */ object_name from wxh_tbd where object_id in (:1,:2,:3);
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1635227538
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1635227538
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10598 | 217K| 631 (0)| 00:00:08 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| WXH_TBD | 10598 | 217K| 631 (0)| 00:00:08 |
|* 3 | INDEX RANGE SCAN | T_I | 10598 | | 92 (0)| 00:00:02 |
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10598 | 217K| 631 (0)| 00:00:08 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| WXH_TBD | 10598 | 217K| 631 (0)| 00:00:08 |
|* 3 | INDEX RANGE SCAN | T_I | 10598 | | 92 (0)| 00:00:02 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
3 - access("OBJECT_ID"=TO_NUMBER(:1) OR "OBJECT_ID"=TO_NUMBER(:2) OR
"OBJECT_ID"=TO_NUMBER(:3))
"OBJECT_ID"=TO_NUMBER(:3))
16 rows selected.
sys@SMART>select (92-32)/2 from dual;
(92-32)/2
----------
30
----------
30
可以看到in list列表为3的时候，索引部分的cost值为92.也就是其余两个绑定值索引部分的cost为30.少了blevel部分。我想这主要是ORACLE在计算in list查询的时候，考虑到了cache的影响
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-713998/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
计算索引基数所采用的num_rows
下一篇：
rebuild索引，违背直觉得实验结果
请登录后发表评论
登录
全部评论
