Sorting PL/SQL Collections, the quite simple way (part two: Have the SQL Engine do the heavy lifting)

Yesterday I published an article on this blog describing an approach to sorting the elements in PL/SQL Collections:Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one) . This approach makes use of the property of PL/SQL Associative Arrays that the keys are always kept in sorted order. We do not have to do anything, just stuff the things we want to have sorted into an Associative Array and when done, retrieve them again. They will reappear properly sorted! With some additional logic, we can use this mechanism to quickly sort large collections of complex records.

However, there are a few downsides to this approach: it will only sort on VARCHAR2 and NUMBER fields – and the alphabetical sort may not be the one we require. More complex sorting rules are not supported. Besides, the code may not be as clear and elegant as we would like. So far I have not done any performance tests – that would be the next step.

But first I want to demonstrate another way of sorting PL/SQL Collections. We will make use of the TABLE, MULTISET and CAST operators to out-source the heavy lifting to the SQL Engine! This means we have the full power of SQL at our disposal for manipulating the PL/SQL collection. Note: not just for sorting, also for filtering and aggregating! There is one important requirement: the PL/SQL Collection must be based on a type that is defined in the database!

 

Let’s start with a very simple example:

create type num_tbl is table of number
/

declare
   l_num_tbl num_tbl:= num_tbl(6,12,9,1,54,21, 11, 2);

We have defined a type num_tbl and now we have an unsorted collection of that type. Our first objective: get the elements of this collection in a properly sorted order. Let’s see how simple that is:

 begin
   select cast ( multiset( select *
                          from table( l_num_tbl )
                          order by 1
                         ) as num_tbl)
   into l_num_tbl
   from dual;

Using the table operator we pretend that l_num_tbl is a table-like-recordset. We can select records from this table-like-structure in an sequence determined by a normal SQL ORDER BY clause. The result of that query is turned into a collection again, using MULTISET, and subsequently casted into the desired collection type, namely NUM_TABLE. The result is selected into the variable l_num_tbl that is the same one as we started with.

Reporting the values in the l_num_tbl collection at this point:

   l_idx:= l_num_tbl.first;
   loop
     dbms_output.put_line(l_num_tbl(l_idx));
     l_idx:= l_num_tbl.next(l_idx);
     exit when l_idx is null;
   end loop;
end;
/
1
2
6
9
11
12
21
54

PL/SQL procedure successfully completed.

And we have done it! That’s even less code than in the comparable example in Sorting PL/SQL Collections, the hard way, the intermediate way and the quite simple way (part one). Note that this exchange with the SQL Engine is only possible because l_num_tbl is based on a type that is created in the database and not defined in PL/SQL.

Sorting Collection of more complex records

What we have seen so far was relatively trivial. Let’s now look at a collection of more complex structures that we may want to sort in more advanced ways. Again, the SQL engine can only sort collections of types that are defined in the database. We define two helper types in the database:

create type sortable
is object
( value varchar2(50)
, pointer number
)
/
create type sortable_table
is table of sortable
/

These types are later used to perform the sort on the field in the records that we want to have sorted.

Now let’s meet the complex record definition and the collection we want to sort:

declare
    type complex_type is record ( last_name varchar2(50)
                                , street varchar2(50)
                                , city varchar2(50)
                                , birthdate date
                                , zipcode varchar2(15)
                                , email_address varchar2(50)
                                , many_more_elements varchar2(4000)
                                );
    type complex_type_table_type is table of complex_type;
    -- define and initialize a complex Nested Table Collection
    l_cpx_tbl complex_type_table_type:= complex_type_table_type();
    l_cpx_rec complex_type;

The records in our collection are created and added as follows:

  begin
    -- first populate the collection with a few values.
    l_cpx_rec.last_name:= 'Jellema';
    l_cpx_rec.street:= 'Rembrandtlaan';
    l_cpx_rec.email_address:= 'toby231@hitmail.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;
    l_cpx_rec.last_name:= 'Abramovic';
    l_cpx_rec.street:= 'Chelsea Road';
    l_cpx_rec.email_address:= 'abra1@notfail.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;
    l_cpx_rec.last_name:= 'Rooney';
    l_cpx_rec.street:= 'ManYou 32';
    l_cpx_rec.email_address:= 'wayne@noavail.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;
    l_cpx_rec.last_name:= 'Basten';
    l_cpx_rec.street:= 'Russia Park 1988';
    l_cpx_rec.email_address:= 'bassie@oneteam.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;

We want to sort the collection by the last_name field in the complex_type records. Currently, the order is rather random – well, actually it is the order in which the records were added to the collection:

    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' email: '||l_cpx_tbl(l_idx).email_address);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
