null值如何使用索引

SQL> create table as select * from all_object;SQL> alter table test modify object_name  null;Table altered.SQL> update test set object_name = null where object_id =10000;1 row updated.SQL> commit;----------------------创建普通的B*树索引SQL> create index idx_test_1 on test(object_name);Index created.SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true);PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST WHERE OBJECT_NAME IS NULL;Execution Plan----------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 97 | 308 (1)| 00:00:04 ||* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 308 (1)| 00:00:04 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_NAME" IS NULL)Statistics----------------------------------------------------------0 recursive calls0 db block gets1074 consistent gets0 physical reads0 redo size1597 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed　------------------------删除idx_test_1,创建复合索引idx_test_2SQL> drop index idx_test_1;Index dropped.SQL> create index idx_test_2 on test(object_name,1);Index created.SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true);PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST WHERE OBJECT_NAME IS NULL;Execution Plan----------------------------------------------------------Plan hash value: 620435891------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 100 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 100 | 4 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TEST_2 | 1 | | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("OBJECT_NAME" IS NULL)Statistics----------------------------------------------------------0 recursive calls0 db block gets4 consistent gets0 physical reads0 redo size1597 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed---------------------删除idx_test_2,创建函数索引idx_test3SQL> drop index idx_test_2;Index dropped.SQL> create index idxx_test_3 on test(nvl(object_name,'xxoo'));Index created.SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true);PL/SQL procedure successfully completed.SQL> select * from test where nvl(object_name,'xxoo')='xxoo';;Execution Plan----------------------------------------------------------Plan hash value: 2118693821-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 242 | 4 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 242 | 4 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDXX_TEST_3 | 2 | | 3 (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access(NVL("OBJECT_NAME",'xxoo')='xxoo')Statistics----------------------------------------------------------0 recursive calls0 db block gets5 consistent gets0 physical reads0 redo size1597 bytes sent via SQL*Net to client524 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed

总结:普通B*树索引不存储null值,所以无法对 is null条件使用普通B*树索引,要使用索引,有两种方法

1:复合索引,

2:函数索引,

从以上两个测试来看,使用复合索引逻辑读为4,函数索引逻辑读为5,且函数nvl可能需要额外的CPU开销,至于为什么函数索引要比复合索引多一个逻辑读,这个暂时不知道,有待研究.

至于复合索引会增加额外的存储空间,对于现在的存储成本,2个字节(常数1)的空间,完全可以接受.