Problem with group by with parallel
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Problem with group by with parallel

Hybrid View

  1. #1
    Join Date
    Sep 2005
    Posts
    278

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    /*+parallel(a,8)*/

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

    Tamil

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    sorry mistyped

    it should be like:

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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you get correct result when you ran w/o parallel hint?

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

    Tamil

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    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

  6. #6
    Join Date
    Sep 2005
    Posts
    278
    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.

  7. #7
    Join Date
    Sep 2005
    Posts
    278
    thanks tamil.. ur query is working fine

    can u tell me the reason

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    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%'

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  10. #10
    Join Date
    Sep 2005
    Posts
    278
    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
  •  


Click Here to Expand Forum to Full Width