**     Jellema email: toby231@hitmail.com
**     Abramovic email: abra1@notfail.com
**     Rooney email: wayne@noavail.com
**     Basten email: bassie@oneteam.com

The sort logic makes use of a helper collection that contains records with two field: the value field on which the complex_type records should be sorted and a pointer field that refers to a position in the l_cpx_tbl.

  declare
    l_sort_tbl sortable_table:= sortable_table();
    l_idx integer;
  begin
    l_idx:= l_cpx_tbl.first;
    -- PREPARE L_SORT_TBL

    -- loop over all elements in the l_cpx_tbl collection
    -- that we want to sort. Use the last_name for every
    -- element in l_cpx_tbl as a key for the l_sort_tbl. Associate
    -- the key with a current element in the l_cpx_tbl collection.
    loop
      l_sort_tbl.extend;
      -- note: the first field in the sortable object is the expression used for sorting!!!
      --       the second field is a pointer to the current position of the element in the l_cpx_tbl
      l_sort_tbl( l_sort_tbl.last):= new sortable( l_cpx_tbl(l_idx).last_name, l_idx);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;

The sort is again done by the SQL Engine. The sort is on the value field and the most important result is the pointers in the sorted order.

   -- SORT L_SORT_TBL
   -- using the table operator we pretend that l_sort_tbl is a table-like-recordset;
   -- we can select records from this table-like-structure in an sequence determined by a
   -- normal SQL ORDER BY clause
   -- the result of that query is turned into a collection again, using MULTISET, and casted
   -- into the desired collection type, namely SORTABLE_TABLE
   select cast ( multiset( select *
                           from table( l_sort_tbl )
                           order by 1 -- note: here we have a very simple sort-operation;
                                      -- it could be much more complex obviously
                         ) as sortable_table)
   into l_sort_tbl
   from dual;
   -- at this point, l_sort_tbl is sorted

 

The set of pointers started out as 1,2,3,4 and is now set to 2,4,1,3:

   -- The hard work was done by the SQL engine!!
   -- brief inspection of the sort helper table l_sort_tbl:
      dbms_output.put_line('Brief look at the contents of the helper table l_sort_tbl:');
      l_idx:= l_sort_tbl.first;
      loop
        dbms_output.put_line('sort index: '||l_idx
                           ||' and sort value: '||l_sort_tbl(l_idx).value
                           ||' and pointer: '||l_sort_tbl(l_idx).pointer);
        l_idx:= l_sort_tbl.next(l_idx);
        exit when l_idx is null;
      end loop;

Brief look at the contents of the helper table l_sort_tbl:
sort index: 1 and sort value: Abramovic and pointer: 2
sort index: 2 and sort value: Basten and pointer: 4
sort index: 3 and sort value: Jellema and pointer: 1
sort index: 4 and sort value: Rooney and pointer: 3

At this point we can recreate the l_cpx_tbl collection with the elements in the proper order:

  declare
    l_ctr integer:=1;
    l_temp_cpx_tbl complex_type_table_type:= complex_type_table_type();
  begin
    l_idx:= l_sort_tbl.first;
    loop
      l_temp_cpx_tbl.extend;
      -- feed into the current slot the element that is at the pointer-indicated position in the original l_cpx_tbl
      l_temp_cpx_tbl(l_temp_cpx_tbl.last):= l_cpx_tbl(l_sort_tbl(l_idx).pointer);
      l_idx:= l_sort_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
    l_cpx_tbl.delete;
    l_cpx_tbl:= l_temp_cpx_tbl;
    -- if l_cpx_tbl was a large collection
    -- then the delete operation on the collection let go of a lot of memory
    -- that we should make available to the pool
    dbms_session.free_unused_user_memory;
    -- DONE! At this point, l_cpx_tbl is properly sorted
  end;

The result when printed to the output is what we were looking for:

    dbms_output.put_line('**********************************************************');
    dbms_output.put_line('We proudly present the sorted results: ');
    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' email: '||l_cpx_tbl(l_idx).email_address);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
 end;
 /
**********************************************************
We proudly present the sorted results:
**     Abramovic email: abra1@notfail.com
**     Basten email: bassie@oneteam.com
**     Jellema email: toby231@hitmail.com
**     Rooney email: wayne@noavail.com

PL/SQL procedure successfully completed.

And we see that the sort was successful.

More advanced SQL operations on our collection

There is still quite a bit of PL/SQL code involved. Besides: we can still only sort on a single expression. And: if we leverage the SQL engine, can’t we make use of other SQL operations on our Collection? The answer is a resounding YES!

