如果任何索引列包含非空值，则该行将被索引。正如您在下面的示例中所看到的，只有一行没有被索引，而且这行在两个索引列中都为 NULL。您还可以看到，当前导索引列具有 NULL 值时，Oracle 肯定会索引一行。
SQL> create table big_table as 2 select object_id as pk_col 3 , object_name as col_1 4 , object_name as col_2 5 from all_objects 6 /Table created.SQL> select count(*) from big_table 2 / COUNT(*)---------- 69238SQL> insert into big_table values (9999990, null, null) 2 /1 row created.SQL> insert into big_table values (9999991, 'NEW COL 1', null) 2 /1 row created.SQL> insert into big_table values (9999992, null, 'NEW COL 2') 2 /1 row created.SQL> select count(*) from big_table 2 / COUNT(*)---------- 69241SQL> create index big_i on big_table(col_1, col_2) 2 /Index created.SQL> exec dbms_stats.gather_table_stats(user, 'BIG_TABLE', cascade=>TRUE)PL/SQL procedure successfully completed.SQL> select num_rows from user_indexes where index_name = 'BIG_I' 2 / NUM_ROWS---------- 69240SQL> set autotrace traceonly expSQL>SQL> select pk_col from big_table 2 where col_1 = 'NEW COL 1' 3 /Execution Plan----------------------------------------------------------Plan hash value: 1387873879-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECt STATEMENT || 2 | 60 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 2 | 60 | 4 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | BIG_I | 2 | | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("COL_1"='NEW COL 1')SQL> select pk_col from big_table 2 where col_2 = 'NEW COL 2' 3 /Execution Plan----------------------------------------------------------Plan hash value: 3993303771-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECt STATEMENT || 2 | 60 | 176 (1)| 00:00:03 ||* 1 | TABLE ACCESS FULL| BIG_TABLE | 2 | 60 | 176 (1)| 00:00:03 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("COL_2"='NEW COL 2')SQL> select pk_col from big_table 2 where col_1 is null 3 and col_2 = 'NEW COL 2' 4 /Execution Plan----------------------------------------------------------Plan hash value: 1387873879-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECt STATEMENT || 1 | 53 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 53 | 4 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | BIG_I | 2 | | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("COL_1" IS NULL AND "COL_2"='NEW COL 2') filter("COL_2"='NEW COL 2')SQL> select pk_col from big_table 2 where col_1 is null 3 and col_2 is null 4 /Execution Plan----------------------------------------------------------Plan hash value: 3993303771-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 1 | 53 | 176 (1)| 00:00:03 ||* 1 | TABLE ACCESS FULL| BIG_TABLE | 1 | 53 | 176 (1)| 00:00:03 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("COL_1" IS NULL AND "COL_2" IS NULL)SQL>此示例在 Oracle 11.1.0.6 上运行。但我非常有信心它适用于所有版本。
