Posted by 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>
Learning is not a spectator sport 
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.
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