开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20150715]Wrong Results.txt

简介: [20150715]Wrong Results.txt http://afatkulin.blogspot.com/2015/07/wrong-results.html --重复测试: SCOTT@test> @ver1 PORT_STRING    ...
+关注继续查看

[20150715]Wrong Results.txt

http://afatkulin.blogspot.com/2015/07/wrong-results.html

--重复测试:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


create table t as select mod(level, 10) n, level m from dual connect by level <= 1000;
create materialized view mv_t enable query rewrite as select n, sum(m), grouping_id(n) gid from t group by rollup(n);

with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+);

SCOTT@test> with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+);
no rows selected


SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4nhtt9tat8cws, child number 0
-------------------------------------
with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+)
Plan hash value: 29516041
-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |        |       |     6 (100)|          |
|   1 |  SORT UNIQUE NOSORT             |      |      1 |    28 |     6  (17)| 00:00:01 |
|*  2 |   FILTER                        |      |        |       |            |          |
|   3 |    NESTED LOOPS OUTER           |      |      1 |    28 |     5   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL           | DUAL |      1 |     2 |     2   (0)| 00:00:01 |
|*  5 |     MAT_VIEW REWRITE ACCESS FULL| MV_T |      1 |    26 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$9EB47AD3
   4 - SEL$9EB47AD3 / DUAL@SEL$1
   5 - SEL$9EB47AD3 / MV_T@SEL$EE2C87C0
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MV_T"."GID"=0)
   5 - filter("MV_T"."N"=CASE  WHEN (ROWID IS NOT NULL) THEN 20 ELSE 20 END )

--仔细观察可以发现2 - filter("MV_T"."GID"=0),导致没有结果输出。

SCOTT@test> select * from MV_T;
         N     SUM(M)        GID
---------- ---------- ----------
         0      50500          0
         1      49600          0
         2      49700          0
         3      49800          0
         4      49900          0
         5      50000          0
         6      50100          0
         7      50200          0
         8      50300          0
         9      50400          0
               500500          1
11 rows selected.

--查询使用物化视图,导致结果出现异常。

SCOTT@test> drop materialized view mv_t;
Materialized view dropped.

SCOTT@test> with v as ( select 20 n from dual) select distinct v.n from v, t where v.n=t.n(+);
         N
----------
        20

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
ts是什么
VSCode提示 例如:定义者指定了一个方法: export function foo(name: string): number { return name.length }
0 0
为什么我们需要 TS ?
文中不少观点的想法系个人见解,有一定的个人局限性,欢迎交流
0 0
Existence and nonexistence results for anisotropic quasilinear elliptic equations
Fragalà, Ilaria; Gazzola, Filippo; Kawohl, Bernd. Existence and nonexistence results for anisotropic quasilinear elliptic equations.
508 0
[20150924]result cache problem.txt
[20150924]result cache problem.txt --昨天看了连接,看到一个关于result cache的例子,重复测试看看: --链接 https://jonathanlewis.
652 0
NSUserDfEfaults
<p style="color:rgb(51,51,51); font-family:Arial; font-size:14px; line-height:26px"> 创建一个user defaults方法有多个,最简单得快速创建方法:</p> <p style="color:rgb(51,51,51); font-family:Arial; font-size:14px; line
732 0
Index downloads are disabled, search results may be incomplete.
国内私募机构九鼎控股打造APP,来就送 20元现金领取地址:http://jdb.jiudingcapital.com/phone.html内部邀请码:C8E245J (不写邀请码,没有现金送)国内私募机构九鼎控股打造,九鼎投资是在全国股份转让系统挂牌的公众公司,股票代码为430719,为“中国PE第一股”,市值超1000亿元。
813 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Why literature is the ultimate
立即下载
Semantic Search--Fast Results from Large,Foreign Language Corpora
立即下载
Why you should care about data layout in the file system
立即下载