本帖最后由 Nowhatcannot 于 2015-8-23 16:53 编辑
create index idx_object_id on t(object_id,object_type);
select /*+index(t,idx_object_id)*/ * from t where object_TYPE='TABLE' AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2940 |00:00:00.09 | 848 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4157 | 2940 |00:00:00.09 | 848 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | 364 | 2940 |00:00:00.02 | 466 |
-------------------------------------------------------------------------------------------------------
select /*+index(t,idx_object_id)*/ * from t t where object_TYPE='TABLE' AND OBJECT_ID IN (20,21);
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2940 |00:00:00.10 | 597 |
| 1 | INLIST ITERATOR | | 1 | | 2940 |00:00:00.10 | 597 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 4157 | 2940 |00:00:00.06 | 597 |
|* 3 | INDEX RANGE SCAN | IDX_OBJECT_ID | 2 | 1 | 2940 |00:00:00.02 | 215 |
--------------------------------------------------------------------------------------------------------
上面的两条语句中IN的写法要更加高效,逻辑读更少,大概是范围扫描的一半。
但是这里我想不明白,IN的写法中索引被扫描了2次,而范围写法索引被扫描了1次,那么为什么IN的写法的逻辑读还比范围的低很多呢,照理说扫描了2次,那么它的逻辑读应该比扫描一次多啊?至少也得多扫描一次ROOT/BRANCH吧。
NOTE:
T表通过CTAS取自DBA_OBJECTS
但是做过如下的更改
object_id>30000的全部为21
object_id<=30000的全部为20