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、在使用物化视图的过程中，前者同时扫描物化视图和原表，效率较低。后者直接扫描物化视图。