1. 首页
  2. IT资讯

[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),也说明没有做全表扫描.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2156593/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182840.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code