Oracle Null系列（三）

null 和执行计划

总结：至少要保证查询的记录要建立索引，才能使用索引

1.单列唯一索引

1. 由于null值是不被建立索引，所以当使用id is null作为谓词时，走了全表扫描。
   null索引链接： null 和索引
   

2. 因为id列不为空的记录都建立了索引。所以当使用 id is not null 作为谓词时，此时执行计划中走了索引全扫描（当数据量较大时，就会全表扫描，因为通过索引获取可能性能更低）。
   

2.复合唯一索引

1. 对于复合唯一索引的情形，当使用单列谓词时，使用 is null 与 is not null 等同于单列唯一索引的情形， is null走全表扫描而 is not null走索引。
   
   
   
   

2. 对于复合唯一索引的情形，当使用所以索引列谓词时，同时不为空（and）走索引，其他情况都不走索引。

• id is null and name is null
  

• id is null or name is null
  

• id is not null and name is not null
  

• id is not null or name is not null
  

3.is null 走索引

3.1 添加 not null 约束

1. 索引列添加 not null 约束，单列索引任然不走索引
   

按理来说（索引列有not null 约束，此列肯定没有null值），不应该走全部扫描，直接返回空（但人家没按照我的道理走）

2. 组合索引中，第一个字段没有出现在where语句中，此时走复合索引（id列有not null 约束，保证了复合索引给表的所有行建立了索引）
   

3.2 通过函数索引来使得is null使用索引

create unique index ipk_study$f_id on study(nvl(id,-1));

3.3 使用伪列创建基于函数的索引来使得is null使用索引

create unique index ipk_study$r_id on study(id,-1);

3.4 总结

    无论是单列唯一索引或复合唯一索引，对于可以为null的列或复合null值，Oracle不会为其存储索引值。故在基于单列创建B树唯一索引或多列创建B树复合唯一索引的情形下，
当列上允许为null值时：

• where子句使用了基于is null的情形，其执行计划走全表扫描。
• where子句使用了基于is not null的情形，其执行计划走索引扫描。

当列上不允许为null值时，存在非null约束：

• where子句使用了基于is null的情行，其执行计划走索引扫描。
• where子句使用了基于is not null的情形，其执行计划走索引扫描。

Null与索引使用时的建议：

1. 对于用于连接或经常被谓词使用到的列应尽可能避免NULL值属性，因为它容易导致索引失效。
2. 为需要使用NULL值的列添加缺省值(alter table tb modify(col default ‘Y’))。
3. 如果NULL值不可避免也不能使用缺省值，应考虑为该常用列使用nvl函数创建索引，或使用伪列来创建索引以提高查询性能。
4. 对于复合索引应保证索引中至少有一列不为NULL值，还是因为全部列为NULL时不被索引存储，以保证使用is null是可以使用索引。
5. 对于复合索引应保证索引列应使用数据类型长度最小的列来添加not null约束应节省磁盘空间。