10g优化器对in的操作有所改进，ms可以用到索引。-优快云博客

首先声明一下：我做dba不久，经验十分有限，技术贴不敢发，怎奈手痒，这篇测试如果有不当之处，请达人尽快指出，以免误导别人

看到网上有篇优化sql的文章说：
“oracle在处理in的时候是转换成or进行处理的，如：
a in (1,2)相当于 a=1 or a=2
而对于b*tree索引来说，对于or操作是不会用到索引的。”

上面这段话估计是针对10g以前的版本，因为我没在9i上做过试验，所以不确定，在10g中可能oracle对优化器又作了改进，使得in操作可以用到索引，以下是我的测试：

SQL> create table test2
  2  as
  3  select object_id oid,object_name oname,owner,created,status from all_objects;

表已创建。

SQL> alter table test2 add constraint pk_test2_oid primary key (oid);

表已更改。

SQL> select * from test2 where oid in (1,2,3,4);

执行计划
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |             | 24 |  1464 | 32 |
| 1 |  INLIST ITERATOR          |             |    |    |    |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2       | 24 |  1464 | 32 |
| 3 | INDEX RANGE SCAN       | PK_TEST2_OID | 297 |    |    5 |
-----------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


SQL> select * from test2 where oname in ('TEST1','TEST2');--没建索引

执行计划
----------------------------------------------------------

-----------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    6 | 366 | 113 |
| 1 |  TABLE ACCESS FULL| TEST2 |    6 | 366 | 113 |
-----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version



SQL> create index ind_test2_oname on test2(oname);

索引已创建。

SQL> select * from test2 where oname in ('TEST1','TEST2');

执行计划
----------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                |    6 | 366 | 110 |
| 1 |  INLIST ITERATOR          |                |    |    |    |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2          |    6 | 366 | 110 |
| 3 | INDEX RANGE SCAN       | IND_TEST2_ONAME | 297 |    |    4 |
--------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

顺便说一下，like操作似乎没有改变，只有在like 'xx%'，即% 放在最后的时候，才会用到索引，如下：


SQL> select * from test2 where oname like '%TEST%';

已选择39行。

执行计划
----------------------------------------------------------

-----------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 24 |  1464 | 113 |
| 1 |  TABLE ACCESS FULL| TEST2 | 24 |  1464 | 113 |
-----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

SQL> select * from test2 where oname like '%TEST2';

执行计划
----------------------------------------------------------

-----------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    6 | 366 | 113 |
| 1 |  TABLE ACCESS FULL| TEST2 |    6 | 366 | 113 |
-----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version



SQL> select * from test2 where oname like 'TEST%';

已选择8行。

执行计划
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                |    8 | 488 |    7 |
| 1 |  TABLE ACCESS BY INDEX ROWID| TEST2          |    8 | 488 |    7 |
| 2 | INDEX RANGE SCAN       | IND_TEST2_ONAME |    8 |    |    3 |
-------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/310745/viewspace-6309/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/310745/viewspace-6309/