Tuesday, September 14, 2010

Oracle PL/SQL - cut down round-trips to database with collections

Introduction

Using PL/SQL collections you could combine the master and detail tables together in a single SELECT that convert the detail records into a collection type. This feature has enormous significance for data intensive programs (almost all enterprise applications) because the number of round-trips can be cut down. This is happening because we do not incurring the overhead of duplicating the master record with each and every detail record.

So imagine that I use following SELECT in my version of popular HR application:


SELECT
d.department_id,
d.department_name,
e.employee_id,
e.first_name,
e.last_name
FROM departments d
INNER JOIN employees e
ON d.department_id = e.department_id


Ok, here the departments table act as master table of employee table. So for each department we have zero or more employees.

Now, consider following example:


SET SERVEROUTPUT ON

CREATE OR REPLACE TYPE employees_ids_t AS TABLE OF NUMBER(6);
/
DECLARE
CURSOR dep_emp_cur IS
SELECT
d.department_id,
d.department_name,
CAST(MULTISET(SELECT e.employee_id
FROM employees e
WHERE d.department_id = e.department_id)
AS employees_ids_t) AS emp_collection
FROM departments d;
dep_emp_row dep_emp_cur%ROWTYPE;

row_index PLS_INTEGER;


BEGIN
OPEN dep_emp_cur;

LOOP

FETCH dep_emp_cur INTO dep_emp_row;

EXIT WHEN dep_emp_cur%NOTFOUND;

DBMS_OUTPUT.put(dep_emp_row.department_id || ', '
|| dep_emp_row.department_name || ': employees: ');

row_index := dep_emp_row.emp_collection.FIRST;
LOOP
EXIT WHEN row_index IS NULL;

DBMS_OUTPUT.put(dep_emp_row.emp_collection(row_index) || ', ');

row_index := dep_emp_row.emp_collection.NEXT(row_index);

END LOOP;

DBMS_OUTPUT.put_line('');

END LOOP;

CLOSE dep_emp_cur;

END;


What the hell is happening here?

Ok look at this pictures from Toad:
When I execute first simple JOIN I get something like this:



You can see how master table is repeated for each detail table.

But if I run second code, you will get something different:



Here you have "blurred" master data with detail data in _single_ row and no repeat of master whatsoever. This is powerful stuff!

No comments:

Post a Comment