Important: wrong Oracle results in parallel query

Hi all,

we did the following on Sun Solaris 64bit 9.2.0.1.0, Sun Solaris 32bit 8.1.7.4.0, Sun Solaris 32bit 8.1.6.3.0, Windows2000 8.1.7.2.1:

drop table t_xxx;
drop table t_yyy;

create table t_xxx as select rpad (object_name, 30, ' ') as object_name from all_objects;
create table t_yyy as select rpad (object_name, 30, ' ') as object_name from all_objects;

alter table t_yyy modify object_name char (30);

describe t_xxx
describe t_yyy

alter table t_xxx parallel (degree 4);
alter table t_yyy parallel (degree 4);

alter session set nls_sort = german;

select object_name from t_xxx
minus
select object_name from t_yyy
/

alter session set nls_sort = binary;

select object_name from t_xxx
minus
select object_name from t_yyy
/

The last two selects yield different results. Under any circumstances I would call this an Oracle bug. Please note that the rpad usage ensures that the data are *really* the same. Please also note that even a different sort mechanism does not explain this because the set minus operation would be consistent in its semantics!

Bottom line: when doing parallel operations make sure you have nls_sort explicitly set to binary. Attention: it will be set to e. g. german in case you set nls_language!

Any comments?

Martin Received on Wed Jul 17 2002 - 09:19:06 CDT