合 在Oracle中，对于一个NUMBER(1)的列，如果查询中的WHERE条件分别是大于3和大于等于4，那么这二者是否等价？
结论
首先对于查询结果而言，二者没有任何区别。从这一点上讲无论是指定大于3还是指定大于等于4，二者结果都是一样的。但是，结果集一样并不代表二者等价，主要表现为以下几点：
① 在CHECK约束下，如果表属于非SYS用户，那么大于3会执行全表扫描；而大于等于4在经过CHECK约束的检查后，通过FILTER结束查询，能够更高效地返回结果，不用扫描全表。如果表属于SYS用户，那么这二者的执行计划是相同的。因为，若表属于非SYS用户，则最终经过查询转换后的执行SQL为：“SELECT "T_NUM1_LHR"."ID" "ID" FROM "SYS"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4 AND 4>4”，而若表属于SYS用户，则最终经过查询转换后的执行SQL为：“SELECT "T_NUM1_LHR"."ID" "ID" FROM "LHR"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4”，所以，在非SYS用户下，最终的执行计划中会有“filter(NULL IS NOT NULL)”的谓词条件。
② 在使用索引的时候，由于Oracle索引结构的特点，两者扫描的节点都是从4开始，在执行计划、逻辑读和执行时间等各方面都不存在性能差异。
③ 在使用物化视图的过程中，大于3会同时扫描物化视图和原表，效率较低；而大于等于4会直接扫描物化视图，效率较高。
由此可见，在返回结果集相同的情况下，使用大于等于代替大于在某些特殊情况下可以带来SQL语句性能上的提升。总结一下，如下图所示：
对于这几种情况分别实验如下：
1 2 3 4 5 6 7 8 9 10 11 | SYS@orclasm > select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production |
（一）在CHECK约束下，二者的执行计划是不一样的
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | DROP TABLE T_NUM1_LHR; CREATE TABLE T_NUM1_LHR(ID NUMBER(1)); ALTER TABLE T_NUM1_LHR ADD CHECK(ID <4); SET AUTOT ON SELECT * FROM T_NUM1_LHR WHERE ID>3; SELECT * FROM T_NUM1_LHR WHERE ID>=4; LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>3; no rows selected Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2700622406 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T_NUM1_LHR | 1 | 13 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID">3) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 330 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>=4; no rows selected Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 3764107410 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS FULL| T_NUM1_LHR | 1 | 13 | 2 (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 (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 330 bytes sent via SQL*Net to client 509 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条件就不再等价了。
若表属于SYS用户，则这二者的执行计划是相同的。
下面通过10053事件查看具体原因：
1 2 3 4 | ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; SELECT * FROM T_NUM1_LHR WHERE ID >= 4; ALTER SESSION SET EVENTS '10053 trace name context off'; SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File'; |
