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 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

SQL Server 索引和表体系结构(聚集索引+非聚集索引)

聚集索引 概述 关于索引和表体系结构的概念一直都是讨论比较多的话题,其中表的各种存储形式是讨论的重点,在各个网站上面也有很多关于这方面写的不错的文章,我写这...

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

通过oracle类比MySQL中的字节字符问题(r4笔记第44天)

在几个月前写过一篇博文 MySQL数据类型 http://blog.itpub.net/23718752/viewspace-1371434/ 当时写完以后有...

33970
来自专栏lonelydawn的前端猿区

oracle细节

01、SQL查询语句不区分大小写,但是数据区分 02、where从句中Name=null是查询不到结果的,必须用 is null 03、union去重,unio...

24580
来自专栏不想当开发的产品不是好测试

mysql @value := 用法

背景 有这么一张表,记录名字和分数,现在需要按照成绩排名,存在并列名次的情况 ? 解决方法 思路:按照score从大到小排序,第一行数据就是第一名,第二行就是第...

29480
来自专栏恰童鞋骚年

Hadoop学习笔记—8.Combiner与自定义Combiner

  在第四篇博文《初识MapReduce》中,我们认识了MapReduce的八大步凑,其中在Map阶段总共五个步骤,如下图所示:

12210
来自专栏zingpLiu

python【第十二篇下】操作MySQL数据库以及ORM之 sqlalchemy

  对象关系映射(英语:Object Relation Mapping,简称ORM,或O/RM,或O/R mapping),是一种程序技术,用于实现面向对象编程...

11410
来自专栏Java后端生活

mysql 时间戳函数FROM_UNIXTIME和UNIX_TIMESTAMP函数的使用说明

29860
来自专栏信安之路

SQL注入的常规思路及奇葩技巧

最近在看《SQL注入攻击与防御》这本书,看了之后感觉自己之前的视野和格局还是太小了些。SQLi的应用特别广泛,多种web数据库不说,移动安卓端也存在通用的SQL...

14200
来自专栏信安之路

sqlmap自带的tamper你了解多少?

sqlmap 是一款注入神器广为人知,里面的 tamper 常常用来绕过 WAF ,很实用的模块,但是却常常被新手忽略(比如我),今天就整理总结一下 tampe...

11600
来自专栏ml

mysql知识初篇(一)

mysql介绍 (1) mysql数据库是瑞典AB开发。 (2) mysql--> sun --> oracle。 (3) mysql数据库的...

36370

扫码关注云+社区

领取腾讯云代金券