【DB笔试面试677】在Oracle中，对于一个NUMBER(1)的列，若WHERE条件是大于3和大于等于4，这二者是否等价？...

♣

题目部分

在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;23BANNER4--------------------------------------------------------------------------------5Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production6PL/SQL Release 11.2.0.3.0 - Production7CORE    11.2.0.3.0      Production8TNS for Linux: Version 11.2.0.3.0 - Production9NLSRTL 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;1011no rows selected1213Elapsed: 00:00:00.001415Execution Plan16----------------------------------------------------------17Plan hash value: 27006224061819--------------------------------------------------------------------------------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--------------------------------------------------------------------------------2526Predicate Information (identified by operation id):27---------------------------------------------------2829   1 - filter("ID">3)3031Note32-----33   - dynamic sampling used for this statement (level=2)343536Statistics37----------------------------------------------------------38          0  recursive calls39          0  db block gets40          0  consistent gets41          0  physical reads42          0  redo size43        330  bytes sent via SQL*Net to client44        509  bytes received via SQL*Net from client45          1  SQL*Net roundtrips to/from client46          0  sorts (memory)47          0  sorts (disk)48          0  rows processed4950LHR@orclasm > SELECT * FROM T_NUM1_LHR WHERE ID>=4;5152no rows selected5354Elapsed: 00:00:00.005556Execution Plan57----------------------------------------------------------58Plan hash value: 37641074105960---------------------------------------------------------------------------------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---------------------------------------------------------------------------------6768Predicate Information (identified by operation id):69---------------------------------------------------7071   1 - filter(NULL IS NOT NULL)72   2 - filter("ID">=4)7374Note75-----76   - dynamic sampling used for this statement (level=2)777879Statistics80----------------------------------------------------------81          0  recursive calls82          0  db block gets83          0  consistent gets84          0  physical reads85          0  redo size86        330  bytes sent via SQL*Net to client87        509  bytes received via SQL*Net from client88          1  SQL*Net roundtrips to/from client89          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 statement10*******************************************1112Final 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">=414kkoqbc: 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)1112*******************************************13Peeked values of the binds in SQL statement14*******************************************1516Final 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>418kkoqbc: 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 client100        520  bytes received via SQL*Net from client101          2  SQL*Net roundtrips to/from client102          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 reads100          0  redo size101        592  bytes sent via SQL*Net to client102        520  bytes received via SQL*Net from client103          2  SQL*Net roundtrips to/from client104          0  sorts (memory)105          0  sorts (disk)106          1  rows processed

从执行计划可以看到，对于大于等于4的情况，Oracle直接扫描了物化视图了。而对于大于3的情况，Oracle同时扫描了物化视图和原表，显然效率比较低。

这个例子其实和第一个例子很类似。虽然根据字段类型可以判断出大于3和大于等于4是等价的，但是对于CBO来说，并不会将数据类型的因素考虑进去。因此导致两个查询在使用物化视图时执行计划的区别。

更多相关文章

随机推荐