Oracle 何时索引空列值? [英] When does Oracle index null column values?

问题描述

我曾经认为，当其中一个列值为空时，Oracle 不会索引行.

I used to think that Oracle does not index a row when one of the column values is null.

一些简单的实验表明情况并非如此.即使某些列可以为空，我也能够意外地运行一些仅访问索引的查询(这当然是一个惊喜).

Some simple experimentation shows this to be not the case. I was able to run some queries unexpectedly accessing only indexes even though some columns were nullable (which of course was a pleasant surprise).

谷歌搜索导致一些博客的答案相互矛盾:我读过除非所有索引列都为空，否则一行被索引，并且除非前导列，一行被索引索引的值为空.

A Google search led to some blogs with conflicting answers: I have read that a row gets indexed unless all indexed columns are null, and also that a row gets indexed unless the leading column value for the index is null.

那么，在什么情况下一行不进入索引?这个 Oracle 版本是特定的吗?

So, in what cases does a row not enter an index? Is this Oracle version specific?

推荐答案

如果任何索引列包含非空值，该行将被索引.正如您在下面的示例中所看到的，只有一行没有被索引，而且这行在两个索引列中都为 NULL.您还可以看到，当前导索引列具有 NULL 值时，Oracle 肯定会索引一行.

If any indexed column contains a non-null value that row will be indexed. As you can see in the following example only one row doesn't get indexed and that's the row which has NULL in both indexed columns. You can also see that Oracle definitely does index a row when the leading index column has a NULL value.

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 上运行.但我非常有信心它适用于所有版本.

This example run on Oracle 11.1.0.6. But I'm pretty confident it holds true for all versions.

这篇关于Oracle 何时索引空列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！