Home » SQL & PL/SQL » SQL & PL/SQL » How to sum up values into a 419(!!) columns wide Table
 How to sum up values into a 419(!!) columns wide Table [message #208578] |
Mon, 11 December 2006 07:24  |
Echo88
Messages: 3
Registered: December 2005
|
Junior Member |
|
|
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
|
|
|
|
| Re: How to sum up values into a 419(!!) columns wide Table [message #208596 is a reply to message #208578] |
Mon, 11 December 2006 08:36  |
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
|
Senior Member |
|
|
In the first SQL, you use UNION to join the result sets. This will remove any duplicates and may even mean you get the wrong results if you happen to have two rows with identical counts from the aggregation and employee tables. For example, if you have two rows with the same key and counts of 5, 11 and 14, it will turn that into a single row rather than aggregating two rows together to give 10,22 and 28. Try switching it to UNION ALL, which preserve the duplicates and also remove an unnecessary grouping operation.
You could also break the SQL into more temporary tables e.g. pull the query from the employee table into another temporary table first and union all/group this with the aggregation table.
|
|
|
|
Goto Forum:
Current Time: Sun Dec 08 05:51:30 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
