90%的面试者都不知道这道题的答案

小编寄语

亲爱的DBA同胞们,你们是否记得在你找工作时,印象最深刻的面试题呢?那些看似简单的题目,实则蕴藏很大的玄机。今天我们通过一道经典的 ORacle DBA面试题目,去发现我们在面试中,到底还缺少那些能力?

这道题看起来很简单,然而,90%的面试者都不知道答案。。。

面试题描述

对于一个NUMBER(1)的列,查询中的WHERE条件如果分别是大于3和大于等于4,二者是否等价。

乍一看,这个问题并不难。请读者朋友们在继续读下文之前,用30秒的时间思考。

接下来我们通过杨长老的博客,来说明面试者在这道题目中给你挖下了那些坑。首先对于查询结果而言,二者没有任何区别。从这一点上讲无论是指定大于3还是指定大于等于4,二者结果都是一样的。

但是,结果集一样并不代表二者等价。

The

01

在Check约束下,二者的执行计划是不一样的

首先我们来看二者的执行计划:

如果表中恰好有上面的CHECK约束,可以发现,对于大于3和大于等于4这两个SQL的执行计划是不一致的。

对于后者,由于查询的条件违反了CHECK约束,因此Oracle在执行计划前面增加了一个FILTER,使得整个查询不需要在执行,因此这个查询不管表中数据有多少,都会在瞬间结束。

而对于大于3这种情况,虽然根据CHECK的约束和列定义,可以推断出这条查询不会返回任何记录,但是Oracle的优化器并没有聪明到根据列的精度来进行分析,因此这个查询会执行全表扫描。

也就是说,虽然这两个查询的最终结果一样,但是执行计划并不相同,而且对于大表而言,这种情况下性能也有较大的差别。

当然这种CHECK约束是特例的情况,一般情况下不会出现。原则上到底是选择大于3还是大于等于4,应该根据具体的业务来决定,而不要尝试利用Oracle的数据精度来设置查询条件。如果以后一旦字段的结构发生了修改,比如这个例子中字段的允许出现小数,那么这两个SQL的WHERE条件就不再等价了。

The

02

在有索引的情况下,二者的性能是否有差异

有朋友认为,如果是大于3的话,走索引扫描,会从3开始扫描,而大于等于4,会从4开始扫描。

根据这个观点对于大于3的情况,Oracle会先找到索引键值为3的块,然后开始扫描。而对于大于等于4的情况,则会找到索引键值为4的块,然后执行扫描。如果表中包含大量索引键值为3的记录,这两个查询就可能存在性能上的差异。

看一个具体的例子:

创建一张测试表,表中所有的ID都等于3,下面插入一条ID为4记录:

下面执行查询语句:

可以看到,无论是执行时间,还是逻辑读,两个SQL没有任何的差别。为了更好的证明Oracle并没有读取ID等于3的记录,执行下面的查询:

其实看过Concept对索引结构有一定了解就知道,根据Oracle索引结构的特点,无论是大于3还是大于等于4,二者的查询是扫描的叶节点都是同一个,因此不会在这一点上不会存在性能的差别。

The

03

在使用物化视图上的差别

如果表上建立了可查询重写的物化视图,两个查询在是否使用物化视图上有所差别。

例子仍然使用T_NUM表:

先构造一张大表,现在分别执行两个查询,检查执行结果:

由于采用的都是全表扫描,二者执行的时间和逻辑读完全一样。

下面建立一个物化视图:

下面检查系统设置是否满足查询重写:

从执行计划可以看到,对于大于等于4的情况,Oracle直接扫描了物化视图了。而对于大于3的情况,Oracle同时扫描了物化视图和原表,显然效率比较低。

这个例子其实和第一个例子很类似。虽然根据字段类型可以判断出大于3和大于等于4是等价的,但是对于CBO来说,并不会将数据类型的因素考虑进去。因此导致两个查询在使用物化视图时执行计划的区别。

总结一下两者的区别

1、在Check约束下,前者会执行全表扫描,后者经过check约束的检查后,通过filter结束查询,能够更高效地返回结果;

2、在使用索引的时候,由于Oracle索引结构的特点,两者扫描的节点都是从4开始,在执行计划和逻辑读,执行时间等各方面都不存在性能差异;

3、在使用物化视图的过程中,前者同时扫描物化视图和原表,效率较低。后者直接扫描物化视图。

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

原文发表时间:2017-09-11

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏有趣的Python

2018.2最新-Scrapy+elasticSearch+Django打造搜索引擎直至部署上线(三)爬虫基础知识

最终项目上线演示地址: http://search.mtianyan.cn 第三节:本节我们先得了解点你做爬虫做搜索引擎的居家必备知识基础。 Github地址:...

2734
来自专栏老马说编程

(25) 异常 (下) / 计算机程序的思维逻辑

上节我们介绍了异常的基本概念和异常类,本节我们进一步介绍对异常的处理,我们先来看Java语言对异常处理的支持,然后探讨在实际中到底应该如何处理异常。 异常处理...

1816
来自专栏逍遥剑客的游戏开发

汉字转成拼音首字母

2666
来自专栏Android机动车

Java 基础(二)——集合源码解析 Iterator

针对一个特定的问题,如果事先不知道需要多少个对象,或者它们的持续时间有多长,那么也不知道如何保存那些对象。既然如此,怎样才能知道那些对象要求多少空间呢?事先上根...

713
来自专栏企鹅号快讯

给初学者:JavaScript 的常见注意点

作者: CarterLi 原文:https://segmentfault.com/a/1190000012730162 上篇说了一些 JS 中数组操作的常见误区...

1786
来自专栏web前端教室

js数据结构与算法--散列

不扯淡了,还是来学技术吧。 散列,是一种常用的数据存储技术,优势在于可以快速的插入或取出,使用它的数据结构,叫散列表。 它的优势哈,插入、删除、取用数据都很快,...

18910
来自专栏JAVA高级架构

SQL索引一步到位

742
来自专栏C/C++基础

C++特性使用建议

使用引用替代指针且所有不变的引用参数必须加上const。在C 语言中,如果函数需要修改变量的值,参数必须为指针,如int foo(int *pval),在 C+...

853
来自专栏数据和云

性能优化之查询转换 - 子查询类

作者简介 ? 韩锋 精通包括Oracle、MySQL、informix等多种关系型数据库,有丰富的数据库架构设计开发经验。就职于宜信。 子查询,是SQL中常...

2625
来自专栏PHP技术

PHP程序员容易忽略的几点精华

1、变量、数组的应用技巧   (1)很多人用得不多的数组函数。foreach、list、each。分别举几个例子,应该就能知道了。例:   $dat...

33310

扫描关注云+社区