6 Replies Latest reply: Jul 25, 2014 4:01 AM by aromani RSS

    GroupBy wrong output

    aromani

      Hi.

      I'm tring to load an aggregatate table from a detailed one,  let's say F is the detailed one and F_AGG the aggregate one.

      Table F has 150,000,000 records.

       

      F(field1, field2, field3, field4, field5, field6, field7) where fielld1 to field6 are the primary key.

      F_AGG(field1, field2, field3, fieldA, fieldB) where field1 to field3 are the primary key and fieldA=SUM( field6 ) and fieldB=SUM( field7 )

       

      I'm doing it with an external etl job: OCI (group by query) ---> sequential file --->OCI (Truncate / Insert)

      The "group by" fields in the extract query are the F_AGG's PK.

       

      Here is the query:

      select field1, field2, field3, sum( field6 ), sum( field7 )

      from table

      group by field1, field2, field3

       

      This job worked fine for years, but some days ago it started to have PK problems loading F_AGG.

      The problem is that the extract query produces 2 records for the same "group by" fields, that is the F_AGG's PK.

       

      The group by query output is

      field1field2 field3 fieldAfieldB
      abc1000015000
      abc2000040000

      insted of

      field1field2 field3 fieldAfieldB
      abc3000045000

       

      When I limit the output dimension the problem doensn't occur

      (directly filtering on detailed records adding a where condition in the above query or filtering the entire query using external query).

       

      It seems to be a problem related with Oracle temporary areas.

      Here's a picture of the TEMP tablespace during the query execution:

      agg3_tot_1.JPG

       

      The second file TEMP02.DBF was added to TEMP just some days ago  using this script:

       

      -- 21/7/2014

      alter TABLESPACE "TEMP"

        add tempfile 'D:\ORACLE\BISE1\ORADATA\BISE1DB\TEMP02.DBF'

        SIZE 4096M REUSE AUTOEXTEND

        ON NEXT  1024M MAXSIZE  32767M

      ;

       

      Maybe I've done in a wrong way ?

      I've also read about a problem related to Oracle Algorithm HASH GROUP BY. Could it be related to this scenario ?

       

      Oracle db version is 10g (10.2.0.5.0).

       

       

      Many thanks for your help.

      Andrea