最近在办公室里,听见这么一段对话:
Bob: Alice,我看了下你昨天告诉我的那个慢查询,我已经把你想要的那个索引给加上去。现在肯定OK了。
Alice:谢谢你,Bob。我马上确认一下…不对啊,还是很慢,看起来没起作用啊
Bob:还真是。看起来Oracle没有用上这个索引,你那个查询我加了/*+INDEX(...)*/索引提示也不行。真是不知道怎么回事了。
然后,问题仍然没有解决。Alice很头疼,因为她要加的特性没有按时发现,Bob也很发愁,因为他觉得Oracle居然没有正常工作。
这是个真事。
Bob忘了Oralce和NULL值的问题了
可怜的Bob忘了,Oralce是不会把NULL放到普通索引里的。你想一下这种情况:
CREATE TABLE person (
id NUMBER(38) NOT NULL PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
date_of_birth DATE NULL
);
CREATE INDEX i_person_dob ON person(date_of_birth);
现在Bob认为有了这个索引什么事都解决了,因为他用这个查询验证了下,这个索引确实是好使的:
SELECT *
FROM person
WHERE date_of_birth > DATE '1980-01-01’;
(
当然了,你不应该使用select *)
这个查询的执行计划看起来很正常:
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| PERSON |
|* 2 | INDEX RANGE SCAN | I_PERSON_DOB |
----------------------------------------------------
这是因为Bob的查询并不需要NULL作为I_PERSON_DOB索引的一部分。不幸的是,Alice的查询看起来大概是这样的:
SELECT 1
FROM dual
WHERE DATE '1980-01-01' NOT IN (
SELECT date_of_birth FROM person
);
实际上,Alice的查询是判断是不是有人是这天生日的。她的执行计划看起来会是这样的:
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | FAST DUAL | |
|* 3 | TABLE ACCESS FULL| PERSON |
-------------------------------------
可以看到,她的查询进行了一个TABLE ACCESS FULL操作,索引被忽略了。为什么呢?很简单:
- Oracle不会把NULL值放到索引里。
- NOT IN(a, b, NULL, c, d)的结果是NULL。
不管你的这个日期’1980-01-01’在没在索引里,我们都得查看整个表来确认date_of_birth列中是否饮食一个NULL值。因为如果存在NULL值的话,
Alice查询中这个NOT IN谓词的结果不是TRUE或FALSE,而是NULL。
Alice可以用NOT EXISTS来解决这个问题
这个问题其实Alice自己就可以很容易搞定,她只需要把NOT IN换成NOT EXISTS就好了,这个谓词能够绕过SQL的特殊的
三值逻辑。
SELECT 1
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM person
WHERE date_of_birth = DATE '1980-01-01'
);
现在新的查询语句的确能够得到一个最优的执行计划:
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | FAST DUAL | |
|* 3 | INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------
但问题仍然存在,因为该来的迟早还是会来的。Alice必须在写每条查询说一句的时候都时刻谨记这次教训。
对于Bob只需把这列设置成NOT NULL就好了
最佳的解决方案,其实就是把这列设置成NOT NULL就好了:
ALTER TABLE person
MODIFY date_of_birth DATE NOT NULL;
有了这个约束后,NOT IN查询就和NOT EXISTS查询是一样的了,Bob和Alice又可以一起快乐地玩耍了。
如何找出这些捣乱的列?
很简单。下面这个查询可以列出所有存在一个NULL值的索引列。
SELECT
i.table_name,
i.index_name,
LISTAGG(
LPAD(i.column_position, 2) || ': ' ||
RPAD(i.column_name , 30) || ' ' ||
DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'),
', '
) WITHIN GROUP (ORDER BY i.column_position)
AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) =
((i.table_name, i.column_name))
WHERE EXISTS (
SELECT 1
FROM user_tab_cols t
WHERE (t.table_name, t.column_name, t.nullable) =
((i.table_name, i.column_name, 'Y' ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;
你现在再用Bob和Alice的schema来执行下,上述查询的结果是:
TABLE_NAME | INDEX_NAME | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)
现在你可以在你自己的schema上运行下这条查询语句,仔细地看一下结果中的那些列有没有必要允许NULL值的出现。应该有半数的情况下是不该出现NULL值的。加上一个NOT NULL约束后,你的程序的性能可能会得到质的提升!
原创文章转载请注明出处:
http://it.deepinmind.com
英文原文链接
分享到:
相关推荐
根据关键字,检索文本类容,并对匹配后的关键字加色,支持ajax...
当你的结果集返回许多行并且需要求总数时,索引视图十分有用。这是因为数据库引擎必须在基表数据更新时维持视图索引,这可能会降低事务的性能。要给一个视图建立一个索引,视图定义必须遵守某组条件和会话设置,同时...
下载不扣分,回帖加1分,欢迎下载,童叟无欺 下面是LIUS的介绍: LIUS是一个基于Jakarta Lucene项目的索引框架。LIUS为Lucene添加了对许多文件格式的进行索引功能如: Ms Word,Ms Excel,Ms PowerPoint,RTF,PDF,XML,...
SqlServer索引碎片整理脚本,提据库查询效率,很有用。
本资料是讲解oracle分区表及分区索引技术资料,也许对大家有用.
主要介绍索引查看方法,同时提供索引重建的方式,已经查看索引重建进度过程,希望对大家有用。
这是关于常用空间索引技术的分析的电子书,希望对各位有用
C#语言一个最令人感兴趣的地方就是类的索引器(indexer)。简单说来,所谓索引器就是一类特殊的属性, ...本文就会引领你设置类来采用索引器。但是,首先让我们概述下属性这个概念以便了解些必要的背景知识。
MySQL索引最佳实践,有用有用有用有用有用有用有用有用
MySQL只学有用的–MYSQL索引原理及使用索引的基础理解索引的常见模型(字典目录的编写形式)1. 哈希表2. 有序数组3. 二叉树聊一聊InnoDB的索引模型(B+树)索引的维护(《新华字典》的目录修改)SQL索引执行过程–...
非常有用的分析表及分区索引,供大家全面学习了解
个人总结!可以看一下,本人觉得很有用的!参考的是我老师的课件!
索引与数据完整性约束,这是学校数据库的重点 ,要好好学习啊 以后会对你的学校有用。
elasticsearch索引流程,以前的笔记,希望有用
如果您想使用但又不想自己构建Python软件包,则可能会发现此索引很有用。 用法 要搜索此索引,请在requirements.txt文件顶部添加一行: --extra-index-url https://alpine-wheels.github.io/index 贡献 索引中是否...
索引是什么:数据库中的索引类似于一本书的目录,在一本书中使用目录可以快速找到你想要的信息,而不需要读完全书。在数据库中,数据库程序使用索引可以重啊到表中的数据,而不必扫描整个表。书中的目录是一个字词...
4、覆盖索引对InnoDB尤其有用,因为InnoDB使用聚集索引组织数据,如果二级索引包含查询所需的数据,就不再需要在聚集索引中查找了。 限制: 1、覆盖索引也并不适用于任意的索引类型,索引必须存储列的值。 2、Hash和...
Lucene生成的数据以表格的形式展示出来.希望对大家有用,方便大家的开发
联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 ...
程序还内嵌了许多有用的工具,比如目录及索引编辑器、 热点图形编辑器、宏编辑器、窗口编辑器等等。借助于这些工具,你即使没有很专门的知识也 可以制作有专业水准的帮助文件,正如作者的口号所说:只要你会写字,你...