Ofter application developers are puzzled with task to allow the user to pick multiple items from some list, and then perform something for all selected items. For example the task could be to display more details, or to increase salary of selected employees.

One of the choices could be to pass the list as a comma separated list in a string parameter. For example:

declare

  procedure print_collection(p_emp_ids_collection in string) is
    c sys_refcursor;
    v_employee_id employees.employee_id%type;
    v_first_name employees.first_name%type;
    v_last_name employees.last_name%type;
    v_email employees.email%type;
    cnt number := 0;
  begin
    dbms_output.put_line('Starting to print');
    open c for
      'select employee_id, first_name, last_name, email
       from employees
       where employee_id in ('||p_emp_ids_collection||')';

    loop
    fetch c into v_employee_id , v_first_name , v_last_name, v_email ;
    exit when c%notfound;
    dbms_output.put_line('Emp ID='||v_employee_id||' , name='||v_first_name||' '||v_last_name||', email='||v_email );
    cnt := cnt + 1;
  end loop;
  dbms_output.put_line('End of printing '||cnt||' records.');
end;

begin
  print_collection('100,102,108');
end;
/

However this is not good in most occasions. It has a number of problems:

• It is prone to sql injection
• It requires dynamic sql, which will be syntactically checked only in run time, not at compile time of the program.
• You cannot use this approach in a declarative cursor for loop, which has fetch arraysize of 100. So you will either fetch records with arraysize 1 (using plsql fetch), or you will have to write with DBMS_SQL and use DEFINE_ARRAY and FETCH_ROWS to achieve fetching in a portion of more than 1 row.

declare
  the_emp_ids_collection t_employee_ids_collection;

  procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is
  begin
    dbms_output.put_line('Starting to print');
    for r in (
      select *
      from employees
      where employee_id in (select * from table(p_emp_ids_collection)))
    loop
      dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
    end loop;
    dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');
  end;

begin
  select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)
  into the_emp_ids_collection from employees;
  print_collection(the_emp_ids_collection);

  the_emp_ids_collection := t_employee_ids_collection (100,102,108);
  print_collection(the_emp_ids_collection);

end;
/

The last variant is a very good one, and can be used in many cases. It can be used with bind variables from Java or ODP.NET (and others probably). However it could have one problem – the optimizer doesn’t currently care about how many entries the collection contains. It assumes a default cardinality, and uses always the same value.

I thought that it could do dynamic sampling, but even if it does – it will do it only on the first attemp to choose an execution plan. In Oracle 11g the cursor can be bind aware (you can even force this with BIND_AWARE hint – ).

some alternative to this could be the variant where MEMBER OF is used like in this example.

declare
  the_emp_ids_collection t_employee_ids_collection;

  procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is
  begin
    dbms_output.put_line('Starting to print');
    for r in (
      select *
      from employees
      where employee_id member of p_emp_ids_collection )
    loop
      dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
    end loop;
    dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');
  end;

begin
  select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)
  into the_emp_ids_collection from employees;
  print_collection(the_emp_ids_collection);

  the_emp_ids_collection := t_employee_ids_collection (100,102,108);
  print_collection(the_emp_ids_collection);

end;
/

I expect the second variant to have worse performance in most cases, since the member of operation cannot be transformed to a sqlset rowset, and it will process every row of the main table. Even for small tables i think that the performance of hash join is better than memeber of implementation for a collection, because AFAIK ‘normal table of’ collections (as opposed to collection which are table of XXX indexed by varchar2) is not implemented as a hash map, but rather as a list (so it will be like nested loop with full scan of table and for every row full scan of collection). Yet, I still have to test this (there might be difference between a MULTISET or SET ).

However there could be implementations that will use a different sql statement based on the number of collection elements. For example if less that 10 records are used – a hint cardinality(5) could be added to the query, if between 10 and 100 – then a hint cardinality 50 could be used, for between 100 and 1000 – cardinality 500, and cardinality 5000 in other cases. With this approach the optionmizer can generate different execution plans depending on the size of collection – so sometimes it will prefer a nested loops plan for small (<10) collections, or a hash join and full table scans for larger collections.

declare
  the_emp_ids_collection t_employee_ids_collection;

  procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is
  begin
    dbms_output.put_line('Starting to print');
    if p_emp_ids_collection.length = 0 then
      null; -- no records, so no need to do the select at all
    elsif p_emp_ids_collection.length < 10 then
      for r in (
        select *
        from employees
where employee_id in (select /*+ cardinality(z,5) */ * from table(p_emp_ids_collection) z )
      loop
        dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
      end loop;
    elsif p_emp_ids_collection.length < 100
      for r in (
        select *
        from employees
where employee_id in (select /*+ cardinality(z,50) */ * from table(p_emp_ids_collection) z )
      loop
        dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
      end loop;
    elsif p_emp_ids_collection.length < 1000
      for r in (
        select *
        from employees
where employee_id in (select /*+ cardinality(z,500) */ * from table(p_emp_ids_collection) z )
      loop
        dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
      end loop;
    else
      for r in (
        select *
        from employees
where employee_id in (select /*+ cardinality(z,5000) */ * from table(p_emp_ids_collection) z )
      loop
        dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );
      end loop;
    end if;
    dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');
  end;

begin
  select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)
  into the_emp_ids_collection from employees;
  print_collection(the_emp_ids_collection);

  the_emp_ids_collection := t_employee_ids_collection (100,102,108);
  print_collection(the_emp_ids_collection);

end;
/

Please note that cardinality hint is not documented, as is BIND_AWARE, and may not be supported on all versions. It works on Oracle 10.2, and I think it no longer works in 11gR2. However you could replace it with some that causes similar effects. You could use hints to driver specific join order, method for selecting the original table /*+ FULL(empooyees) */ ), decision to use an index, or the join method – e.g. USE_NL for nested loops or HASH_SJ HASH SEMI join.