[20180625]10g下查詢條件rownum = 0.txt

[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),也說明沒有做全表掃描.