原来对于索引的认识只知道索引可以基于一个或者多个列，B-Tree索引不包含null，但有些情况下我们又需要通过where 列名 is null来查找一些数据，这时候数据库由于没办法使用索引就会使用全表扫描，导致执行效率低下，这时候我们可以通过使用含常数的复合索引来解决这个问题。
下面开始进行实验：
首先建立测试表
SYS@ORCL>create table test_objects nologging as select rownum id,a.* from dba_objects a where 1=2;
Table created.
插入500万条数据：
SYS@ORCL>declare
l_cnt number;
l_rows number:=&1;
begin
insert /*+ append */ into test_objects select rownum,a.* from dba_objects a;
l_cnt:=sql%rowcount;
commit;
while(l_cnt loop insert /*+ append */ into test_objects select rownum+l_cnt, owner,object_name,subobject_name, object_id,data_object_id, object_type,created,last_ddl_time, timestamp,status,temporary, generated,secondary from test_objects where rownum<=l_rows-l_cnt; l_cnt:=l_cnt+sql%rowcount; commit; end loop; end; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 / Enter value for 1: 5000000 old 3: l_rows number:=&1; new 3: l_rows number:=5000000; PL/SQL procedure successfully completed. 在object_id列上建立一般的B-Tree索引： SYS@ORCL>create index idx_oid_test_objects on test_objects(object_id); Index created. 收集表信息： SYS@ORCL>exec dbms_stats.gather_table_stats('SYS','TEST_OBJECTS'); PL/SQL procedure successfully completed. 测试一下索引能否正常使用： SYS@ORCL>select count(*) from test_objects where object_id=52457; COUNT(*) ---------- 99 Execution Plan ---------------------------------------------------------- Plan hash value: 3877533889 -------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------- | 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | INDEX RANGE SCAN| IDX_OID_TEST_OBJECTS | 99 | 495 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- ---------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=52457) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 411 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 可以看到索引可以正常使用，下面我们来进入正题，当where条件中为object_id is null的时候会如何： SYS@ORCL>select count(*) from test_objects where object_id is null; COUNT(*) ---------- 0 Execution Plan ---------------------------------------------------------- Plan hash value: 3799704240 -------------------------------------------------------------------------------- --- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- --- | 0 | SELECT STATEMENT | | 1 | 5 | 16612 (2)| 00:03:2 0 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TEST_OBJECTS | 1 | 5 | 16612 (2)| 00:03:2 0 | -------------------------------------------------------------------------------- --- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID" IS NULL) Statistics -----------------------------------------------------