题目部分
在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语句性能上的提升。总结一下,如下图所示:
对于这几种情况分别实验如下:
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约束下,二者的执行计划是不一样的。
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事件查看具体原因:
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';
SYS用户:
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T_NUM1_LHR"."ID">=4
apadrv-start sqlid=4141557682765762850
:
call(in-use=1400, alloc=16344), compile(in-use=54632, alloc=55568), execution(in-use=2480, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T_NUM1_LHR"."ID" "ID" FROM "SYS"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4
kkoqbc: optimizing query block SEL$1 (#0)
普通用户:
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
constraint: "T_NUM1_LHR"."ID"<4
finally: "T_NUM1_LHR"."ID">=4 AND 4>4
FPD: transitive predicates are generated in query block SEL$1 (#0)
"T_NUM1_LHR"."ID">=4 AND 4>4
apadrv-start sqlid=11964066854041036881
:
call(in-use=1696, alloc=16344), compile(in-use=55176, alloc=58488), execution(in-use=2744, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T_NUM1_LHR"."ID" "ID" FROM "LHR"."T_NUM1_LHR" "T_NUM1_LHR" WHERE "T_NUM1_LHR"."ID">=4 AND 4>4
kkoqbc: optimizing query block SEL$1 (#0)
(二)在有索引的情况下,二者的性能是否有差异
DROP TABLE T_NUM2_LHR;
CREATE TABLE T_NUM2_LHR(ID NUMBER,NAME VARCHAR2(30));
CREATE INDEX IND_TNUM2_ID ON T_NUM2_LHR(ID);
INSERT INTO T_NUM2_LHR SELECT 3,OBJECT_NAME FROM DBA_OBJECTS;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
INSERT INTO T_NUM2_LHR SELECT * FROM T_NUM2_LHR;
COMMIT;
INSERT INTO T_NUM2_LHR VALUES(4,'test');
COMMIT;
SET TIMING ON
SET AUTOT ON
SELECT * FROM T_NUM2_LHR WHERE ID>3;
SELECT * FROM T_NUM2_LHR WHERE ID>=4;
LHR@orclasm > SELECT * FROM T_NUM2_LHR WHERE ID>3;
ID NAME
---------- ------------------------------
4 test
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4021107501
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_NUM2_LHR | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TNUM2_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">3)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT * FROM T_NUM2_LHR WHERE ID>=4;
ID NAME
---------- ------------------------------
4 test
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4021107501
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_NUM2_LHR | 1 | 30 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TNUM2_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到,无论是执行时间,还是逻辑读,两个SQL没有任何的差别。根据Oracle索引结构的特点,无论是大于3还是大于等于4,这二者的查询所扫描的叶节点都是同一个,因此,在这一点上不会存在性能的差别。
(三)在使用物化视图上的差别
如果表上建立了可查询重写的物化视图,那么这两个查询在是否使用物化视图上有所差别。
CREATE TABLE T_NUM3_LHR(ID NUMBER,NUM NUMBER(1));
ALTER TABLE T_NUM3_LHR ADD PRIMARY KEY(ID);
INSERT INTO T_NUM3_LHR SELECT ROWNUM,MOD(ROWNUM,4) FROM DBA_OBJECTS;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+54916,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+109832,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+219664,MOD(ROWNUM,4) FROM T_NUM3_LHR;
INSERT INTO T_NUM3_LHR SELECT ROWNUM+439328,MOD(ROWNUM,4) FROM T_NUM3_LHR;
COMMIT;
INSERT INTO T_NUM3_LHR VALUES(1000000,4);
COMMIT;
SET AUTOT ON
SELECT * FROM T_NUM3_LHR WHERE NUM>3;
SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
LHR@orclasm > SET AUTOT ON
LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>3;
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 621453705
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 314 (3)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">3)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1150 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 621453705
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 314 (3)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM">=4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1150 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由于采用的都是全表扫描,二者执行的时间和逻辑读完全一样。
下面建立一个物化视图:
SET AUTOT OFF
CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
LHR@orclasm > SET AUTOT OFF
LHR@orclasm > CREATE MATERIALIZED VIEW LOG ON T_NUM3_LHR WITH(NUM);
Materialized view log created.
LHR@orclasm > CREATE MATERIALIZED VIEW MV_T_NUM3_LHR REFRESH FAST ENABLE QUERY REWRITE AS SELECT ID,NUM FROM T_NUM3_LHR WHERE NUM>=4;
Materialized view created.
LHR@orclasm > show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
LHR@orclasm > SET AUTOT ON
LHR@orclasm > SELECT * FROM T_NUM3_LHR WHERE NUM>3;
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
SELECT * FROM T_NUM3_LHR WHERE NUM>=4;
Plan hash value: 4012093353
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 338 | 317 (3)| 00:00:04 |
| 1 | VIEW | | 13 | 338 | 317 (3)| 00:00:04 |
| 2 | UNION-ALL | | | | | |
| 3 | MAT_VIEW REWRITE ACCESS FULL| MV_T_NUM3_LHR | 1 | 26 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T_NUM3_LHR | 12 | 312 | 314 (3)| 00:00:04 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("NUM">3 AND "NUM"<4)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
1153 consistent gets
0 physical reads
0 redo size
588 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@orclasm >
ID NUM
---------- ----------
1000000 4
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4274348025
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T_NUM3_LHR | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
592 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从执行计划可以看到,对于大于等于4的情况,Oracle直接扫描了物化视图了。而对于大于3的情况,Oracle同时扫描了物化视图和原表,显然效率比较低。
这个例子其实和第一个例子很类似。虽然根据字段类型可以判断出大于3和大于等于4是等价的,但是对于CBO来说,并不会将数据类型的因素考虑进去。因此导致两个查询在使用物化视图时执行计划的区别。
本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