[20180625]10g下查询条件rownum = 0.txt
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> alter session set statistics_level=all;
Session altered.
SCOTT@test> select * from emp where rownum = 0;
no rows selected
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9cqutphfzqcdr, child number 1
-------------------------------------
select * from emp where rownum = 0
Plan hash value: 2063368778
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 1 | COUNT | | 1 | | | | | 0 |00:00:00.01 | 7 |
|* 2 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 518 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=0)
25 rows selected.
--//可以发现实际上真实做的是全表扫描,逻辑读7,采用的是filter.
--//可以发现这个是一个bug,结果大量的逻辑读.可以在11g重复测试:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> select * from emp where rownum = 0;
no rows selected
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9cqutphfzqcdr, child number 0
-------------------------------------
select * from emp where rownum = 0
Plan hash value: 1973284518
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 0 |00:00:00.01 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 1 | 38 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=0)
25 rows selected.
--//这里逻辑读是0,A-rows=0(id=2),也说明没有做全表扫描.
