对于整数大于3和大于等于4是否一样

http://yangtingkun.itpub.net/post/468/474742

今天同事问了我一个问题，对于一个NUMBER(1)的列，查询中的WHERE条件如果分别是大于3和大于等于4，二者是否等价。

听到这个话题觉得比较有意思，在这里简单描述一下。

对于查询结果而言，二者没有任何区别。从这一点上讲无论是指定大于3还是指定大于等于4，二者结果都是一样的。

但是并不意味着二者等价：

SQL> CREATE TABLE T_NUM (ID NUMBER(1));

表已创建。

SQL> ALTER TABLE T_NUM ADD CHECK (ID < 4);

表已更改。

SQL> SET AUTOT ON
SQL> SELECT * FROM T_NUM WHERE ID > 3;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 331351779

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_NUM | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("ID">3)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
186 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
237 bytes sent via SQL*Net to client
327 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> SELECT * FROM T_NUM WHERE ID >= 4;

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 1637684650

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T_NUM | 1 | 13 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)
2 - filter("ID">=4)

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
12 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
237 bytes sent via SQL*Net to client
327 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

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

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

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

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

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

yangtingkun 发表于:2008.12.01 23:20 ::分类: ( ORACLE ) ::阅读:(2302次) :: 评论 (0)