最近单位的数据库要从db2转到oracle。我在转换sql语句时,发现一个非常奇怪的问题。请各位帮忙看看。同样的sql语句在db2环境下运行正常,而到了oralce10g下,却出现不可思议的情况,但是在9i下却正常。我怀疑是不是10g有bug。
sql原文:
drop table Fact_XHG12_2006_10;
create table Fact_XHG12_2006_10(
TimeCode Char(6),
IEFlag Char(1),
CustomCode Char(4),
CityCode Char(4),
EntTypeCode Char(4),
TradeTypeCode Char(4),
MakerCountryCode Char(5),
ProductCode Char(10),
Quantity NUMBER(13),
Money NUMBER(13),
SumQ NUMBER(13),
SumM NUMBER(13)
) nologging;
drop table Temp_Fact_XHG12;
create table Temp_Fact_XHG12(
TimeCode Char(6),
IEFlag Char(1),
CustomCode Char(4),
CityCode Char(4),
EntTypeCode Char(4),
TradeTypeCode Char(4),
MakerCountryCode Char(5),
ProductCode Char(10),
Quantity NUMBER(13),
Money NUMBER(13),
SumQ NUMBER(13),
SumM NUMBER(13)
) nologging;
insert into Temp_Fact_XHG12
select main.TimeCode,
main.IEFlag,
main.CustomCode,
main.CityCode,
main.EntTypeCode,
main.TradeTypeCode,
main.MakerCountryCode,
main.ProductCode,
main.Quantity,
main.Money,
main.SumQ,
main.SumM
from MAIN_2006_10_XHG12 main,
Base_Product_HGHS_2006 hs
where main.productcode = hs.productcode;
commit;update Temp_Fact_XHG12 set CityCode = substr(citycode,1,2)||'99'
where CityCode in ('1303','3205','3206','3207','3303','3706','4404','4405','4408','4505','5107','6546');
commit;insert into Fact_XHG12_2006_10
select TimeCode,
IEFlag,
CustomCode,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode,
sum(Quantity),
sum(Money),
sum(SumQ),
sum(SumM)
from Temp_Fact_XHG12
group by TimeCode,
IEFlag,
CustomCode,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode;
commit;drop table Fact_XHG12_New_2006_10;
create table Fact_XHG12_New_2006_10(
TimeCode Char(6),
IEFlag Char(1),
CityCode Char(4),
EntTypeCode Char(4),
TradeTypeCode Char(4),
MakerCountryCode Char(5),
ProductCode Char(10),
Quantity NUMBER(13),
Money NUMBER(13),
SumQ NUMBER(13),
SumM NUMBER(13)
) nologging;insert into Fact_XHG12_New_2006_10
select TimeCode,
IEFlag,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode,
sum(Quantity),
sum(Money),
sum(SumQ),
sum(SumM)
from Fact_XHG12_2006_10
group by TimeCode,
IEFlag,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode;
commit;以上操作分别对9i和10g进行。
执行结束后,进行查询a1=select sum(summ) from MAIN_2006_10_XHG12;
a2=select sum(summ) from Fact_XHG12_2006_10;
a3=select sum(summ) from Fact_XHG12_New_2006_10;b1=select count(*) from MAIN_2006_10_XHG12;
b2=select count(*) from Fact_XHG12_2006_10;
b2=select count(*) from Fact_XHG12_New_2006_10;a1 a2 a3 b1 b2 b3
10g 1424949842331 1419138799106 1417534542265 10109949 4682979 3844876
9i 1424949842331 1424949842331 1424949842331 10109949 4682979 3844876发现问题:10g的查询结果不正确。而9i的结果正确分别将9i和10g中的Fact_XHG12_New_2006_10表的列summ导出。
使用UltraEdit软件进行比较,发现两者相差明显。有些值9i有,10g没有。而有些值10g有,9i没有。
比如,summ=399616,该值在9i存在,而在10g中没有。
难道说10g和9i对group by语句的处理有什么区别。
如何解决这个问题,恳请大家帮助。不胜感激。
以上四条语句的结果应该完全一样。
但是奇怪的事情发生了。在3种条件下结果不一样。
DB2 V8 一样
Oracle 9i 一样
Oracle 10g 不一样表MAIN_2006_10_XHG12的数据有1000万行左右。
还有一个现象是,当我把原始数据降到50万行时,Oracle10g 下的计算结果就开始正确了。
所以我怀疑是不是Oracle10g存在未解决的Bug。不知道大家是不是遇到过此类问题。
非常着急。恳请回复。
sql原文:
drop table Fact_XHG12_2006_10;
create table Fact_XHG12_2006_10(
TimeCode Char(6),
IEFlag Char(1),
CustomCode Char(4),
CityCode Char(4),
EntTypeCode Char(4),
TradeTypeCode Char(4),
MakerCountryCode Char(5),
ProductCode Char(10),
Quantity NUMBER(13),
Money NUMBER(13),
SumQ NUMBER(13),
SumM NUMBER(13)
) nologging;
drop table Temp_Fact_XHG12;
create table Temp_Fact_XHG12(
TimeCode Char(6),
IEFlag Char(1),
CustomCode Char(4),
CityCode Char(4),
EntTypeCode Char(4),
TradeTypeCode Char(4),
MakerCountryCode Char(5),
ProductCode Char(10),
Quantity NUMBER(13),
Money NUMBER(13),
SumQ NUMBER(13),
SumM NUMBER(13)
) nologging;
insert into Temp_Fact_XHG12
select main.TimeCode,
main.IEFlag,
main.CustomCode,
main.CityCode,
main.EntTypeCode,
main.TradeTypeCode,
main.MakerCountryCode,
main.ProductCode,
main.Quantity,
main.Money,
main.SumQ,
main.SumM
from MAIN_2006_10_XHG12 main,
Base_Product_HGHS_2006 hs
where main.productcode = hs.productcode;
commit;update Temp_Fact_XHG12 set CityCode = substr(citycode,1,2)||'99'
where CityCode in ('1303','3205','3206','3207','3303','3706','4404','4405','4408','4505','5107','6546');
commit;insert into Fact_XHG12_2006_10
select TimeCode,
IEFlag,
CustomCode,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode,
sum(Quantity),
sum(Money),
sum(SumQ),
sum(SumM)
from Temp_Fact_XHG12
group by TimeCode,
IEFlag,
CustomCode,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode;
commit;drop table Fact_XHG12_New_2006_10;
create table Fact_XHG12_New_2006_10(
TimeCode Char(6),
IEFlag Char(1),
CityCode Char(4),
EntTypeCode Char(4),
TradeTypeCode Char(4),
MakerCountryCode Char(5),
ProductCode Char(10),
Quantity NUMBER(13),
Money NUMBER(13),
SumQ NUMBER(13),
SumM NUMBER(13)
) nologging;insert into Fact_XHG12_New_2006_10
select TimeCode,
IEFlag,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode,
sum(Quantity),
sum(Money),
sum(SumQ),
sum(SumM)
from Fact_XHG12_2006_10
group by TimeCode,
IEFlag,
CityCode,
EntTypeCode,
TradeTypeCode,
MakerCountryCode,
ProductCode;
commit;以上操作分别对9i和10g进行。
执行结束后,进行查询a1=select sum(summ) from MAIN_2006_10_XHG12;
a2=select sum(summ) from Fact_XHG12_2006_10;
a3=select sum(summ) from Fact_XHG12_New_2006_10;b1=select count(*) from MAIN_2006_10_XHG12;
b2=select count(*) from Fact_XHG12_2006_10;
b2=select count(*) from Fact_XHG12_New_2006_10;a1 a2 a3 b1 b2 b3
10g 1424949842331 1419138799106 1417534542265 10109949 4682979 3844876
9i 1424949842331 1424949842331 1424949842331 10109949 4682979 3844876发现问题:10g的查询结果不正确。而9i的结果正确分别将9i和10g中的Fact_XHG12_New_2006_10表的列summ导出。
使用UltraEdit软件进行比较,发现两者相差明显。有些值9i有,10g没有。而有些值10g有,9i没有。
比如,summ=399616,该值在9i存在,而在10g中没有。
难道说10g和9i对group by语句的处理有什么区别。
如何解决这个问题,恳请大家帮助。不胜感激。
以上四条语句的结果应该完全一样。
但是奇怪的事情发生了。在3种条件下结果不一样。
DB2 V8 一样
Oracle 9i 一样
Oracle 10g 不一样表MAIN_2006_10_XHG12的数据有1000万行左右。
还有一个现象是,当我把原始数据降到50万行时,Oracle10g 下的计算结果就开始正确了。
所以我怀疑是不是Oracle10g存在未解决的Bug。不知道大家是不是遇到过此类问题。
非常着急。恳请回复。
楼主确定不是由于数据不一致的原因导致的么
9i的group by col1, col2,... 执行的时候,默认把记录集order by col1, col2, ...进行排序
10g不会, 楼主比较数据的时候最好加上排序再导出进行比较
方法一:
Alter system set optimizer_features_enable='10.1.0' scope=spfile;
方法二:
Alter system set "_gby_hash_aggregation_enabled"=false scope=spfile;
楼主你的os, oracle version?
oracle version:10.2
在sqlplus中执行Alter system set "_gby_hash_aggregation_enabled"=false问题可以解决。
说明oracle10.2中group by 的hash算法在数据量非常大的时候有冲突现象发生。
至于楼上提出的问题:我觉得说明oracle10.2中group by和order by 同时存在的时候group by 子句用的根本不是hash算法,还是原来的sort算法