ORACLE中空值查询的索引设计

文章目录

第一章 空值查询概述 1

第二章 不同设计的实验对比 1

2.1 索引应用场景 2

2.2 访问效率对比 2

2.3 比较索引体积 3

2.4 DML语句对索引的影响 3

2.4.1 更新 3

2.4.2 插入 4

2.4.3 删除 6

第三章 总结 7

空值查询概述

在应用SQL的编写过程中，经常会涉及到空值的查询。而空值一般情况下是不会记录到索引中的。因此：常规的索引设计中，即使空值查询列上存在索引。那大概率也是无法通过索引完成过滤数据的。造成的结果就是低效查询或者全表扫描等问题。

而实际场景中，尽管查询列为空，但满足为空记录的数据也是较少的，如果能设计出一种包含空值的索引，则可以避免上述低效查询甚至是全表扫描的问题。经过个人的经验总结，通常为了满足空值查询的索引设计，包含如下两类索引：

1. 查询列+常量列建立组合索引;
2. 通过函数(如DECODE)将空值列处理为一个常量值。

经过总结：上述两类索引有如下特性：

1. 函数DECODE索引由于只存储空值，其体积较小。查询时消耗的资源较小。其对DML语句的影响也是最小的；但对应的要求修改语句代码。
2. 查询列+常量列与传统普通索引的表现一致。不论体积、资源消耗还是对DML语句的影响，都偏高。但其好处是除了记录了空值外，还记录了索引列本身的值。可以满足空值和普通值查询的多种查询场景。

不同设计的实验对比

为了对比上述两类索引设计的优缺点和差异。设计了如下脚本

实验脚本：

--表结构

create table t1 as select * from dba_objects;

--索引设计

CREATE INDEX SZT.IDX_T1_VALUE ON T1(STATUS,'NA');

CREATE INDEX SZT.IDX_T1_DECODE ON T1(DECODE(STATUS,'','NA'));

数据分布情况如下：

如下，分别从索引大小、索引的应用场景、访问效率、以及对DML类语句的影响。来分析差异：

索引应用场景

由于不同的索引设计，其主要应用的场景也不同。

1. 函数DECODE索引由于只记录了空值查询的部分，其余非空列并没有存储。因此只能对空值做查询处理。且需要在代码中完整的使用函数部分查询。

SELECT * FROM T1 WHERE DECODE(STATUS,'','NA')='NA';

1. 查询列+常量列的方式。由于直接将常量列包含进组合索引中，保证了查询列的空值部分数据也会被存储。因此可以直接对索引列做普通查询或空值查询。

SELECT * FROM T1 WHERE STATUS is null;

SELECT * FROM T1 WHERE STATUS ='INVALID';

访问效率对比

1.函数DECODE索引：

SELECT * FROM T1 WHERE DECODE(STATUS,'','NA')='NA';

执行计划如下：

2.常量列组合索引：

SELECT * FROM T1 WHERE STATUS is null;

SELECT * FROM T1 WHERE STATUS ='INVALID';

执行计划如下：

比较索引体积

SELECT SEGMENT_NAME,BLOCKS,BLOCKS*8 size_kb FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'IDX_T1_%';

总结：

由于函数索引只记录了空值部分，其体积相对正常索引较小。

DML语句对索引的影响

更新

测试语句：

UPDATE /*+ full(t1) */T1 SET STATUS='INVALID';

通过相同的语句，分别测试无索引、函数DECODE索引、常量列组合索引三种索引的更新效率：

无索引：

函数DECODE索引：

查询列+常量列索引：

总结：由于函数DECODE中只记录了空值的部分，其余大部分索引列都没有记录到索引中，因此其对更新语句的影响很小。与无索引的情况十分接近。

插入

测试语句：

INSERT INTO T1 SELECT * FROM T2;

通过相同的语句，分别测试无索引、函数DECODE索引、常量列组合索引三种索引的插入效率：

无索引：

函数DECODE索引：

查询列+常量列索引：

总结：由于函数DECODE中只记录了空值的部分，其余大部分索引列都没有记录到索引中，因此其对插入语句的影响很小。与无索引的情况十分接近。

删除

测试语句：

DELETE FROM T1;

通过相同的语句，分别测试无索引、函数DECODE索引、常量列组合索引三种索引的插入效率：

无索引：

函数DECODE索引：

查询列+常量列索引：

总结：删除场景下，仍然是存储数据较少的函数DECODE索引表现良好。传统的普通索引无论是全表删除还是索引扫删除。其消耗的逻辑读次数都是偏高的。

总结

实际环境中，要根据业务特性来进行索引的设计。

如表上DML操作较为频繁，则需要考虑创建索引对DML的影响。将影响降到最低。同时其索引特性需要改写查询逻辑。

如果空值查询和普通值查询均有大量的查询场景，则可以考虑查询列+常量列的普通索引。