How to sum up values into a 419(!!) columns wide Table

From: Thomas <echo88de_at_yahoo.de>
Date: 11 Dec 2006 03:10:19 -0800
Message-ID: <1165835419.319212.27770@79g2000cws.googlegroups.com>

Hello,

I have a quite difficult question. I think this is for your cracks... My Problem is I have a table 17 Mio. rows and 419 columns (3 varchar2 and 416 number) where I want to sum up/aggregate transaction data extracted from a second table. The result should be saved into a temporary table whith the same structure as the first one. To make it easier here to understand I transfered it into a real world example reducing the number of columns: I have a table named aggregation_table where I summed up the salary of employees and there total number according to different criteria (like age or sex) for the last year. They all should be saved into another table for further processing. The table itself should then represent this years values (marked with 00) as far as last years values (marked with 01 in this new table but extracted from columns with '00' from the "old" aggregation_table).
So we developed an SQL which should achieve this:

---

insert into temp_aggregation_table
(mandant, ktoid, dwh_dat
, ANZAM00, ANZAM01
, ANZBM00, ANZBM01
, ANZCM00, ANZCM01
, SUMAM00, SUMAM01
, SUMBM00, SUMBM01
, SUMCM00, SUMCM01

)
select

   mandant, ktoid, dwh_dat

   , SUM(ANZAM00) as ANZAM00, SUM(ANZAM01) as ANZAM01
   , SUM(ANZBM00) as ANZBM00, SUM(ANZBM01) as ANZBM01
   , SUM(ANZCM00) as ANZCM00, SUM(ANZCM01) as ANZCM01
   , SUM(SUMAM00) as SUMAM00, SUM(SUMAM01) as SUMAM01
   , SUM(SUMBM00) as SUMBM00, SUM(SUMBM01) as SUMBM01
   , SUM(SUMCM00) as SUMCM00, SUM(SUMCM01) as SUMCM01

from (

   select mandant,ktoid, dwh_dat

     , count(A) as ANZAM00, NULL as ANZAM01
     , count(B) as ANZBM00, NULL as ANZBM01
     , count(C) as ANZCM00, NULL as ANZCM01
     , sum(A) as SUMAM00  , NULL as SUMAM01
     , sum(B) as SUMBM00  , NULL as SUMBM01
     , sum(C) as SUMCM00  , NULL as SUMCM01
   from (
         select mandant, ktoid, dwh_dat,
          (case when IS_MALE_FLG='1'
           then SALARY else NULL END) as A,
          (case when IS_OLDER_T_SIXTY='1'
 	   then SALARY else NULL END) as B,
          (case when IS_YOUNGER_T_THIRTY='1'
	   then SALARY else NULL END) as C
           from employee_table
        )

   group by mandant,ktoid, dwh_dat
   UNION
   select

        mandant,  ktoid, dwh_dat
        , ANZAM00 , ANZAM01
        , ANZBM00 , ANZBM01
        , ANZCM00 , ANZCM01
        , SUMAM00 , SUMAM01
        , SUMBM00 , SUMBM01
        , SUMCM00 , SUMCM01
       from aggregation_table
     )

group by mandant,ktoid, dwh_dat

---

The 419-columns-variant looks the same but has Q (quarter), additionally to M (month). E,H,P,R,S additionally to A,B,C and the values reach from 00 to 12 instead only from 00 to 01. But we than have the problem (with 419 columns variant) that oracle mentiones a "sort key too long" error.
My First question is how can we extend this oracle internal memory where the sort key is saved, if any? Or how can we prevent the ORA-01467: sort key too long error?

So we tried to circumvent the problem with the following SQL. But at this I also need some suggestions on optimizing because this is very SLOW:
A change to PL/SQL is impossible because these statements were invoked from another application which can only pass sqls o the Database.

---

insert into temp_aggregation_table
(
mandant, ktoid, dwh_dat
, ANZAM00, ANZAM01
, ANZBM00, ANZBM01
, ANZCM00, ANZCM01
, SUMAM00, SUMAM01
, SUMBM00, SUMBM01
, SUMCM00, SUMCM01

)

with
in_a as

     (
   select mandant, ktoid, dwh_dat

      , SUM(ANZAM00) as ANZAM00
      , SUM(ANZBM00) as ANZBM00
      , SUM(ANZCM00) as ANZCM00
      , SUM(SUMAM00) as SUMAM00
      , SUM(SUMBM00) as SUMBM00
      , SUM(SUMCM00) as SUMCM00
     from (
       select mandant,ktoid, dwh_dat
          , count(A) as ANZAM00
	  , count(B) as ANZBM00
	  , count(C) as ANZCM00
	  , sum(A) as SUMAM00
	  , sum(B) as SUMBM00
	  , sum(C) as SUMCM00
        from (
	    select mandant, ktoid, dwh_dat,
	      (case when IS_MALE_FLG='1'
		then SALARY else NULL END) as A,
	      (case when IS_OLDER_T_SIXTY='1'
	        then SALARY else NULL END) as B,
       	      (case when IS_YOUNGER_T_THIRTY='1'
		then SALARY else NULL END) as C
              from employee_table
             )
	group by mandant,ktoid, dwh_dat
        UNION
        select
   	  mandant,  ktoid, dwh_dat
	  , ANZAM00
	  , ANZBM00
	  , ANZCM00
	  , SUMAM00
	  , SUMBM00
	  , SUMCM00
	from aggregation_table
         )
      group by mandant,ktoid, dwh_dat
      ),
in_b as
     (
   select mandant,  ktoid, dwh_dat
     , ANZAM01
     , ANZBM01
     , ANZCM01
     , SUMAM01
     , SUMBM01
     , SUMCM01
     from aggregation_table
     )

select
in_a.mandant, in_a.ktoid, in_a.dwh_dat
, in_a.ANZAM00, in_b.ANZAM01
, in_a.ANZBM00, in_b.ANZBM01
, in_a.ANZCM00, in_b.ANZCM01
, in_a.SUMAM00, in_b.SUMAM01
, in_a.SUMBM00, in_b.SUMBM01
, in_a.SUMCM00, in_b.SUMCM01

from in_a, in_b

where in_a.mandant=in_b.mandant
  and in_a.ktoid=in_b.ktoid
  and in_a.dwh_dat=in_b.dwh_dat ;
======================================

I hope this post is not too much and you are able to help me with my problem. So I thank you in advance

and many greetinx from germany

Thomas Received on Mon Dec 11 2006 - 05:10:19 CST