有一个表大概2000万数据,表名mid_item_prd_201003, 现我对此表进行sum 分组,形成另外一个临时表mid_201003_tmp1
结果发现 两个表数据对不去来了,求高手解决 oracle 版本
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
create table mid_201003_tmp1 as
select prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,
pay_id,area_id,sum(money) money
from mid_item_prd_201003
group by
prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,pay_id,area_id;select sum(money) from mid_201003_tmp1 ; --11734536460select sum(money) from mid_item_prd_201003 ; --11731516135
结果发现 两个表数据对不去来了,求高手解决 oracle 版本
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
create table mid_201003_tmp1 as
select prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,
pay_id,area_id,sum(money) money
from mid_item_prd_201003
group by
prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,pay_id,area_id;select sum(money) from mid_201003_tmp1 ; --11734536460select sum(money) from mid_item_prd_201003 ; --11731516135
你原表的数据有没有被改变或者删除?
我查了一下资料,说是oracle的sum分组后会产生差异。属于oracle的bug ,等高人来指导
我查了一下资料,说是oracle的sum分组后会产生差异。属于oracle的bug ,等高人来指导
=================
如果真有这种这么低级而且致命的BUG,那谁还用啊
pay_id,area_id,sum(money) money
from mid_item_prd_201003
group by
prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,pay_id,area_id;你再这个外层再嵌套SUM计算下得到的是什么数值,然后和建立临时表的数据比较下
select sum(money) from (
select prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,
pay_id,area_id,sum(money) money
from mid_item_prd_201003
group by
prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,pay_id,area_id) 和 select sum(money) from mid_item_prd_201003 数据不一样
不是临时表的问题
select sum(money) from (
select prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,
pay_id,area_id,sum(money) money
from mid_item_prd_201003
group by
prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,pay_id,area_id) 和 select sum(money) from mid_item_prd_201003 数据不一样也就是说同一个表 mid_item_prd_201003 直接查询 和 分组后再查询 数据就不一样了
楼主解决了说明下
设置取消hash group by试试
alter system set "_gby_hash_aggregation_enabled" = false ;
不错。http://dbaspot.com/forums/oracle-server/146809-10g-have-group-bug.html
9i group by后默认排序
10g group by使用hash连结聚集后不排序
改变有关
导致group失败?
在执行创建语句时候
create table mid_201003_tmp1 as
select prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,
pay_id,area_id,sum(money) money
from mid_item_prd_201003
group by
prd_id,acct_item_id,serv_type_id,urban_id,cust_id,if_mer,pay_id,area_id
order by prd_id ;