PL/SQL arrays–the index datatype

You get some interesting (but perhaps not unexpected) results when playing with the speed of array functions in PL/SQL.  This is a series of tests comparing “BY PLS_INTEGER” arrays with “BY VARCHAR2” arrays.  In all the cases, their speed is pretty much blindingly fast, but the comparison between the two seems to be dependent on the type of array function being performed.

Anyway … on to the tests

Repeated assignment to the same array element



SQL> set timing off
SQL> set serverout on
SQL> declare
  2    type vtab is table of number
  3      index by varchar2(100);
  4    type ntab is table of number
  5      index by pls_integer;
  6    p ntab;
  7    p1 vtab;
  8    q number;
  9  begin
 10    q := dbms_utility.get_time;
 11    for i in 1 .. 10000000 loop
 12      p(12345) := i;
 13      p(6789) := i;
 14    end loop;
 15    dbms_output.put_line('by number: Ela='||(dbms_utility.get_time-q));
 16    q := dbms_utility.get_time;
 17    for i in 1 .. 10000000 loop
 18      p1('12345') := i;
 19      p1('6789') := i;
 20    end loop;
 21    dbms_output.put_line('by varchar2: Ela='||(dbms_utility.get_time-q));
 22  end;
 23  /
by number: Ela=81
by varchar2: Ela=164

PL/SQL procedure successfully completed.

Repeated assignment to the contiguous array elements



SQL> declare
  2    type vtab is table of number
  3      index by varchar2(100);
  4    type ntab is table of number
  5      index by pls_integer;
  6    p ntab;
  7    p1 vtab;
  8    q number;
  9  begin
 10    q := dbms_utility.get_time;
 11    for i in 1 .. 1000000 loop
 12      p(i) := i;
 13    end loop;
 14    dbms_output.put_line('by number: Ela='||(dbms_utility.get_time-q));
 15    q := dbms_utility.get_time;
 16    for i in 1 .. 1000000 loop
 17      p1(i) := i;
 18    end loop;
 19    dbms_output.put_line('by varchar2: Ela='||(dbms_utility.get_time-q));
 20  end;
 21  /
by number: Ela=11
by varchar2: Ela=59

PL/SQL procedure successfully completed.

Repeated assignment to the contiguous but sparse array elements



SQL>
SQL> declare
  2    type vtab is table of number
  3      index by varchar2(100);
  4    type ntab is table of number
  5      index by pls_integer;
  6    p ntab;
  7    p1 vtab;
  8    q number;
  9  begin
 10    q := dbms_utility.get_time;
 11    for i in 1 .. 1000000 loop
 12      p(i*345) := i;
 13    end loop;
 14    dbms_output.put_line('by number: Ela='||(dbms_utility.get_time-q));
 15    q := dbms_utility.get_time;
 16    for i in 1 .. 1000000 loop
 17      p1(i*345) := i;
 18    end loop;
 19    dbms_output.put_line('by varchar2: Ela='||(dbms_utility.get_time-q));
 20  end;
 21  /
by number: Ela=50
by varchar2: Ela=63

PL/SQL procedure successfully completed.

Repeated assignment to the random array elements



SQL>
SQL>
SQL> set timing off
SQL> set serverout on
SQL> declare
  2    type vtab is table of number
  3      index by varchar2(100);
  4    type ntab is table of number
  5      index by pls_integer;
  6    p ntab;
  7    p1 vtab;
  8    rnd ntab;
  9    q number;
 10  begin
 11    for i in 1 .. 1000000 loop
 12      rnd(i) := trunc(dbms_random.value(1,1000000));
 13    end loop;
 14
 15    q := dbms_utility.get_time;
 16    for i in 1 .. 1000000 loop
 17      p(rnd(i)) := i;
 18    end loop;
 19    dbms_output.put_line('by number: Ela='||(dbms_utility.get_time-q));
 20    q := dbms_utility.get_time;
 21    for i in 1 .. 1000000 loop
 22      p1(rnd(i)) := i;
 23    end loop;
 24    dbms_output.put_line('by varchar2: Ela='||(dbms_utility.get_time-q));
 25  end;
 26  /
by number: Ela=34
by varchar2: Ela=100

PL/SQL procedure successfully completed.

Navigation through contiguous array elements



