Performance Improvement with Associative Arrays in PL/SQL, Oracle

Lets see, how the performance can be improved with the use of Associative Arrays.

We are going using Associative Arrays for caching the data to reduce the function calls or select statements.

Suppose, there is a Function (get_dept_info) which returns the dept name for a given dept no from the dept table.

In a main pl/sql block, from the emp table, all the employees will be retrieved one by one in a loop, for each emp retrieved, the above function (get_dept_info) will be called to get the dept name (we can get the dept name by joining the emp table directly with dept table also). For explaining the concept only we are using this example.

The algorithm that we are going to follow is

Create a replica table (emp_new) for the emp table

Insert more data into the table (emp_new) so for the testing.

Create a pl/sql block

In the pl/sql block, loop thru the employees in the emp_new table

for each emp, try to get the dept name :

This part we will do in two ways

1: directly calling (get_dept_info) function

2: using associative arrays

then do any processing as per the requirement. In our case we will not do anything.

Now lets how to do this:

--Connect to the user Scott

SQL> SHOW USER;
USER is "SCOTT"

--Set the TIMI ON to see the time taken to execute the commands

SQL> SET TIMI ON;

--Create a table, exact replica of emp table

SQL> CREATE TABLE emp_new AS SELECT * FROM emp;

Table created.

Elapsed: 00:00:00.10

--Change the column width of empno to store any bigger no.

SQL> ALTER TABLE emp_new MODIFY empno NUMBER;

Table altered.

Elapsed: 00:00:00.03

--A pl/sql block to insert more rows into the emp_new table

SQL> BEGIN
2 FOR I IN 1..15 LOOP
3 INSERT INTO emp_new SELECT * FROM emp_new;
4 END LOOP;
5 UPDATE emp_new SET empno = empno+ROWNUM;
6 COMMIT;
7 END;
8
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.28
SQL>
SQL> SELECT COUNT(*) FROM emp_new;

COUNT(*)
----------
491520

Elapsed: 00:00:01.23

--Create the function to return the dept name for a given dept no

SQL> CREATE OR REPLACE FUNCTION get_dept_info ( p_dept_no dept.deptno%TYPE) RETURN VARCHAR2 IS
2 v_dname dept.dname%TYPE;
3 BEGIN
4 SELECT dname INTO v_dname FROM dept WHERE deptno = p_dept_no;
5 RETURN v_dname;
6 EXCEPTION
7 WHEN OTHERS THEN
8 Raise_application_error (-20100,'Error '|| sqlerrm ||' for deptno = ' || p_dept_no);
9 END get_dept_info ;
10
11 /

Function created.

Elapsed: 00:00:00.06

--Now, execute the following code to get the dept name for the dept no

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_str VARCHAR2(32000):='In Declaration Secion';
3 v_dname dept.dname%TYPE ;
4
5 BEGIN
6 v_str :='Before For loop at 100';
7 FOR emp_data IN (SELECT * FROM emp_new WHERE deptno IS NOT NULL) LOOP --loop thru the employees
8 v_str :='Calling get_dept_info (' ||emp_data.deptno ||') at 200';
9 v_dname := get_dept_info (emp_data.deptno); --function call to get the dname
10 --DBMS_OUTPUT.PUT_LINE('For dno '|| emp_data.deptno ||' dname is '|| v_dname);
11 NULL;
12 END LOOP;
13
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 Raise_application_error(-20010,'Error '||SQLERRM ||' at '||v_str);
18 END;
19
20 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.57

--Change the code to get the dept name using associative arrays

--Create an associative array index by VARCHAR2;

--FOR each dept no retrieved from the emp table, first check in the associative arrary, if there exists a dept name

--If exists then return

--If not then, NO_DATA_FOUND exception will be raised

--In the exception handler, call the function (get_dept_info) to get the dept nam

--store the dept name in the associative array for the next use

--In this way, the no of calls to the function will be reduced(the max no of calls will be the no of distinct values in the emp table)

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_str VARCHAR2(32000):='In Declaration Secion';
3 v_dname dept.dname%TYPE ;
4 TYPE dname_type IS TABLE OF dept.dname%TYPE INDEX BY VARCHAR2(10); --Array type declaration
5 v_dname_tab dname_type; --Variable for the array
6 BEGIN
7 v_str :='Before For loop at 100';
8 FOR emp_data IN (SELECT * FROM emp_new WHERE deptno IS NOT NULL) LOOP
9 BEGIN
10 v_dname := v_dname_tab(emp_data.deptno); -- Check associative array
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN --Means the dept no is not there in the array
13 v_str :='Calling get_dept_info (' ||emp_data.deptno ||') at 200';
14 v_dname := get_dept_info (emp_data.deptno); --call the function to get the dept name
15 v_dname_tab(emp_data.deptno ) := v_dname ; --Store in the associative array for the future use
16 END;
17
18 --DBMS_OUTPUT.PUT_LINE('For dno '|| emp_data.deptno ||' dname is '|| v_dname);
19 NULL;
20 END LOOP;
21
22
23 EXCEPTION
24 WHEN OTHERS THEN
25 Raise_application_error(-20010,'Error '||SQLERRM ||' at '||v_str);
26 END;
27
28 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.76

The time taken for first block to execute is 14 secs. The time taken for the second block to execute is 2 secs. See the diff.