oracle query optimization: summing across columns

Question

I need help with optimizing a query.

I have a table with many account type columns and corresponding values

if accounttype1 contains an account id say "2000" and the corresponding value is in chargeamount1, if accounttype1 contains an account id say "2500" and the corresponding value is in chargeamount2 and so forth.

I have to get the sum of a specified account type across all the account type columns by summing the corresponding chargeamount values. I have tried to achieve this with a function as shown below...

select msisdn,
       fn_get_acc_amount ('2000', accounttype1, chargeamount1,  accounttype1_a, chargeamount1_a, accounttype2, chargeamount2, accounttype2_a,
       chargeamount2_a,accounttype3, chargeamount3,accounttype3_a,chargeamount3_a
      )data_account_4508_usage
      from  data_cdr  partition (p20160521)

the function:

    CREATE OR REPLACE function fn_get_acc_amount(p_account varchar2,
p_accounttype1 varchar2, p_chargeamount1 varchar2,p_accounttype1_a varchar2, p_chargeamount1_a varchar2,
p_accounttype2 varchar2, p_chargeamount2 varchar2,p_accounttype2_a varchar2, p_chargeamount2_a varchar2,
p_accounttype3 varchar2, p_chargeamount3 varchar2,p_accounttype3_a varchar2, p_chargeamount3_a varchar2)
return number as

v_accum number := 0;

begin
  if p_accounttype1  = p_account then
    v_accum := v_accum + to_number(nvl(p_chargeamount1,0));
  end if;

  if p_accounttype1_a = p_account then
    v_accum := v_accum + to_number(nvl(p_chargeamount1_a,0));
  end if;

  if p_accounttype2 = p_account then
    v_accum := v_accum + to_number(nvl(p_chargeamount2,0));
  end if;

  if p_accounttype2_a = p_account then
    v_accum := v_accum + to_number(nvl(p_chargeamount2_a,0));
  end if;

  if p_accounttype3 = p_account then
    v_accum := v_accum + to_number(nvl(p_chargeamount3,0));
  end if;

  if p_accounttype3_a = p_account then
    v_accum := v_accum + to_number(nvl(p_chargeamount3_a,0));
  end if;

  return nvl(v_accum,0);

end;
/

on my IDE this runs and returns a value... but having to create or insert the output into another table takes for ever because the table data_cdr has about 30 million records.

Below is my attempt to optimize the function:

CREATE OR REPLACE function fn_get_acc_amount(
p_account varchar2,
p_accounttype1 varchar2, p_chargeamount1 varchar2,p_accounttype1_a varchar2, p_chargeamount1_a varchar2,
p_accounttype2 varchar2, p_chargeamount2 varchar2,p_accounttype2_a varchar2, p_chargeamount2_a varchar2,
p_accounttype3 varchar2, p_chargeamount3 varchar2,p_accounttype3_a varchar2, p_chargeamount3_a varchar2)
return number 
result_cache
is
v_accum number;
v_sql varchar2 (4000);
begin
 v_accum :=0;
 v_sql:='with acc_table as 
        (
            select :1 accounttype, nvl(:2,0) chargeamount from dual union all
            select :3, nvl (:4,0 ) from dual union all 
            select :5, nvl (:6,0 ) from dual union all 
            select :7, nvl (:8,0 ) from dual union all 
            select :9, nvl (:10,0 ) from dual union all 
            select :11, nvl (:12,0 ) from dual                 
        )
  select sum(chargeamount) from acc_table
  where accounttype ='||p_account;

  execute immediate v_sql into v_accum
  using p_accounttype1 , p_chargeamount1 ,p_accounttype1_a , p_chargeamount1_a ,
        p_accounttype2 , p_chargeamount2 ,p_accounttype2_a , p_chargeamount2_a ,
        p_accounttype3 , p_chargeamount3 ,p_accounttype3_a , p_chargeamount3_a ;

  return nvl(v_accum,0);

end;
/

it is still running for ever. I need suggestions that i can you to optimize this. i have tried looking to the pivot queries but i could not get it to work.

i will be grateful for any insight i can get .

Answer 1

A straight SQL statement is almost certain to run (much) faster than anything you can do in a function - especially if you read the rows one at a time.

Something like this may work:

select [whatever other columns,]
         case p_accounttype1   when '2000' then p_chargeamount1   else 0 end +
         case p_accounttype1_a when '2000' then p_chargeamount1_a else 0 end +
         case p_accounttype2   when '2000' then p_chargeamount2   else 0 end +
         case p_accounttype2_a when '2000' then p_chargeamount2_a else 0 end +
         case p_accounttype3   when '2000' then p_chargeamount3   else 0 end +
         case p_accounttype3_a when '2000' then p_chargeamount3_a else 0 end
       as data_account_4508_usage
from [......]

or, if you need the sum of these sums across all columns (I couldn't tell from the narrative and I didn't read your function, sorry) - you can wrap the entire sum of case expressions within one big outer SUM().

You can plug in a bind variable for '2000' if you may need to run this for different account id's.

With that said, if you have any power in your organization, you should advocate strongly for normalizing your data model. You shouldn't have to jump through such hoops for what should be one of the simplest possible queries. Good luck!

Answer 2

the first idea I have is to change your query like this:

select msisdn, sum(amount) from (
select msisdn, chargeamount1 as amount from data_cdr partition (p20160521) where accounttype1 = '2000' and chargeamount1 is not null
union all
select msisdn, chargeamount1_a from data_cdr partition (p20160521) where accounttype1_a = '2000' and chargeamount1_a is not null
union all
select msisdn, chargeamount2 from data_cdr partition (p20160521) where accounttype2 = '2000' and chargeamount2 is not null
union all
select msisdn, chargeamount2_a from data_cdr partition (p20160521) where accounttype2_a = '2000' and chargeamount2_a is not null
union all
select msisdn, chargeamount3 from data_cdr partition (p20160521) where accounttype3 = '2000' and chargeamount3 is not null
union all
select msisdn, chargeamount3_a from data_cdr partition (p20160521) where accounttype3_a = '2000' and chargeamount3_a is not null
) as tmp
group by msisdn

of course your amounts columns are integer (not string) and you have different indexes on every accounttype column with all needed fields