random technical thoughts from the Nominet technical team

Query collections with SQL as if a normal table

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...
Posted by arjan on Aug 18th, 2008

You may have worked with collections in SQL and PL/SQL already. Collections are basically arrays holding columns or records of a user-defined data type. They come in various flavors. They can be defined as PL/SQL variables, but also as data types within the database. For example like this:

create type test_coll_type is table of number;

Imagine you have a procedure receiving such an array filled with id’s as an IN parameter. Within the procedure we need to check whether any of the values in this array exist within a table in the database. One’s first idea to solve this would probably be something like this:

create table x(id number, name varchar2(30));

---------- ------------------------------
1 James
2 Mark
3 John

create or replace procedure who_walks_the_dog1(p_coll   test_coll_type)
   l_name varchar2(30);
   for i in p_coll.first..p_coll.last
     if p_coll(i) is not null
      select name into l_name from x where id = p_coll(i);
      when no_data_found then null;

      dbms_output.put_line(l_name||' walks the dog');
     end if;
   end loop;
end who_walks_the_dog1;

Of course that is not very pretty code and it took me longer to write than I initially thought it would take. It also means a lot of calls to the database. That creates a lot of switches between the SQL and PL/SQL engine causing performance degradation.

It would be ideal if we could just take the list of ID’s and use it in a WHERE clause saying “WHERE id in (list of id’s)”.

Here is how you do it. You use the TABLE operator that basically converts a collection to a normal table so you can use the collection in SQL statements. That looks like this:

create or replace procedure who_walks_the_dog2(p_coll   test_coll_type)
type name_tabtyp is table of varchar2(30) index by binary_integer;
name_tab name_tabtyp;
select name bulk collect into name_tab from x where id in (select column_value from table(p_coll));

if name_tab.count > 0
for i in name_tab.first..name_tab.last
dbms_output.put_line(name_tab(i)||' walks the dog');
end loop;
end if;
end who_walks_the_dog2;

The TABLE operator allows you to write shorter, simpler code that’s more intuitive to read and it should be faster especially when you process a lot of rows. Test it like this.

   test_coll test_coll_type := test_coll_type();
   test_coll(1) := 1;
   test_coll(2) := 2;


Collections and the TABLE operator have lots more interesting applications. Perhaps more in the next post.

One Response

  1. techblog » Blog Archive » Performance gains of the TABLE operator Says:

    [...] up from my previous post on using SQL on collections using the TABLE operator, I did a little test on my assertion that the [...]

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

Recent Posts

Highest Rated