oracle索引空字段,一文搞懂Oracle中索引和空值的恩恩怨怨

在日常的工作中，空值总是有特殊的身份，它和索引之间也是“恩怨”颇多。

(1)有时候创建索引会因为空值出现一些奇怪的结果。

(2)有时候一个简单的查询因为空值却走不了索引。

我们来简单地模拟一下这些问题。

首先创建一个空表，注意，对于ID列我们是加了NOT NULL约束的。

SQL> create table index_test(id number not null,name varchar2(30) ) ;

Table created.

创建一个唯一性索引，包含了ID和name列。

SQL> create unique index inx_test on index_test(id,name);

Index created.

这个时候我们对表index_test插入数据。因为name列没有非空约束，所以可以为空。注意第一条insert语句，如果插入空串也会作为NULL来处理。

SQL> insert into index_test values(2,'');

1 row created.

SQL> insert into index_test values(1,'a');

1 row created.

SQL> insert into index_test values(3,null);

1 row created.

收集一下统计信息。

exec dbms_stats.gather_table_stats(user,'INDEX_TEST');

查看查询的执行计划，如下。

SQL> select *from index_test where id is not null;

Execution Plan

----------------------------------------------------------

Plan hash value: 4273605835

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 |

| 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

对于这个查询可能没什么感觉，走了全索引扫描。我们在查询条件中添加了id is not null的条件，其实ID列已经存在非空约束了，所以这个过滤条件可有可无。

我们来看看不加过滤条件的情况，还是走了全索引扫描，如下。

SQL> select *from index_test;

Execution Plan

----------------------------------------------------------

Plan hash value: 4273605835

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 |

| 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

取消ID列的非空约束。

SQL> alter table index_test modify(id number null);

Table altered.

再次查询执行计划，发现走了全表扫描。

SQL> select *from index_test;

Execution Plan

----------------------------------------------------------

Plan hash value: 356488860

-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 12 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| INDEX_TEST | 3 | 12 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

添加非空的过滤条件，又可以走索引了。

SQL> select *from index_test where id is not null;

Execution Plan

----------------------------------------------------------

Plan hash value: 4273605835

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 |

|* 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID" IS NOT NULL)

那么问题来了，索引对于空值好像总是有些特殊，下面来看看空值在索引中的一些细节。

SQL> set autot off

这个时候表index_test的索引还是唯一性索引，尝试插入一些值来对比一下。

如果插入name列为NULL，可以正常插入。

SQL> insert into index_test values(1,null);

1 row created.

如果插入ID列为NULL，也可以正常插入。因为我们取消了ID列的非空约束。

SQL> insert into index_test values(null,1);

1 row created.

插入ID、name列为NULL，这个时候竟然可以正常插入。

SQL> insert into index_test values(null,null);

1 row created.

再尝试一条，竟然还可以正常插入。

SQL> insert into index_test values(null,null);

1 row created.

我们再来试试空串的情况，发现结果和NULL是一致的，都可以插入。

SQL> insert into index_test values('','');

1 row created.

SQL> insert into index_test values('','');

1 row created.

简单分析一下索引，来看看空值在索引中的存储情况。

SQL> analyze index inx_test validate structure;

Index analyzed.

首先来看index_test中的数据情况，因为有些行存在空值，把rownum打印出来方便查看。

SQL> select rownum,id,name from index_test;

ROWNUM ID NAME

---------- ---------- ------------------------------

1 2

2 1 a

3 3

4 1

5 1

6

7

8

9

9 rows selected.

一共有9行数据，索引只有5行，最后4行都不在索引中。这也就基本能够说明为什么上面的查询条件中id is NOT NULL有时候走索引，有时候又不走索引。

至于为什么可以成功插入ID、name列为空的行，是因为对于Oracle来说，(NULL，NULL)和(NULL，NULL)是不同的，NULL值总是介于一种很模糊的状态。

SQL> select name,lf_rows from index_stats;

NAME LF_ROWS

------------------------------ ----------

INX_TEST 5

对表index_test中的数据进行统计，NULL值的统计结果是包含了NULL和空串。

SQL> select id,name,count(*)from index_test group by id,name ;

ID NAME COUNT(*)

---------- ------------------------------ ----------

1 1

4

1 a 1

3 1

2 1

1 1

6 rows selected.

此外，在平时的工作中，如果需要对某个表创建索引，就需要考虑NULL值的情况，为了使得索引能够正常启用，我们需要索引列中至少有一列存在非空约束。

就如下面的情况，我们已经存在唯一性索引，但是因为b树索引不会存储NULL的条目，所以对表中已有的空值就需要使用全表扫描。

SQL> set autot trace exp

SQL> select id,name from index_test;

Execution Plan

----------------------------------------------------------

Plan hash value: 356488860

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 12 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| INDEX_TEST | 3 | 12 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

如果需要输出非空的数据，加入is NOT NULL的过滤条件，索引就能够正常启用。

select id,name from index_test where id is not null

Execution Plan

----------------------------------------------------------

Plan hash value: 4273605835

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 |

|* 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID" IS NOT NULL)

小结： Oracle中的空值会影响索引扫描，会出现一些潜在的性能问题，在表设计中，字段如果可以设置为非空，就绝对不要使用null值。

举报/反馈