我曾经认为当列值之一为空时，Oracle 不会索引行。
一些简单的实验表明情况并非如此。即使某些列可以为空，我也能够意外地运行一些仅访问索引的查询（这当然是一个惊喜）。
谷歌搜索导致一些博客的答案相互矛盾：我读到除非所有索引列都为空，否则一行会被索引，并且除非索引的前导列值为空，否则一行会被索引。
那么，在什么情况下一行不进入索引呢？这个 Oracle 版本是特定的吗？
如果任何索引列包含非空值，则该行将被索引。正如您在下面的示例中所看到的，只有一行没有被索引，而且这行在两个索引列中都为 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(*) ---------- 69238 SQL> 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(*) ---------- 69241 SQL> 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 ---------- 69240 SQL> set autotrace traceonly exp SQL> 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 上运行。但我非常有信心它适用于所有版本。
