|
在Oralce的搜索中，如果where条件句后使用了is null搜索条件，那么即使该列上使用了索引，Oracle也不会进行索引搜索。那么，这条规则一定正确吗？ 构建实验环境 准备数据环境，构建数据表。 SQL> create table t as select object_id,object_name,owner from dba_objects; Table created //设置一些object_id为null的情况 SQL> update t set object_id=null where wner='PERFSTAT'; 139 rows updated SQL> commit; Commit complete SQL> select count(*) from t; COUNT(*) ---------- 52806 //构建单值索引 SQL> create index idx_t_id on t(object_id); Index created //收集统计信息 SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all indexed columns'); PL/SQL procedure successfully completed SQL> select * from dba_segments where segment_name='IDX_T_ID'; //索引：空间120个数据块，0.98MB。 SEGMENT_NAME SEGMENT_TYPE HEADER_BLOCK BYTES BLOCKS EXTENTS -------------- ------------- ------------ ---------- ---------- ---------- IDX_T_ID INDEX 66057 983040 120 15 在这种情况下，使用索引列的is null条件，查询是不会走索引的。 SQL> select * from t where object_id is null; 已选择139行。 已用时间: 00: 00: 00.06 执行计划 ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 139 | 4726 | 68 (3)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 139 | 4726 | 68 (3)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID" IS NULL) 统计信息 ---------------------------------------------------------- 128 recursive calls 0 db block gets 323 consistent gets 0 physical reads 0 redo size 5416 bytes sent via SQL*Net to client 484 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 139 rows processed 实验说明，在有索引的情况下，使用is null的搜索语句是不会走索引路径的。 分析原因 这种情况出现的原因，本质上是因为oracle索引在生成过程中，是不会为索引列为null的数据行建索引，使之成为索引树的叶节点。更深层的原因，笔者认为是和null在oracle中，进行比较操作的过程中，表达式取值永远为null有关。 那么，对那些索引列为空的数据行，索引树上是没有对应的叶节点记录的，也就无从谈起借助索引进行搜索。 有一种思路，认为如果是构建组合索引，也就是将两个或两个以上列作为索引列，进行单列is null检索的时候，是可能会走索引的。原理很简单，对数据行来说，只有所有的索引列都是空的数据行，才不会被加入到索引叶节点。只要有一个索引列有值（或者定义其不能为空），就可以通过索引获取到。 在laoxiong老师的博客中，学到了一种思路，既然索引列集合需要有非空值才能进入索引叶节点，那么可以利用常量进行索引构建。 常量组合索引 构建组合索引。 SQL> drop index idx_t_id; Index dropped SQL> create index idx_t_cmp1 on t(object_id,0); //找一个常量值0，构建索引； Index created SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all indexed columns'); PL/SQL procedure successfully completed 此时，索引数据字典。 SQL> select * from dba_segments where segment_name='IDX_T_CMP1'; //发现组合索引的体积扩大很多，达到了256个块，2MB大小。 SEGMENT_NAME SEGMENT_TYPE HEADER_BLOCK BYTES BLOCKS EXTENTS ------------- ------------------ ------------ ---------- ---------- ---------- IDX_T_CMP1 INDEX 66057 2097152 256 17 进行搜索is null。 SQL> select * from t where object_id is null; 已选择139行。 已用时间: 00: 00: 00.03 执行计划 ---------------------------------------------------------- Plan hash value: 3648234756 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 139 | 4726 | 3 (0)|00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 139 | 4726 | 3 (0)|00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_CMP1 | 139 | | 2 (0)|00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID" IS NULL) 统计信息 ---------------------------------------------------------- 139 recursive calls 0 db block gets 49 consistent gets 0 physical reads 0 redo size 5416 bytes sent via SQL*Net to client 484 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 139 rows processed 结论：虽然使用了is null，但是查询还是选择了索引作为执行路径。相比较全表扫描的方法。对比如下。
对比项目中，发现性能提升比较明显。 利弊分析 使用索引是我们优化性能的一个重要手段。is null因为其特殊性，是不能纳入到优化范畴的。借助本文中的常数组合索引，可以实现is null查询的索引，同时兼顾原有取值查询的索引路径。 但是，这种方法是存在一定折中的，就是索引体积的变化。从上面的例子看出，使用常量组合索引之后，索引的体积扩大了一倍。这种扩大主要是对常量值叶占位和空行信息加入上。 实际应用中，也要考虑全表中空值的数量比例关系，选择合适的情景进行设置。 此外，还要注意索引列的长度体积。如果是数据值类型比较小的列，效果较好。如果数据值都比较大，Oracle优化器在选择路径的时候，可能还是会选择全表扫描。 (责任编辑：IT) |