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!