1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Add contents of Associative array

Discussion in 'SQL PL/SQL' started by arunshankar.c, Aug 30, 2012.

  1. arunshankar.c

    arunshankar.c Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hi All,

    Need help.

    I have an associative array as below:

    TYPE va_abc_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    Now, my va_abc_array contains below data:

    va_abc_array(1) := 40;
    va_abc_array(2) := 50;
    va_abc_array(3) := 60;
    va_abc_array(4) := 70;

    Please let me know if there is any inbuilt function which can add the contents of Associative array va_abc_array.
    => va_abc_array(1)+va_abc_array(2)+va_abc_array(3)+va _abc_array(4) = 40 + 50 + 60 + 70 which sums up to 220

    Also, please let me know any better way (considering the performance aspect) because I know I can do this using For Loop. I am avoiding using the FOR Loop because of huge data in multiple associative arrays as above which may result in performance degradation.

    Thanks,
    Arun
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why do you think a for loop in this context would degrade performance? I do not find that to be true:

    Code (SQL):
    SQL> DECLARE
      2          TYPE va_abc_array IS TABLE OF NUMBER INDEX BY binary_integer;
      3          v_abc_array va_abc_array;
      4          v_arr_ttl       NUMBER:=0;
      5
      6  BEGIN
      7          FOR i IN 1..4 loop
      8                  v_abc_array(i):=30+(i*10);
      9          END loop;
     10
     11          FOR i IN 1..4 loop
     12                  v_arr_ttl:= v_arr_ttl + v_abc_array(i);
     13          END loop;
     14
     15          dbms_output.put_line(v_arr_ttl);
     16
     17  END;
     18  /
    220
     
    PL/SQL PROCEDURE successfully completed.
     
    Elapsed: 00:00:00.00
    SQL>
    SQL> DECLARE
      2          TYPE va_abc_array IS TABLE OF NUMBER INDEX BY binary_integer;
      3          v_abc_array va_abc_array;
      4          v_arr_ttl       NUMBER:=0;
      5
      6  BEGIN
      7          FOR i IN 1..4000000 loop
      8                  v_abc_array(i):=30+(i*10);
      9          END loop;
     10
     11          FOR i IN 1..4000000 loop
     12                  v_arr_ttl:= v_arr_ttl + v_abc_array(i);
     13          END loop;
     14
     15          dbms_output.put_line(v_arr_ttl);
     16
     17  END;
     18  /
    80000140000000
     
    PL/SQL PROCEDURE successfully completed.
     
    Elapsed: 00:00:01.31
    SQL>
    SQL> DECLARE
      2          TYPE va_abc_array IS TABLE OF NUMBER INDEX BY binary_integer;
      3          v_abc_array va_abc_array;
      4          v_arr_ttl       NUMBER:=0;
      5
      6  BEGIN
      7          FOR i IN 1..8000000000 loop
      8                  v_abc_array(i):=30+(i*10);
      9          END loop;
     10
     11          FOR i IN 1..8000000000 loop
     12                  v_arr_ttl:= v_arr_ttl + v_abc_array(i);
     13          END loop;
     14
     15          dbms_output.put_line(v_arr_ttl);
     16
     17  END;
     18  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01426: NUMERIC overflow
    ORA-06512: at line 7

    Elapsed: 00:00:00.01

    SQL>
    You can't add enough data to the PL/SQL table to make this perform poorly; the numeric overflow is from attempting to load the PL/SQL table, not computing the sum. Also note the execution times, in seconds and hundredths of a second -- even for 4 million table elements this block runs in under 2 seconds and that time includes loading the table and computing the sum. And, no, there is no built-in function to compute the sum of PL/SQL table contents.