Problem with group by with parallel

There is strange problem with group by results
when running in parallel

the table recs have nearly 180 million records

insert into tab1
select /*+parallel(a,8)*/
transcode, filename, rectype, count(*), sum(charge), sum(units)
from recs
group by transcode, filename, rectype

the above is giving some strange reults for some files

for example the output of filename "XYZ" is
DBFG, XYZ, 10, 2000, 3000, 10000
DBFG, XYZ, 20, 2000, 3000, 10000
DBFG, XYZ, 30, 2000, 3000, 10000

when executed as

insert into tab1
select /*+parallel(a,8)*/
transcode, filename, rectype, count(*), sum(charge), sum(units)
from recs
where recs.filename = 'XYZ'
group by transcode, filename, rectype

the output is

DBFG, XYZ, 10, 3000, 6000, 12000
DBFG, XYZ, 20, 5000, 7345, 13467
DBFG, XYZ, 30, 2000, 3000, 10000

this output is correct when comparing to the previous one

I could not able to recognize the actual problem, please can someone help me

/*+parallel(a,8)*/

Can you tell me alias "a" refers to which table?

Tamil

sorry mistyped

it should be like:

select .......
from recs a
group .......

Did you get correct result when you ran w/o parallel hint?

Try /*+ FULL(a) PARALLEL(a, 8) */

Tamil

which oracle version are you using?

what do you get if you execute:

insert into tab1
select /*+ parallel(a,8) */
transcode, filename, rectype, count(*), sum(charge), sum(units)
from recs a
where recs.filename like 'XY%'
group by transcode, filename, rectype

thanks for ur replies


as tamil said i tried

but there was still some difference
but the number of files which have differences get reduced

iam using oracle 10g

mike iam getting correct results while running such

thanks tamil.. ur query is working fine

can u tell me the reason

what do you get with:

select b.*
from
(select /*+ FULL(a) PARALLEL(a, 8) */
transcode, filename, rectype, count(*) cnt, sum(charge) sum_c, sum(units) sum_u
from recs a
group by transcode, filename, rectype) b
where b.filename like 'XY%'

Quote:

---

Originally Posted by tabreaz

thanks tamil.. ur query is working fine

can u tell me the reason

---

There may be some bug in oracle kernels. Do you use PGA?

Tamil

yes i use pga.

mike i get correct results if i use that query

Disable PGA, and then re-run your original query.
If you get wrong result set, then PGA may be the culprit.

Tamil