Array caching length before loop in PL-SQL

Question

I was wondering if the length of the array used in PL-SQL cache will FOR LOOPsignificantly affect the performance of the procedures? So it will be in the following example:

ln_count := lna_avcs.COUNT;
FOR i in 1..ln_count LOOP
--do something
END LOOP;

will be more effective than this:

FOR i in 1..lna_avcs.COUNT LOOP
--do something
END LOOP;

Let's say the dimension of the array is from 100 to 1000.

I know that in javascript the caching example can have significant improvements. thanks

Answer 1

, 1 2. , . 1 , 2 .

:

type array_t is varray(1000) of varchar2(80);
array array_t ;

:

  select dbms_random.string('X',25)
    bulk collect into array
    from dual connect by level < 1000;

1:

for j in 1..10000 loop
   for i in 1..array.count loop
     temp := array(i);
   end loop;
end loop;

2:

for j in 1..10000 loop
    vcount := array.COUNT;
    for i in 1..vcount loop
         temp := array(i);
    end loop;
end loop;

Test1 Test2,

timestart := systimestamp;
--Test1 or Test2 is here
timeend := systimestamp;
diff :=  extract(second from timeEnd - timeStart);

diff loop_test:

 insert into loop_test values
 (1, diff);

200 . loop_test :

select  id, 
        avg(exectime), 
        count(*), 
        max(exectime), 
        min(exectime) 
from loop_test
group by id

ID  AVG(EXECTIME)   COUNT(*)    MAX(EXECTIME)   MIN(EXECTIME)
1   0.797545        200         1.046           0.78
2   0.79841         200         1.045           0.78

:

array.Count , . Oracle 11g.

:

declare 
type array_t is varray(1000) of varchar2(80);
array array_t ;
temp varchar2(80);
timestart timestamp;
 timeend timestamp;
 diff number;
begin
    select dbms_random.string('X',25)
    bulk collect into array
    from dual connect by level < 1000;

for k in 1..200 loop
timestart := systimestamp;

for j in 1..10000 loop
for i in 1..array.count loop
     temp := array(i);
 end loop;
end loop;

timeend := systimestamp;

 diff :=  extract(second from timeEnd - timeStart);
dbms_output.put_line(diff || '  ' || timestart || ' ' || timeend);

 insert into loop_test values
 (1, diff);

commit;
end loop;

end;