in list查詢計算cost

wei-xh發表於2011-12-27
plan for
  2  select /*+ index(wxh_tbd) */ object_name from wxh_tbd where object_id =:1;
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
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 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=TO_NUMBER(:1))
14 rows selected.
plan for
  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
----------------------------------------------------------------------------------------
| 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))
16 rows selected.
(92-32)/2 from dual;
 (92-32)/2
----------
        30
可以看到in list列表為3的時候，索引部分的cost值為92.也就是其餘兩個繫結值索引部分的cost為30.少了blevel部分。我想這主要是ORACLE在計算in list查詢的時候，考慮到了cache的影響

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-713998/，如需轉載，請註明出處，否則將追究法律責任。

相關文章