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 calls
0 db block gets
1074 consistent gets
0 physical reads
0 redo size
1597 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
　
------------------------删除idx_test_1,创建复合索引idx_test_2
SQL> 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 calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1597 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---------------------删除idx_test_2,创建函数索引idx_test3
SQL> 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 calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1597 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

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

1:复合索引,

2:函数索引,

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

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