oracle paralel 并行查询的困惑，高手请进！

[ojuju10]

如果用并行 /*+ FULL(mrdatatemp) PARALLEL(mrdatatemp) */，每次统计的结果都不一样，统计的总数都是动态的变化

的，总有部分数据没有统计进去；


如果去掉/*+ FULL(mrdatatemp) PARALLEL(mrdatatemp) */，不用并行，统计的总数都是正确的，都为100万，

但是速度没有并行快.

难道是数据量比较大，又进行了行列转换，用并行统计有误

熊掌和鱼不能兼得啊

请高手指正，是否并行语句的参数没有配置好，还是别的什么原因？多谢




select sum(scount) from
(
select /*+ FULL(mrdatatemp) PARALLEL(mrdatatemp) */
to_date(to_char(datetime,'YYYY-MM-DD HH24'),'YYYY-MM-DD HH24:MI:SS') as DATATIME,
to_char(datetime,'YYYY'),
to_char(datetime,'MM') ,
to_char(datetime,'DD') ,
to_char(datetime,'HH24') ,
rncid,
cid,
count(1) as SCOUNT,
sum(case when BLER=0 then 1 else 0 end) as B0,
sum(case when BLER=1 then 1 else 0 end) as B1,
sum(case when BLER=2 then 1 else 0 end) as B2,
sum(case when BLER=3 then 1 else 0 end) as B3,
sum(case when BLER=4 then 1 else 0 end) as B4,
sum(case when BLER=5 then 1 else 0 end) as B5,
sum(case when BLER=6 then 1 else 0 end) as B6,
sum(case when BLER=7 then 1 else 0 end) as B7,
sum(case when BLER=8 then 1 else 0 end) as B8,
sum(case when BLER=9 then 1 else 0 end) as B9,
sum(case when BLER=10 then 1 else 0 end) as B10,
sum(case when BLER=11 then 1 else 0 end) as B11,
sum(case when BLER=12 then 1 else 0 end) as B12,
sum(case when BLER=13 then 1 else 0 end) as B13,
sum(case when BLER=14 then 1 else 0 end) as B14,
sum(case when BLER=15 then 1 else 0 end) as B15,
sum(case when BLER=16 then 1 else 0 end) as B16,
sum(case when BLER=17 then 1 else 0 end) as B17,
sum(case when BLER=18 then 1 else 0 end) as B18,
sum(case when BLER=19 then 1 else 0 end) as B19,
sum(case when BLER=20 then 1 else 0 end) as B20,
sum(case when BLER=21 then 1 else 0 end) as B21,
sum(case when BLER=22 then 1 else 0 end) as B22,
sum(case when BLER=23 then 1 else 0 end) as B23,
sum(case when BLER=24 then 1 else 0 end) as B24,
sum(case when BLER=25 then 1 else 0 end) as B25,
sum(case when BLER=26 then 1 else 0 end) as B26,
sum(case when BLER=27 then 1 else 0 end) as B27,
sum(case when BLER=28 then 1 else 0 end) as B28,
sum(case when BLER=29 then 1 else 0 end) as B29,
sum(case when BLER=30 then 1 else 0 end) as B30,
sum(case when BLER=31 then 1 else 0 end) as B31,
sum(case when BLER=32 then 1 else 0 end) as B32,
sum(case when BLER=33 then 1 else 0 end) as B33,
sum(case when BLER=34 then 1 else 0 end) as B34,
sum(case when BLER=35 then 1 else 0 end) as B35,
sum(case when BLER=36 then 1 else 0 end) as B36,
sum(case when BLER=37 then 1 else 0 end) as B37,
sum(case when BLER=38 then 1 else 0 end) as B38,
sum(case when BLER=39 then 1 else 0 end) as B39,
sum(case when BLER=40 then 1 else 0 end) as B40,
sum(case when BLER=41 then 1 else 0 end) as B41,
sum(case when BLER=42 then 1 else 0 end) as B42,
sum(case when BLER=43 then 1 else 0 end) as B43,
sum(case when BLER=44 then 1 else 0 end) as B44,
sum(case when BLER=45 then 1 else 0 end) as B45,
sum(case when BLER=46 then 1 else 0 end) as B46,
sum(case when BLER=47 then 1 else 0 end) as B47,
sum(case when BLER=48 then 1 else 0 end) as B48,
sum(case when BLER=49 then 1 else 0 end) as B49,
sum(case when BLER=50 then 1 else 0 end) as B50,
sum(case when BLER=51 then 1 else 0 end) as B51,
sum(case when BLER=52 then 1 else 0 end) as B52,
sum(case when BLER=53 then 1 else 0 end) as B53,
sum(case when BLER=54 then 1 else 0 end) as B54,
sum(case when BLER=55 then 1 else 0 end) as B55,
sum(case when BLER=56 then 1 else 0 end) as B56,
sum(case when BLER=57 then 1 else 0 end) as B57,
sum(case when BLER=58 then 1 else 0 end) as B58,
sum(case when BLER=59 then 1 else 0 end) as B59,
sum(case when BLER=60 then 1 else 0 end) as B60,
sum(case when BLER=61 then 1 else 0 end) as B61,
sum(case when BLER=62 then 1 else 0 end) as B62,
sum(case when BLER=63 then 1 else 0 end) as B63,
sum(case when BLER=64 then 1 else 0 end) as B64,
sum(case when BLER=65 then 1 else 0 end) as B65,
sum(case when BLER=66 then 1 else 0 end) as B66,
sum(case when BLER=67 then 1 else 0 end) as B67,
sum(case when BLER=68 then 1 else 0 end) as B68,
sum(case when BLER=69 then 1 else 0 end) as B69,
sum(case when BLER=70 then 1 else 0 end) as B70,
sum(case when BLER=71 then 1 else 0 end) as B71,
sum(case when BLER=72 then 1 else 0 end) as B72,
sum(case when BLER=73 then 1 else 0 end) as B73,
sum(case when BLER=74 then 1 else 0 end) as B74,
sum(case when BLER=75 then 1 else 0 end) as B75,
sum(case when BLER=76 then 1 else 0 end) as B76,
sum(case when BLER=77 then 1 else 0 end) as B77,
sum(case when BLER=78 then 1 else 0 end) as B78,
sum(case when BLER=79 then 1 else 0 end) as B79,
sum(case when BLER=80 then 1 else 0 end) as B80,
sum(case when BLER=81 then 1 else 0 end) as B81,
sum(case when BLER=82 then 1 else 0 end) as B82,
sum(case when BLER=83 then 1 else 0 end) as B83,
sum(case when BLER=84 then 1 else 0 end) as B84,
sum(case when BLER=85 then 1 else 0 end) as B85,
sum(case when BLER=86 then 1 else 0 end) as B86,
sum(case when BLER=87 then 1 else 0 end) as B87,
sum(case when BLER=88 then 1 else 0 end) as B88,
sum(case when BLER=89 then 1 else 0 end) as B89,
sum(case when BLER=90 then 1 else 0 end) as B90,
sum(case when BLER=91 then 1 else 0 end) as B91,
sum(case when BLER=92 then 1 else 0 end) as B92,
sum(case when BLER=93 then 1 else 0 end) as B93,
sum(case when BLER=94 then 1 else 0 end) as B94,
sum(case when BLER=95 then 1 else 0 end) as B95,
sum(case when BLER=96 then 1 else 0 end) as B96,
sum(case when BLER=97 then 1 else 0 end) as B97,
sum(case when BLER=98 then 1 else 0 end) as B98,
sum(case when BLER=99 then 1 else 0 end) as B99,
sum(case when BLER=100 then 1 else 0 end) as B100
from mrdatatemp
where BLER is not null
group by
to_char(datetime,'YYYY-MM-DD HH24'),
to_char(datetime,'YYYY'),
to_char(datetime,'MM') ,
to_char(datetime,'DD') ,
to_char(datetime,'HH24') ,
rncid,
cid
) aa