SQL>
SQL>
SQL>
SQL> set timing off
SQL> set serverout on
SQL> declare
  2    type vtab is table of number
  3      index by varchar2(100);
  4    type ntab is table of number
  5      index by pls_integer;
  6    p ntab;
  7    p1 vtab;
  8    rnd ntab;
  9    q number;
 10    idx pls_integer;
 11  begin
 12    for i in 1 .. 1000000 loop
 13      rnd(i) := trunc(dbms_random.value(1,1000000));
 14    end loop;
 15    for i in 1 .. 1000000 loop
 16      p(rnd(i)) := i;
 17    end loop;
 18    for i in 1 .. 1000000 loop
 19      p1(rnd(i)) := i;
 20    end loop;
 21
 22    q := dbms_utility.get_time;
 23    idx := p.first;
 24    while idx != p.last loop
 25      idx := p.next(idx);
 26    end loop;
 27    dbms_output.put_line('by number: Ela='||(dbms_utility.get_time-q));
 28
 29    q := dbms_utility.get_time;
 30    idx := p1.first;
 31    while idx != p1.last loop
 32      idx := p1.next(idx);
 33    end loop;
 34    dbms_output.put_line('by varchar2: Ela='||(dbms_utility.get_time-q));
 35  end;
 36  /
by number: Ela=8
by varchar2: Ela=42

PL/SQL procedure successfully completed.

SQL>
SQL>

Navigation through sparse array elements



SQL> set timing off
SQL> set serverout on
SQL> declare
  2    type vtab is table of number
  3      index by varchar2(100);
  4    type ntab is table of number
  5      index by pls_integer;
  6    p ntab;
  7    p1 vtab;
  8    rnd ntab;
  9    q number;
 10    idx pls_integer;
 11  begin
 12    for i in 1 .. 100000 loop
 13      rnd(i) := trunc(dbms_random.value(1,1000000));
 14    end loop;
 15    for i in 1 .. 100000 loop
 16      p(rnd(i)) := i;
 17    end loop;
 18    for i in 1 .. 100000 loop
 19      p1(rnd(i)) := i;
 20    end loop;
 21
 22    q := dbms_utility.get_time;
 23    for i in 1 .. 10 loop
 24      idx := p.first;
 25      while idx != p.last loop
 26        idx := p.next(idx);
 27      end loop;
 28    end loop;
 29    dbms_output.put_line('by number: Ela='||(dbms_utility.get_time-q));
 30
 31    q := dbms_utility.get_time;
 32    for i in 1 .. 10 loop
 33      idx := p1.first;
 34      while idx != p1.last loop
 35        idx := p1.next(idx);
 36      end loop;
 37    end loop;
 38    dbms_output.put_line('by varchar2: Ela='||(dbms_utility.get_time-q));
 39  end;
 40  /
by number: Ela=14
by varchar2: Ela=61

PL/SQL procedure successfully completed.

SQL>

3 thoughts on “PL/SQL arrays–the index datatype

  1. In the examples in the middle, wouldn’t the VARCHAR2 examples all be doing 1 million implicit conversions on the index value that the PLS_INTEGER array wouldn’t have to do? Not sure this would matter much, but seems like not apples to apples in that case.

    • Just for completeness…

      SQL> declare
      2 type vtab is table of number
      3 index by varchar2(100);
      4 type ntab is table of number
      5 index by pls_integer;
      6 p ntab;
      7 p1 vtab;
      8 q number;
      9 c varchar2(10);
      10 begin
      11 q := dbms_utility.get_time;
      12 for i in 1 .. 1000000 loop
      13 c := to_char(i);
      14 p(i) := i;
      15 end loop;
      16 dbms_output.put_line(‘by number: Ela=’||(dbms_utility.get_time-q));
      17 q := dbms_utility.get_time;
      18 for i in 1 .. 1000000 loop
      19 c := to_char(i);
      20 p1(c) := i;
      21 end loop;
      22 dbms_output.put_line(‘by varchar2: Ela=’||(dbms_utility.get_time-q));
      23 end;
      24 /
      by number: Ela=31
      by varchar2: Ela=62

      PL/SQL procedure successfully completed.

  2. Yes, I did consider that – but the alternative seemed that it may add more bias, eg

    for i in 1 .. 10000
    char_var := to_char(i)
    array(char_var) := …

    means an extra variable assignment.

    I suppose I could make a similar redundant assignment in the numeric case

    Cheers,
    Connor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s