-
Problem with group by Clause
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
Last edited by tabreaz; 02-08-2006 at 12:24 PM.
-
/*+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
Last edited by tabreaz; 02-09-2006 at 03:35 AM.
-
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%'
-
 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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|