空与非空:浅谈非空约束的影响

黄玮(Fuyuncat)

资深Oracle DBA,个人网www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价.

非空约束是字段的一个重要属性。但是,很多时候,数据库表的设计人员似乎并不十分在意这个属性。最常见的现象就是,除了主键字段外,所有字段都不指定该属性。而在Oracle中,默认是允许为空。

而实际上,优化器在选择执行计划时,非空约束是一个重要的影响因素。为了说明问题,我们建立以下测试表,然后分别说明非空约束在各种情况下对执行计划和性能的影响。

谓词评估

在上面表中,字段SUBOBJECT_NAME中不存在空值,但也没有非空约束,再看以下查询,查找该字段的空值记录:

统计信息如下:

我们看到,需要对表进行全表扫描(关于索引,随后再讨论)。而如果我们加上非空约束,可以看到执行计划已经性能的变化:

统计信息如下:

注意到,在全表扫描之前,增加了一个filter,而fileter的表达式是NULL IS NOT NULL,其逻辑结果是FALSE,因此,实际上其子操作(全表扫描)并未执行。相应的,请性能数据里面CR为0。

之所以优化器会为执行计划增加这样一个filter,是因为优化器在做查询转换(Query Transformation)时,会将非空约束作为参照条件之一,对where子句的谓词做逻辑结果评估,如果评估结果为false,则会增加一个这样的filter,以避免执行一些高代价的操作。从10053跟踪文件中,可以看到这对于优化器对执行计划代价估算的影响:

非空约束对索引选择的影响

我们知道,Oracle中B*树索引中不存在空键值,即在表的数据记录中,如果索引中所有字段都为空,则该记录不会被构建到索引树中。也就是说,如果索引字段上没有非空约束,则表记录与索引记录不是完全映射的。

我们先去掉subobject_name上的非空约束,并在上面建立索引:

HELLODBA.COM>alter table t_test1 modify subobject_name null; Table altered. HELLODBA.COM>create index t_test1_subo_idx on t_test1(subobject_name) compute statistics; Index created.

执行以下语句,以获取subobject_name最小的10条记录。为了提高效率,我们希望直接从索引中直接读取前10条ROWID(索引数据已经按照subobject_name排序),然后根据ROWID获取数据记录:

我们来看执行计划

统计信息如下:

但是,查询计划和结果看,语句并没有按照设想的方式执行,得出的数据也不是我们需要的。其原因就在于,由于空值不被索引,优化器无法确认索引数据是否涵盖了所有数据记录,因而它没有选择指定索引。

我们把非空约束加上,执行计划和结果就符合我们的需求了。

我们来看执行计划

统计信息如下

非空约束对连接查询的影响

在进行数据关联时,数据集中关联字段是否存在空值也会影响优化器对执行计划的选择。我们再创建一张测试表。

再将subobject_name的非空约束去掉。

HELLODBA.COM>alter table t_test1 modify subobject_name null; Table altered.

我们通过以下语句查找t_test1中subobject_name不为table_name的数据:

统计信息如下:

可以看到,执行计划通过添加函数LNNVL和NOT EXISTS,对数据进行过滤得到结果,性能相当低。

注意:当逻辑表达是中的操作数可能为空时,LNNVL函数可以判断出该表达式的结果。

我们再把非空约束加上,

统计信息如下

可以看到执行计划通过ANTI-JOIN获取我们需要的数据,性能大为改善。

我们知道,对于空值的逻辑判断,只能通过IS NULL或IS NOT NULL进行判断,除此之外,一旦表达式中含有NULL值,结果始终为空。这也就是空值的存在不会影响JOIN或SEMI-JOIN的原因。

ANTI-JOIN,通过关联方法(NESTED-LOOP、MERGE、HASH)判断记录是否符合条件,一旦发现两边记录可以关联上,则判定不符合要求,可以放弃对一个数据集中的剩余数据的判断,因而能提高性能。而关联是同等价匹配(=)实现的,不适合空值数据。因此,关联字段可能存在空值时,无法采用ANTI-JOIN。

注意:无论是否存在空值,NOT EXISTS都可以采用ANTI-JOIN,但是它的逻辑结果与NOT IN并不等价。在以下例子中,NOT EXISTS和NOT IN的结果不相同:

统计信息如下

另外一种情况

统计信息如下:

事务回滚

HELLODBA.COM>rollback; Rollback complete.

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-08-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JetpropelledSnake

Django学习笔记之Queryset的高效使用

对象关系映射 (ORM) 使得与SQL数据库交互更为简单,不过也被认为效率不高,比原始的SQL要慢。

583
来自专栏xingoo, 一个梦想做发明家的程序员

cuda编程知识普及

本帖经过多方整理,大多来自各路书籍《GPGPU编程技术》《cuda高性能》 1 grid 和 block都可以用三元向量来表示: grid的数组元素是bloc...

2656
来自专栏刘望舒

Java并发编程(四)Java内存模型

相关文章 Java并发编程(一)线程定义、状态和属性 Java并发编程(二)同步 Java并发编程(三)volatile域 前言 此前我们讲到了线程、同步...

1736
来自专栏zhisheng

【死磕Java并发】—–Java内存模型之总结

经过四篇博客阐述,我相信各位对Java内存模型有了最基本认识了,下面LZ就做一个比较简单的总结。

833
来自专栏令仔很忙

UML之状态图

  状态:是指在对象生命周期中满足某些条件、执行某些活动或等待某些事件的一个条件和状况。

431
来自专栏无题

JVM锁优化概述

* 锁优化 jdk1.6对锁的实现引入了大量的优化,如自旋锁、适应性自旋锁、锁消除、锁粗化、偏向锁、轻量级锁等技术来减少锁操作的开销。 锁主要存在四中状态,依...

2685
来自专栏Java技术栈

图解 5 种 Join 连接及实战案例!(inner/ left/ right/ full/ cross)

1503
来自专栏Java Edge

线程安全与锁优化1 线程安全2 锁优化

3269
来自专栏数据和云

深入原理:分区剪裁特性剖析

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化,SQL优化和troubleshooting 编辑手记:深入学习...

2687
来自专栏黄日成的专栏

大话 Select、Poll、Epoll

提到select、poll、epoll相信大家都耳熟能详了,三个都是IO多路复用的机制,可以监视多个描述符的读/写等事件。

7.8K10

扫描关注云+社区