| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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
>
> and many greetinx from germany
>
> Thomas
I suspect that the problem is with the UNION clause. Will UNION ALL work for your situation in place of the UNION?
Also, looking at your query it appears that you are requiring Oracle to
do a bit more work than necessary. Are the IS_MALE_FLG, WHEN
IS_OLDER_T_SIXTY, and IS_YOUNGER_T_THIRTY columns numeric columns or
are they CHAR (or VARCHAR2)? You are treating them as CHAR or
VARCHAR2.
INSERT INTO TEMP_AGGREGATION_TABLE (
MANDANT,
KTOID,
DWH_DAT, ANZAM00, ANZAM01, ANZBM00, ANZBM01, ANZCM00, ANZCM01, SUMAM00, SUMAM01, SUMBM00, SUMBM01, SUMCM00,
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 (
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,
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
Replacement SQL:
SELECT
MANDANT,
KTOID,
DWH_DAT,
COUNT(DECODE(IS_MALE_FLG,'1',1,NULL)) AS ANZAM00,
NULL AS ANZAM01,
COUNT(DECODE(IS_OLDER_T_SIXTY,'1',1,NULL)) AS ANZBM00,
NULL AS ANZBM01,
COUNT(DECODE(IS_YOUNGER_T_THIRTY,'1',1,NULL)) AS ANZCM00,
NULL AS ANZCM01,
SUM(DECODE(IS_MALE_FLG,'1',SALARY,0)) AS SUMAM00,
NULL AS SUMAM01,
SUM(DECODE(IS_OLDER_T_SIXTY,'1',SALARY,0)) AS SUMBM00,
NULL AS SUMBM01,
SUM(DECODE(IS_YOUNGER_T_THIRTY,'1',SALARY,0)) AS SUMCM00,
NULL AS SUMCM01
FROM
EMPLOYEE_TABLE
GROUP BY
MANDANT,
KTOID,
DWH_DAT
UNION ALL
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Dec 11 2006 - 09:10:54 CST
 |
 |