[20150715]Wrong Results.txt

最新推荐文章于 2024-02-22 14:07:32 发布

转载最新推荐文章于 2024-02-22 14:07:32 发布 · 58 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/280022

文章标签：

#数据库

本文详细分析了一次SQL查询中，使用物化视图导致输出结果异常的问题，通过深入理解查询过程和物化视图的工作原理，揭示了问题的根本原因并提供了解决方案。

[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