[Tomac]

理论上说不应该,除非你遇到BUG了.

[oraclelogan]

[Quote=引用楼主 ojuju10 的帖子:]
如果用并行 /*+ FULL(mrdatatemp) PARALLEL(mrdatatemp) */，每次统计的结果都不一样，统计的总数都是动态的变化

的，总有部分数据没有统计进去；


如果去掉/*+ FULL(mrdatatemp) PARALLEL(mrdatatemp) */，不用并行，统计的总数都是正确的，都为100万，

但是速度没有并行快.

难道是数据量比较大，又进行了行列转换，用并行统计有误

熊掌和鱼不能兼得啊

请高手指正，是否并行语句的参数没有…
[/Quote]

道理上，应该不可能的，你的sql也是符合oracle语法的，难道是你的oracle系统有bug？你安装的是哪个版本？是否正版？

[pathuang68]

顶，太高深了

[qin_phoenix]

顶一个

[ojuju10]

oracle 版本是：10.2.0.1.0
应该是正版的吧，我们公司比较大，不会用盗版软件

[Tomac]

没有解决问题,不好意思得分.给你查询了一下, 下面这个BUG和你的SQL很类似.
并行查询方面BUG太多了, 很多都是 Wrong Results.

谢谢！


Bug 5903293 Wrong Results for Parallel Query using Group By or Distinct or has correlation
This note gives a brief overview of bug 5903293.
The content was last updated on: 26-AUG-2008
Click here for details of each of the sections below.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 10.2.0.1 but < 11
Versions confirmed as being affected 10.1.0.4
10.2.0.1
10.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 10.2.0.4 (Server Patch Set)
11.1.0.6 (Base Release)



Description
Wrong results are possible if a query is parallelized and any of the following
conditions :
1) Has a Group by with a PL/SQL function (Including Sysdate)
2) Has a Distinct with a PL/SQL function (Including Sysdate)
3) Has a correlated subquery.