null的研究（第二发）-CSDN博客

第二发主要是null与索引

还记得刚刚工作的时候，有一个很简单的sql
查询一个表的数据，根据一个字段排序，这个字段有索引，但是没有任何约束，也没有null值
但是呢，相同场景再现如下：
SQL> select * from emp where rownum<3 order by hiredate;

Execution Plan
----------------------------------------------------------
Plan hash value: 691404987

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 76 | 3 (34)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 2 | 76 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
有索引的，为啥不走索引呢？
原因是没有非空约束，
SQL> select count(*) from emp where hiredate is null;

COUNT(*)
----------
0
看数据的话，确实是没有null值，但是，oracle不知道，它得扫描完才知道是不是有空值。所以加个非空约束或者is not null就好了；

SQL> select * from emp where rownum<3 and hiredate is not null order by hiredate;

Execution Plan
----------------------------------------------------------
Plan hash value: 2115277132

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 76 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | I | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
当初写sql都写不出来，被这个苦恼了半天，至今记忆犹新。

下面开始本文正题。

null,是不进入索引的，但是这里说的索引，是指一般的b－tree单列索引，像函数索引，位图索引啥的，还是能够存储null的，
另外，复合索引不能存储全null的，但是只要有一列不为null，就可以进入索引。

单列索引的上面例子有了，直接进入复合索引部分。
创建测试表，数据，索引

SQL> create table test (ename varchar2(32),sal number);

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> insert into test values(2,3);

1 row created.

SQL> commit;

Commit complete.

SQL> create index i on test(ename,sal);

Index created.

下一步：收集统计信息

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.
查询索引相关信息如下：
SQL> select index_name,num_rows from user_indexes where index_name = 'I';

INDEX_NAME NUM_ROWS
-------------------- ----------
I 3

可以发现，我明明插入了4条数据，但是统计信息中只有3条，说明全null的数据复合索引也是不存的，但是，只要有一列有数据，那么这条数据都可以进索引。

SQL> set null null
SQL> select * from test;
ENAME SAL
-------------------------------- ----------
null null
1 null
null 1
2 3
可以发现，表中有全空的数据，下面进行查询测试：

SQL> select * from test where ename is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 2 | 62 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
可以发现，走的方式为全表扫描。
SQL> delete from test where ename is null and sal is null;

1 rows deleted.

SQL> commit;
Commit complete.

SQL> select * from test;

ENAME SAL
-------------------------------- ----------
1 null
null 1
2 3
现在表中数据没有全空的了，但是再次执行：

SQL> select * from test where ename is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 2 | 62 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
如上：还是全表扫描

－－对于复合索引来说，全是null，或者两列都没有非空约束，也是不会进入索引的。
下面，演示的是有字段为null（不全为null），的查询走索引

SQL> create table test (ename varchar2(32),sal number not null);
SQL> create index i on test(ename,sal);
SQL> insert into test values(null,1);
SQL> insert into test values(1,2);
SQL> insert into test values(2,3);
SQL> commit;

SQL> exec dbms_stats.gather_schema_stats('TEST');

PL/SQL procedure successfully completed.

SQL> select * from test where ename is null;

ENAME SAL
-------------------------------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 706391979

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I | 1 | 31 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

针对null，建立伪列复合索引：
如果有一列，null非常少，但是呢，又需要查询这一列，同时还不能建立两个字段的复合索引（因为索引是影响insert update delete的），可以建立一个假列的索引；
如下：

SQL> create table test (ename varchar2(32),sal number);
SQL> insert into test values(null,1);
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into test values(i,i);
4 end loop;
5 end;
6 /
SQL> create index i on test(ename,1);

SQL> select ename from test where ename is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 706391979

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I | 1 | 18 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30123160/viewspace-2057165/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2057165/