6 Replies Latest reply on Jul 25, 2014 9:01 AM by aromani

    GroupBy wrong output



      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

      insted of

      field1field2 field3 fieldAfieldB


      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:



      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'


        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 (



      Many thanks for your help.