♣
题目 部分
在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语句性能上的提升。总结一下，如下图所示：
对于这几种情况分别实验如下：
1SYS@orclasm > select * from v$version; 2 3BANNER 4-------------------------------------------------------------------------------- 5Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 6PL/SQL Release 11.2.0.3.0 - Production 7CORE 11.2.0.3.0 Production 8TNS for Linux: Version 11.2.0.3.0 - Production 9NLSRTL Version 11.2.0.3.0 - Production
（一）在CHECK约束下，二者的执行计划是不一样的。
1DROP TABLE T_NUM1_LHR;
2CREATE TABLE T_NUM1_LHR(ID NUMBER(1));
3ALTER TABLE T_NUM1_LHR ADD CHECK(ID <4);
4SET AUTOT ON
5SELECT * FROM T_NUM1_LHR WHERE ID>3;
6SELECT * FROM T_NUM1_LHR WHERE ID>=4;
7
8
9LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>3;
10
11no rows selected
12
13Elapsed: 00:00:00.00
14
15Execution Plan
16----------------------------------------------------------
17Plan hash value: 2700622406
18
19--------------------------------------------------------------------------------
20| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
21--------------------------------------------------------------------------------
22| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
23|* 1 | TABLE ACCESS FULL| T_NUM1_LHR | 1 | 13 | 2 (0)| 00:00:01 |
24--------------------------------------------------------------------------------
25
26Predicate Information (identified by operation id):
27---------------------------------------------------
28
29 1 - filter("ID">3)
30
31Note
32-----
33 - dynamic sampling used for this statement (level=2)
34
35
36Statistics
37----------------------------------------------------------
38 0 recursive calls
39 0 db block gets
40 0 consistent gets
41 0 physical reads
42 0 redo size
43 330 bytes sent via SQL*Net to client
44 509 bytes received via SQL*Net from client
45 1 SQL*Net roundtrips to/from client
46 0 sorts (memory)
47 0 sorts (disk)
48 0 rows processed
49
50LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>=4;
51
52no rows selected
53
54Elapsed: 00:00:00.00
55
56Execution Plan
57----------------------------------------------------------
58Plan hash value: 3764107410
59
60---------------------------------------------------------------------------------
61| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
62---------------------------------------------------------------------------------
63| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
64|* 1 | FILTER | | | | | |
65|* 2 | TABLE ACCESS FULL| T_NUM1_LHR | 1 | 13 | 2 (0)| 00:00:01 |
66---------------------------------------------------------------------------------
67
68Predicate Information (identified by operation id):
69---------------------------------------------------
70
71 1 - filter(NULL IS NOT NULL)
72 2 - filter("ID">=4)
73
74Note
75-----
76 - dynamic sampling used for this statement (level=2)
77
78
79Statistics
80----------------------------------------------------------
81 0 recursive calls
82 0 db block gets
83 0 consistent gets
84 0 physical reads
85 0 redo size
86 330 bytes sent via SQL*Net to client
87 509 bytes received via SQL*Net from client
88 1 SQL*Net roundtrips to/from client
89 0 sorts (memory)
90 0 sorts (disk)
91 0 rows processed
如果表中恰好有上面的CHECK约束，那么可以发现，对于大于3和大于等于4这两个SQL的执行计划是不一致的。对于后者，由于查询的条件违反了CHECK约束，因此Oracle在执行计划前面增加了一个FILTER，使得整个查询不需要在执行，因此这个查询不管表中数据有多少，都会在瞬间结束。
而对于大于3这种情况，虽然根据CHECK的约束和列定义，可以推断出这条查询不会返回任何记录，但是Oracle的优化器并没有聪明到根据列的精度来进行分析，因此这个查询会执行全表扫描。也就是说，虽然这两个查询的最终结果一样，但是执行计划并不相同，而且对于大表而言，这种情况下性能也有较大的差别。
当然这种CHECK约束是特例的情况，一般情况下不会出现。原则上到底是选择大于3还是大于等于4，应该根据具体的业务来决定，而不要尝试利用Oracle的数据精度来设置查询条件。如果以后一旦字段的结构发生了修改，比如这个例子中字段的允许出现小数，那么这两个SQL的WHERE条件就不再等价了。
若表属于SYS用户，则这二者的执行计划是相同的。
下面通过10053事件查看具体原因：
1ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; 2SELECT * FROM T_NUM1_LHR WHERE ID >= 4; 3ALTER SESSION SET EVENTS '10053 trace name context off'; 4SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';
SYS用户：
1try to generate transitive predicate from check constraints for query block SEL$1 (#0) 2finally: "T_NUM1_LHR"."ID">=4 3 4apadrv-start sqlid=4141557682765762850 5 : 6 call(in-use=1400, alloc=16344), compile(in-use=54632, alloc=55568), execution(in-use=2480, alloc=4032) 7 8******************************************* 9Peeked values of the binds in SQL statement 10******************************************* 11 12Final query after transformations:******* UNPARSED QUERY IS ******* 13SELECT "T_NUM1_LHR"."ID" "ID" FROM "SYS"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4 14kkoqbc: optimizing query block SEL$1 (#0)
普通用户：
1try to generate transitive predicate from check constraints for query block SEL$1 (#0) 2constraint: "T_NUM1_LHR"."ID"<4 3 4finally: "T_NUM1_LHR"."ID">=4 AND 4>4 5 6FPD: transitive predicates are generated in query block SEL$1 (#0) 7"T_NUM1_LHR"."ID">=4 AND 4>4 8apadrv-start sqlid=11964066854041036881 9 : 10 call(in-use=1696, alloc=16344), compile(in-use=55176, alloc=58488), execution(in-use=2744, alloc=4032) 11 12******************************************* 13Peeked values of the binds in SQL statement 14******************************************* 15 16Final query after transformations:******* UNPARSED QUERY IS ******* 17SELECT "T_NUM1_LHR"."ID" "ID" FROM "LHR"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4 AND 4>4 18kkoqbc: optimizing query block SEL$1 (#0)
（二）在有索引的情况下，二者的性能是否有差异
1DROP TABLE T_NUM2_LHR;
2CREATE TABLE T_NUM2_LHR(ID NUMBER,NAME VARCHAR2(30));
3CREATE INDEX IND_TNUM2_ID ON T_NUM2_LHR(ID);
4INSERT INTO T_NUM2_LHR SELECT 3，OBJECT_NAME FROM DBA_OBJECTS;
5INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
6INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
7INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
8INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
9COMMIT;
10INSERT INTO T_NUM2_LHR VALUES(4,'test');
11COMMIT;
12
13SET TIMING ON
14SET AUTOT ON
15SELECT * FROM T_NUM2_LHR WHERE ID>3;
16SELECT * FROM T_NUM2_LHR WHERE ID>=4;
17
18LHR@orclasm > SELECT * FROM T_NUM2_LHR WHERE ID>3;
19
20 ID NAME
21---------- ------------------------------
22 4 test
23
24Elapsed: 00:00:00.00
25
26Execution Plan
27----------------------------------------------------------
28Plan hash value: 4021107501
29
30--------------------------------------------------------------------------------------------
31| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
32--------------------------------------------------------------------------------------------
33| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
34| 1 | TABLE ACCESS BY INDEX ROWID| T_NUM2_LHR | 1 | 30 | 1 (0)| 00:00:01 |
35|* 2 | INDEX RANGE SCAN | IND_TNUM2_ID | 1 | | 1 (0)| 00:00:01 |
36--------------------------------------------------------------------------------------------
37
38Predicate Information (identified by operation id):
39---------------------------------------------------
40
41 2 - access("ID">3)
42
43Note
44-----
45 - dynamic sampling used for this statement (level=2)
46
47
48Statistics
49----------------------------------------------------------
50 0 recursive calls
51 0 db block gets
52 4 consistent gets
53 0 physical reads
54 0 redo size
55 595 bytes sent via SQL*Net to client
56 520 bytes received via SQL*Net from client
57 2 SQL*Net roundtrips to/from client
58 0 sorts (memory)
59 0 sorts (disk)
60 1 rows processed
61
62LHR@orclasm > SELECT * FROM T_NUM2_LHR WHERE ID>=4;
63
64 ID NAME
65---------- ------------------------------
66 4 test
67
68Elapsed: 00:00:00.00
69
70Execution Plan
71----------------------------------------------------------
72Plan hash value: 4021107501
73
74--------------------------------------------------------------------------------------------
75| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
76--------------------------------------------------------------------------------------------
77| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
78| 1 | TABLE ACCESS BY INDEX ROWID| T_NUM2_LHR | 1 | 30 | 1 (0)| 00:00:01 |
79|* 2 | INDEX RANGE SCAN | IND_TNUM2_ID | 1 | | 1 (0)| 00:00:01 |
80--------------------------------------------------------------------------------------------
81
82Predicate Information (identified by operation id):
83---------------------------------------------------
84
85 2 - access("ID">=4)
86
87Note
88-----
89 - dynamic sampling used for this statement (level=2)
90
91
92Statistics
93----------------------------------------------------------
94 0 recursive calls
95 0 db block gets
96 4 consistent gets
97 0 physical reads
98 0 redo size
99 595 bytes sent via SQL*Net to client
100 520 bytes received via SQL*Net from client
101 2 SQL*Net roundtrips to/from client
102 0 sorts (memory)
103 0 sorts (disk)
104 1 rows processed
可以看到，无论是执行时间，还是逻辑读，两个SQL没有任何的差别。根据Oracle索引结构的特点，无论是大于3还是大于等于4，这二者的查询所扫描的叶节点都是同一个，因此，在这一点上不会存在性能的差别。
（三）在使用物化视图上的差别
如果表上建立了可查询重写的物化视图，那么这两个查询在是否使用物化视图上有所差别。
1CREATE TABLE T_NUM3_LHR(ID NUMBER,NUM NUMBER(1));
2ALTER TABLE T_NUM3_LHR ADD PRIMARY KEY(ID);
3INSERT INTO T_NUM3_LHR SELECT ROWNUM,MOD(ROWNUM,4) FROM DBA_OBJECTS;
4INSERT INTO T_NUM3_LHR SELECT ROWNUM+54916,MOD(ROWNUM,4) FROM T_NUM3_LHR;
5INSERT INTO T_NUM3_LHR SELECT ROWNUM+109832,MOD(ROWNUM,4) FROM T_NUM3_LHR;
6INSERT INTO T_NUM3_LHR SELECT ROWNUM+219664,MOD(ROWNUM,4) FROM T_NUM3_LHR;
7INSERT INTO T_NUM3_LHR SELECT ROWNUM+439328,MOD(ROWNUM,4) FROM T_NUM3_LHR;
8COMMIT;
9INSERT INTO T_NUM3_LHR VALUES(1000000,4);
10COMMIT;
11
12SET AUTOT ON
13SELECT * FROM T_NUM3_LHR WHERE NUM>3;
14SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
15LHR@orclasm > SET AUTOT ON
16LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>3;
17
18 ID NUM
19---------- ----------
20 1000000 4
21
22Elapsed: 00:00:00.01
23
24Execution Plan
25----------------------------------------------------------
26Plan hash value: 621453705
27
28--------------------------------------------------------------------------------
29| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
30--------------------------------------------------------------------------------
31| 0 | SELECT STATEMENT | | 12 | 312 | 314 (3)| 00:00:04 |
32|* 1 | TABLE ACCESS FULL| T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
33--------------------------------------------------------------------------------
34
35Predicate Information (identified by operation id):
36---------------------------------------------------
37
38 1 - filter("NUM">3)
39
40Note
41-----
42 - dynamic sampling used for this statement (level=2)
43
44
45Statistics
46----------------------------------------------------------
47 0 recursive calls
48 1 db block gets
49 1150 consistent gets
50 0 physical reads
51 0 redo size
52 588 bytes sent via SQL*Net to client
53 520 bytes received via SQL*Net from client
54 2 SQL*Net roundtrips to/from client
55 0 sorts (memory)
56 0 sorts (disk)
57 1 rows processed
58
59LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
60
61 ID NUM
62---------- ----------
63 1000000 4
64
65Elapsed: 00:00:00.01
66
67Execution Plan
68----------------------------------------------------------
69Plan hash value: 621453705
70
71--------------------------------------------------------------------------------
72| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
73--------------------------------------------------------------------------------
74| 0 | SELECT STATEMENT | | 12 | 312 | 314 (3)| 00:00:04 |
75|* 1 | TABLE ACCESS FULL| T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
76--------------------------------------------------------------------------------
77
78Predicate Information (identified by operation id):
79---------------------------------------------------
80
81 1 - filter("NUM">=4)
82
83Note
84-----
85 - dynamic sampling used for this statement (level=2)
86
87
88Statistics
89----------------------------------------------------------
90 0 recursive calls
91 1 db block gets
92 1150 consistent gets
93 0 physical reads
94 0 redo size
95 588 bytes sent via SQL*Net to client
96 520 bytes received via SQL*Net from client
97 2 SQL*Net roundtrips to/from client
98 0 sorts (memory)
99 0 sorts (disk)
100 1 rows processed
由于采用的都是全表扫描，二者执行的时间和逻辑读完全一样。
下面建立一个物化视图：
1SET AUTOT OFF
2CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
3
4CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
5
6
7LHR@orclasm > SET AUTOT OFF
8LHR@orclasm > CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
9
10Materialized view log created.
11
12LHR@orclasm > CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
13
14Materialized view created.
15
16
17LHR@orclasm > show parameter query
18
19NAME TYPE VALUE
20------------------------------------ ----------- ------------------------------
21query_rewrite_enabled string TRUE
22query_rewrite_integrity string enforced
23LHR@orclasm > SET AUTOT ON
24LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>3;
25
26 ID NUM
27---------- ----------
28 1000000 4
29
30Elapsed: 00:00:00.01
31
32Execution Plan
33----------------------------------------------------------
34SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
35Plan hash value: 4012093353
36
37------------------------------------------------------------------------------------------------
38| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
39------------------------------------------------------------------------------------------------
40| 0 | SELECT STATEMENT | | 13 | 338 | 317 (3)| 00:00:04 |
41| 1 | VIEW | | 13 | 338 | 317 (3)| 00:00:04 |
42| 2 | UNION-ALL | | | | | |
43| 3 | MAT_VIEW REWRITE ACCESS FULL| MV_T_NUM3_LHR | 1 | 26 | 3 (0)| 00:00:01 |
44|* 4 | TABLE ACCESS FULL | T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
45------------------------------------------------------------------------------------------------
46
47Predicate Information (identified by operation id):
48---------------------------------------------------
49
50 4 - filter("NUM">3 AND "NUM"<4)
51
52Note
53-----
54 - dynamic sampling used for this statement (level=2)
55
56
57Statistics
58----------------------------------------------------------
59 0 recursive calls
60 1 db block gets
61 1153 consistent gets
62 0 physical reads
63 0 redo size
64 588 bytes sent via SQL*Net to client
65 520 bytes received via SQL*Net from client
66 2 SQL*Net roundtrips to/from client
67 0 sorts (memory)
68 0 sorts (disk)
69 1 rows processed
70
71LHR@orclasm >
72 ID NUM
73---------- ----------
74 1000000 4
75
76Elapsed: 00:00:00.00
77
78Execution Plan
79----------------------------------------------------------
80Plan hash value: 4274348025
81
82----------------------------------------------------------------------------------------------
83| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
84----------------------------------------------------------------------------------------------
85| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
86| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T_NUM3_LHR | 1 | 26 | 3 (0)| 00:00:01 |
87----------------------------------------------------------------------------------------------
88
89Note
90-----
91 - dynamic sampling used for this statement (level=2)
92
93
94Statistics
95----------------------------------------------------------
96 0 recursive calls
97 0 db block gets
98 3 consistent gets
99 0 physical reads
100 0 redo size
101 592 bytes sent via SQL*Net to client
102 520 bytes received via SQL*Net from client
103 2 SQL*Net roundtrips to/from client
104 0 sorts (memory)
105 0 sorts (disk)
106 1 rows processed
从执行计划可以看到，对于大于等于4的情况，Oracle直接扫描了物化视图了。而对于大于3的情况，Oracle同时扫描了物化视图和原表，显然效率比较低。
这个例子其实和第一个例子很类似。虽然根据字段类型可以判断出大于3和大于等于4是等价的，但是对于CBO来说，并不会将数据类型的因素考虑进去。因此导致两个查询在使用物化视图时执行计划的区别。
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。