SQL中where条件的过滤字段有索引并且遇到以下情况：

        in是可以走索引的；

        not in可以走索引

        is null 不可以走索引；

        is not null 可以走索引

        exists 可以走索引；

        not exists不可以走索引；

以下为测试场景：

1、创建测试数据

创建了三个表：demo、test、demo_test

create 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_NAME

FROM   DEMO

WHERE  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不可以走索引；