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

问题描述

我曾经认为当其中一个列值为null时，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屋！