SQL中where条件的过滤字段有索引并且遇到以下情况：
in是可以走索引的；
not in可以走索引
is null 不可以走索引；
is not null 可以走索引
exists 可以走索引；
not exists不可以走索引；
以下为测试场景： 1、创建测试数据创建了三个表：demo、test、demo_testcreate table demo as select * from dba_objects;create index idx_id_01 on demo(object_id);表demo的object_id字段有索引 create table test as select * from dba_objects where rownum <=100; create table demo_not (id int,name varchar2(10));insert into demo_not select level id,'a' from dual connect by level <= 100;insert into demo_not select level id,'b' from dual connect by level <= 1000;insert into demo_not select level id,'c' from dual connect by level <= 1000;insert into demo_not select level id,'d' from dual connect by level <= 1000;insert into demo_not select level id,'e' from dual connect by level <= 1000; create index idx_name on demo_not(name); 2、测试不同场景1、测试in是否走索引 SQL> select object_id,object_name from demo where object_id in(111,222); Execution Plan ---------------------------------------------------------- Plan hash value: 1664213925 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 1106 | 9 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| DEMO | 14 | 1106 | 9 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_ID_01 | 337 | | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=111 OR "OBJECT_ID"=222) Note ----- - dynamic sampling used for this statement (level=2) 使用in可以走索引 2、测试not in是否走索引 SQL> select name from demo_not where name not in('b','c','d','e'); 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1371489473 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 700 | 5 (0)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| IDX_NAME | 100 | 700 | 5 (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NAME"<>'b' AND "NAME"<>'c' AND "NAME"<>'d' AND "NAME"<>'e') Note ----- - dynamic sampling used for this statement (level=2) not in可以走索引，不过只能走索引快速全扫描 3、 测试is null是否走索引 SQL> select object_id,object_name from demo where object_id < 100 or object_id is null; 98 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4000794843 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 1106 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| DEMO | 14 | 1106 | 347 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NULL OR "OBJECT_ID"<100) Note ----- - dynamic sampling used for this statement (level=2) is null走不了索引 4、 测试is not null是否走索引 SQL> select object_id,object_name from demo where object_id <1000 and object_id is not null; 997 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 607115961 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 997 | 78763 | 18 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 997 | 78763 | 18 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID_01 | 997 | | 4 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<1000) Note ----- - dynamic sampling used for this statement (level=2) 可以走索引 5、 测试exists是否走索引 SELECT OBJECT_ID ,OBJECT_NAMEFROM DEMOWHERE EXISTS (SELECT 1 FROM TEST WHERE DEMO.OBJECT_ID = TEST.OBJECT_ID) Execution Plan ---------------------------------------------------------- Plan hash value: 657191359 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 100 | 9200 | 104 (1)| 00:00:02 | | 1 | NESTED LOOPS | | 100 | 9200 | 104 (1)| 00:00:02 | | 2 | NESTED LOOPS | | 100 | 9200 | 104 (1)| 00:00:02 | | 3 | SORT UNIQUE | | 100 | 1300 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | TEST | 100 | 1300 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_ID_01 | 1 | | 1 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 79 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("DEMO"."OBJECT_ID"="TEST"."OBJECT_ID") Note ----- - dynamic sampling used for this statement (level=2) exists可以走索引 6、 测试not exists是否走索引 SQL> SELECT OBJECT_ID 2 ,OBJECT_NAME 3 FROM DEMO 4 WHERE NOT EXISTS 5 (SELECT 1 FROM TEST WHERE DEMO.OBJECT_ID = TEST.OBJECT_ID); 86909 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 254465291 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 98856 | 8881K| 350 (1)| 00:00:05 | |* 1 | HASH JOIN RIGHT ANTI| | 98856 | 8881K| 350 (1)| 00:00:05 | | 2 | TABLE ACCESS FULL | TEST | 100 | 1300 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | DEMO | 98856 | 7626K| 347 (1)| 00:00:05 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEMO"."OBJECT_ID"="TEST"."OBJECT_ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 7039 consistent gets 0 physical reads 0 redo size 3738694 bytes sent via SQL*Net to client 64246 bytes received via SQL*Net from client 5795 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 86909 rows processed 总的来说：in是可以走索引的；not in可以走索引（索引快速全扫描）is null 不可以走索引；is not null 可以走索引exists 可以走索引；not exists不可以走索引；