Let’s now look at a further integration with the SQL engine. Note: it is my expectation that given the more massive  data exchange from PL/SQL to SQL and back, this approach might have issues with performance and resource usage when the number or size of collection elements increases. Again, we should investigate this.

Since we want the SQL engine to have full access to the elements in the collection, we have to base our work on database types:

create type complex_type
is object
( last_name varchar2(50)
, street varchar2(50)
, city varchar2(50)
, birthdate date
, zipcode varchar2(15)
, email_address varchar2(50)
, many_more_elements varchar2(4000)
)
/

create type complex_type_table_type
is table of complex_type
/

Now the code to initializes the collection is slightly changed. Note though how similar the PL/SQL RECORD and SQL OBJECT types are!

declare
    -- define and initialize a complex Nested Table Collection
    l_cpx_tbl complex_type_table_type:= complex_type_table_type();
  begin
    -- first populate the collection with a few values.
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Jellema','Rembrandtlaan', 'Enschede', sysdate - 36*365, '7545 ZJ','toby231@hitmail.com',null );
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Abramovic','Chelsea Road', 'London', sysdate - 43*365, 'MSCW','abra1@notfail.com',null );
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Rooney','ManYou 32', 'Manchester', sysdate - 23*362, '32121','wayne@noavail.com',null );
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Basten','Russia Park 1988', 'Utrecht', sysdate - 39*365+43, '2006 WK','bassie@oneteam.com',null );

Now that we have our collection in a format understood by the SQL engine, operating on it becomes a breeze. Let’s look at an example that just sorts:

declare
    ...
    l_idx integer;
    l_sort_idx varchar2(50);
    l_cpx_rec complex_type;
  begin
  ...
    -- have the SQL engine do the heavy lifting in terms of sorting the collection!
    select cast ( multiset( select complex_type
                                   ( last_name
                                   , street
                                   , city
                                   , birthdate
                                   , zipcode
                                   , email_address
                                   , many_more_elements )
                            from table( l_cpx_tbl )
                            order by last_name -- note: here we have a very simple sort-operation;
                                               -- it could be much more complex obviously
                        ) as complex_type_table_type)
    into l_cpx_tbl
    from dual;
    -- DONE! At this point, l_cpx_tbl is properly sorted

The results of this operation look like this:

    dbms_output.put_line('**********************************************************');
    dbms_output.put_line('We proudly present the sorted results: ');
    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' email: '||l_cpx_tbl(l_idx).email_address);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
 end;
 /
**********************************************************
We proudly present the sorted results:
**     Abramovic email: abra1@notfail.com
**     Basten email: bassie@oneteam.com
**     Jellema email: toby231@hitmail.com
**     Rooney email: wayne@noavail.com

PL/SQL procedure successfully completed.

A more advanced SQL operation on the same collection is just as easy to implement:

    -- have the SQL engine do the heavy lifting in terms of sorting the collection!
    select cast ( multiset(  select complex_type
                                    ( last_name
                                    , street
                                    , city
                                    , birthdate
                                    , zipcode
                                    , email_address
                                    , many_more_elements
                                      ||' '||runcount
                                    )
                             from   ( select ct.*
                                      ,      count(*) over
                                                      (order by last_name desc) runcount
                                      from   table( l_cpx_tbl ) ct
                                      where  city <> 'London'
                                      order
                                      by     city
                                      ,      length(last_name)
                                      ,      sysdate - birthdate
                                      ,      2
                                    )
                        ) as complex_type_table_type)
    into l_cpx_tbl
    from dual;
    -- DONE! At this point, l_cpx_tbl is properly sorted

and the results are:

    dbms_output.put_line('**********************************************************');
    dbms_output.put_line('We proudly present the sorted results: ');
    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' city: '||l_cpx_tbl(l_idx).city
                         ||' email: '||l_cpx_tbl(l_idx).email_address
                         ||' birthdate: '||l_cpx_tbl(l_idx).birthdate
                         ||' many_more_elements: '||l_cpx_tbl(l_idx).many_more_elements
                         );
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
  end;
/
 **********************************************************
We proudly present the sorted results:
**     Jellema city: Enschede email: toby231@hitmail.com birthdate: 09-JUN-70 many_more_elements:  2
**     Rooney city: Manchester email: wayne@noavail.com birthdate: 14-AUG-83 many_more_elements:  1
**     Basten city: Utrecht email: bassie@oneteam.com birthdate: 23-JUL-67 many_more_elements:  3

PL/SQL procedure successfully completed.

 

Resources

Download the source code for this article: plsqlCollectionSortSQLScripts.sql