当 where 条件中 含有 <> 条件或者 not in 时 走INDEX FAST FULL SCAN 执行计划中的 限制条件 not in 转换为 <>
where 条件中含有 = 号 或in 时 走INDEX RANGE SCAN 注意执行计划中的限制条件 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("OBJECT_ID"<>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("OBJECT_ID"<>45 AND "OBJECT_ID"<>65 AND "OBJECT_ID"<>95
AND "OBJECT_ID"<>32 AND "OBJECT_ID"<>64 AND "OBJECT_ID"<>83 AND
"OBJECT_ID"<>5 AND "OBJECT_ID"<>2 AND "OBJECT_ID"<>1)
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)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22664653/viewspace-672908/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
【性能优化】查询绑定变量的sql语句
请登录后发表评论
登录
全部评论
