View the Exhibit and examine the PL/SQL code.
The code takes a long time to execute. What would you recommend to improve performance?
A.
using NOT NULL constraint when declaring the variables
B.
using the BULK COLLECT option for query instead of cursor
C.
using WHILE.. END LOOP instead of FOR .. END LOOP
D.
using the SIMPLE_INTEGER data type instead of the NUMBER data type
B
B
http://stackoverflow.com/questions/2715736/pl-sql-bulk-collect-into-associative-array-with-sparse-key
We can populate associative arrays with bulk collect but only if the index is an integer, and we are happy to index by (an implicit) ROWNUM, i.e not a sparse key…
SQL> declare
2 type n_array is table of varchar2(30)
3 index by binary_integer;
4 emp_names n_array;
5 begin
6 select ename
7 bulk collect into emp_names
8 from emp ;
9
10 dbms_output.put_line(‘count=’||emp_names.count()
11 ||’::last=’||emp_names.last());
12 dbms_output.put_line(emp_names(19));
13
14 end;
15 /
count=19::last=19
FEUERSTEIN
PL/SQL procedure successfully completed.
SQL>
It’s D.
Cursor FOR loops already use BULK COLLECT since Oracle Database 10g according to Steven Feuerstein. And corresponding Oracle course strongly suggests using SIMPLE_INTEGER data type instead of PLS_INTEGER (not saying about NUMBER, which is a really bizarre choice for a counter). When compiled in NATIVE mode it can improve performance by an order of magnitude, given that the program unit consists mostly of computations rather than SQL. When compiled in an INTERPRETED mode performance may not increase that much, but at least it stays the same.