关于索引和空值的讨论(r3笔记第80天)

在日常的工作中,空值总是有特殊的身份,对于它的处理有时候也是比较纠结。 有时候创建索引的时候会因为空值出现一些奇怪的结果。 有时候一个简单的查询因为空值却走不了索引。 有时候却因为空值而能走索引。 我们来简单的模拟一下这些问题。 首先创建一个空表,注意对于id列我们是加了not null的约束的。

SQL> create table index_test(id number not null,name varchar2(30) ) ; Table created. 我们创建一个唯一性索引,包含了id和name列。 SQL> create unique index inx_test on index_test(id,name); Index created. 这个时候我们对表index_test插入数据。因为name列没有非空约束,所以可以为空。注意第一条insert语句,如果插入空串也会作为null来处理。 SQL> insert into index_test values(2,''); 1 row created. SQL> insert into index_test values(1,'a'); 1 row created. SQL> insert into index_test values(3,null); 1 row created. 收集一下统计信息。

exec dbms_stats.gather_table_stats(user,'INDEX_TEST'); 这个时候我们来看一下下面这个查询额执行计划。

SQL> select *from index_test where id is not null; Execution Plan ---------------------------------------------------------- Plan hash value: 4273605835 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------

对于这个查询可能没什么感觉,走了全索引扫描。我们在查询条件中添加了id is not null的条件,其实id列已经存在非空约束了。所以这个过滤条件可有可无。 我们来看看不加过滤条件的情况。还是走了全索引扫描。 SQL> select *from index_test; Execution Plan ---------------------------------------------------------- Plan hash value: 4273605835 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------

这个时候我们来取消id列的非空约束。 SQL> alter table index_test modify(id number null); Table altered. 然后再次查询执行计划

SQL> select *from index_test; Execution Plan ---------------------------------------------------------- Plan hash value: 356488860 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 12 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| INDEX_TEST | 3 | 12 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- 来看看添加非空的过滤条件,又可以走索引了。

SQL> select *from index_test where id is not null;

Execution Plan ---------------------------------------------------------- Plan hash value: 4273605835

----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 | |* 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------

Predicate Information (identified by operation id): ---------------------------------------------------

1 - filter("ID" IS NOT NULL)

好了问题来了,索引对于空值好像总是有些特殊,我们来看看空值在索引中的一些细节。

SQL> set autot off 这个时候表index_test的索引还是唯一性索引,我们尝试插入一些值来对比一下。 如果插入name列为null,可以正常插入。 SQL> insert into index_test values(1,null); 1 row created. 如果插入id列为null,也可以正常插入。因为我们取消了id列的非空约束。

SQL> insert into index_test values(null,1); 1 row created. 插入id,name列为null,这个时候竟然可以正常插入。 SQL> insert into index_test values(null,null); 1 row created. 再尝试一条,竟然还可以正常插入。 SQL> insert into index_test values(null,null); 1 row created. 我们再来试试空串的情况,发现结果和Null是一致的,都可以插入。 SQL> insert into index_test values('',''); 1 row created. SQL> insert into index_test values('',''); 1 row created. 简单分析一下索引,我们来看看空值在索引中的存储情况。 SQL> analyze index inx_test validate structure; Index analyzed. 先来看看index_test中的数据情况,因为有些行存在空值,就把rownum也给打印出来方便查看。 SQL> select rownum,id,name from index_test; ROWNUM ID NAME ---------- ---------- ------------------------------ 1 2 2 1 a 3 3 4 1 5 1 6 7 8 9 9 rows selected. 我们来看看一共有9行数据,索引只有5行,最后4行都不在索引中。这也就基本能够说明为什么上面的查询条件中id is not null的时候有时候走索引,有时候又不走索引了。 至于为什么可以成功插入id,name列为空的行,是因为对于oracle来说,(null,null)和(null,null)是不同的,null值总是介于一种很模糊的状态。 SQL> select name,lf_rows from index_stats; NAME LF_ROWS ------------------------------ ---------- INX_TEST 5 对表Index_test中的数据进行统计,null值的统计结果是包含了Null和空串。

SQL> select id,name,count(*)from index_test group by id,name ; ID NAME COUNT(*) ---------- ------------------------------ ---------- 1 1 4 1 a 1 3 1 2 1 1 1 6 rows selected. 此外,在平时的工作中,如果需要对某个表创建索引,就需要考虑null值的情况,为了使得索引能够正常启用,我们需要索引列中至少有一列存在非空约束。

就如下面的情况,我们已经存在唯一性索引,但是因为b树索引不会存储null的条目,所以对表中已有的空值就需要使用全表扫描了。 SQL> set autot trace exp SQL> select id,name from index_test; Execution Plan ---------------------------------------------------------- Plan hash value: 356488860 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 12 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| INDEX_TEST | 3 | 12 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------

我们如果需要输出非空的数据,加入is not null的过滤条件,索引就能够正常启用了。 select id,name from index_test where id is not null Execution Plan ---------------------------------------------------------- Plan hash value: 4273605835 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 12 | 1 (0)| 00:00:01 | |* 1 | INDEX FULL SCAN | INX_TEST | 3 | 12 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID" IS NOT NULL)

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2014-12-10

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于虚拟索引的学习(r3笔记第75天)

昨天简单总结了下不可见索引,今天来说说虚拟索引。 这两个索引听起来有点类似。其实差别还是比较大。 不可见索引有对应的索引段,而虚拟索引没有对应的索引段存...

29760
来自专栏杨建荣的学习笔记

基于DB time的调优分析 (r6笔记第79天)

继昨天使用DB time能够快速灵活的定位sql语句之后,发现分析问题更快捷,高效了。今天就牛刀小试,把一个数据库从500%的负载调到不到100%的负载。前提是...

31740
来自专栏乐沙弥的世界

Oracle大表清理truncate .. reuse storage

a、通过上述测试,当使用reuse storage与普通方式并无明显差异 b、truncate table 是ddl操作,无法回滚 c、尽管无明显性能差...

17540
来自专栏nnngu

019 单例模式的5种写法

单例模式的定义:保证一个类仅有一个实例,并提供一个访问它的全局访问点! 1、懒汉 public class Singleton { private st...

30240
来自专栏闵开慧

phpmyadmin中导入文件时显示 No database selected

错误 SQL 查询: -- 数据库: `7789_pay` -- -- -------------------------------------...

42760
来自专栏杨建荣的学习笔记

关于不可见索引的学习(r3笔记74天)

不可见索引在日常工作中可能实用比较少,自己体验了一把,还是比较实用的功能,在平时的工作中不妨尝试一下。 我们来先体验一下,然后再细细的总结一下。 测试环境基于1...

34680
来自专栏乐沙弥的世界

Oracle自适应共享游标

    自适应游标共享Adaptive Cursor Sharing或扩展的游标共享(Extended Cursor Sharing)是Oracle 11g...

12720
来自专栏杨建荣的学习笔记

聚簇因子和执行计划的联系(r3笔记第90天)

在平时的工作中,可能会碰到一种很奇怪的问题,本来在生产环境中有些sql语句执行没有问题,一个很普通的查询预期走了索引扫面,但是拷贝数据到其它环境之后,就发现却走...

29850
来自专栏Netkiller

数据库记录安全解决方案

数据库记录安全解决方案 http://netkiller.github.io/journal/mysql.security.html 摘要 数据库记录防删除,放...

42960
来自专栏数据库新发现

Oracle诊断案例-Sql_trace之一

http://www.eygle.com/case/sql_trace_1.htm

13330

扫码关注云+社区

领取腾讯云代金券