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

Thomas wrote:
> 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

You could probably try breaking it up into separate pieces and then join those together combined with an insert statement.

Before you start doing that I would question the design. 400 columns in a table is fairly unusual and may reflect a bad ERD. Received on Mon Dec 11 2006 - 07:52:26 CST