【索引】反向索引--條件 範圍查詢（二）

當 where 條件中 含有 <> 條件或者 not in 時 走INDEX FAST FULL SCAN 執行計劃中的 限制條件 not in 轉換為 <>

PHP code:



SQL> select object_id from t1 where object_id <> 1;


53519 rows selected.





Execution Plan


----------------------------------------------------------


Plan hash value: 711836071


-----------------------------------------------------------------------------


| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |


-----------------------------------------------------------------------------


|   0 | SELECT STATEMENT     |      | 53518 |   261K|    29   (4)| 00:00:01 |


|*  1 |  INDEX FAST FULL SCAN| I_ID | 53518 |   261K|    29   (4)| 00:00:01 |


-----------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter(&quot;OBJECT_ID&quot;<>1)





Statistics


----------------------------------------------------------


          1  recursive calls


        。。。。。。


      53519  rows processed





SQL> select object_id from t1 where object_id not in (45,65,95,32,1,2,5,64,83);


53511 rows selected.


Execution Plan


----------------------------------------------------------


Plan hash value: 711836071


-----------------------------------------------------------------------------


| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |


-----------------------------------------------------------------------------


|   0 | SELECT STATEMENT     |      | 53510 |   261K|    31  (10)| 00:00:01 |


|*  1 |  INDEX FAST FULL SCAN| I_ID | 53510 |   261K|    31  (10)| 00:00:01 |


-----------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   1 - filter(&quot;OBJECT_ID&quot;<>45 AND &quot;OBJECT_ID&quot;<>65 AND &quot;OBJECT_ID&quot;<>95


              AND &quot;OBJECT_ID&quot;<>32 AND &quot;OBJECT_ID&quot;<>64 AND &quot;OBJECT_ID&quot;<>83 AND


              &quot;OBJECT_ID&quot;<>5 AND &quot;OBJECT_ID&quot;<>2 AND &quot;OBJECT_ID&quot;<>1)

where 條件中含有 = 號 或in 時  走INDEX RANGE SCAN 注意執行計劃中的限制條件 in 被轉換為 =

PHP code:



SQL> select object_id from t1 where object_id =55 or object_id =65;


Execution Plan


----------------------------------------------------------


Plan hash value: 3991740069


--------------------------------------------------------------------------


| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |


--------------------------------------------------------------------------


|   0 | SELECT STATEMENT  |      |     2 |    10 |     3   (0)| 00:00:01 |


|   1 |  INLIST ITERATOR  |      |       |       |            |          |


|*  2 |   INDEX RANGE SCAN| I_ID |     2 |    10 |     3   (0)| 00:00:01 |


--------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - access("OBJECT_ID"=55 OR "OBJECT_ID"=65)


Statistics


----------------------------------------------------------


          1  recursive calls


        。。。。。。


          2  row

PHP code:



SQL> select object_id from t1 where object_id in (45,65,95,32,1,2,5,64,83);


8 rows selected.





Execution Plan


----------------------------------------------------------


Plan hash value: 3991740069


--------------------------------------------------------------------------


| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |


--------------------------------------------------------------------------


|   0 | SELECT STATEMENT  |      |     9 |    45 |     9   (0)| 00:00:01 |


|   1 |  INLIST ITERATOR  |      |       |       |            |          |


|*  2 |   INDEX RANGE SCAN| I_ID |     9 |    45 |     9   (0)| 00:00:01 |


--------------------------------------------------------------------------


Predicate Information (identified by operation id):


---------------------------------------------------


   2 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=5 OR


              "OBJECT_ID"=32 OR "OBJECT_ID"=45 OR "OBJECT_ID"=64 OR "OBJECT_ID"=65 OR


              "OBJECT_ID"=83 OR "OBJECT_ID"=95)