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.<\/p>\n
One of the choices could be to pass the list as a comma separated list in a string parameter. For example:<\/p>\n
declare\r\n\r\n procedure print_collection(p_emp_ids_collection in string) is\r\n c sys_refcursor;\r\n v_employee_id employees.employee_id%type;\r\n v_first_name employees.first_name%type;\r\n v_last_name employees.last_name%type;\r\n v_email employees.email%type;\r\n cnt number := 0;\r\n begin\r\n dbms_output.put_line('Starting to print');\r\n open c for\r\n 'select employee_id, first_name, last_name, email\r\n from employees\r\n where employee_id in ('||p_emp_ids_collection||')';\r\n\r\n loop\r\n fetch c into v_employee_id , v_first_name , v_last_name, v_email ;\r\n exit when c%notfound;\r\n dbms_output.put_line('Emp ID='||v_employee_id||' , name='||v_first_name||' '||v_last_name||', email='||v_email );\r\n cnt := cnt + 1;\r\n end loop;\r\n dbms_output.put_line('End of printing '||cnt||' records.');\r\nend;\r\n\r\nbegin\r\n print_collection('100,102,108');\r\nend;\r\n\/<\/pre>\nHowever this is not good in most occasions. It has a number of problems:<\/p>\n
declare\r\n the_emp_ids_collection t_employee_ids_collection;\r\n\r\n procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is\r\n begin\r\n dbms_output.put_line('Starting to print');\r\n for r in (\r\n select *\r\n from employees\r\n where employee_id in (select * from table(p_emp_ids_collection)))\r\n loop\r\n dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );\r\n end loop;\r\n dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');\r\n end;\r\n\r\nbegin\r\n select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)\r\n into the_emp_ids_collection from employees;\r\n print_collection(the_emp_ids_collection);\r\n\r\n the_emp_ids_collection := t_employee_ids_collection (100,102,108);\r\n print_collection(the_emp_ids_collection);\r\n\r\nend;\r\n\/<\/pre>\nThe 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.<\/p>\n
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 – <\/a> ).<\/p>\nsome alternative to this could be the variant where MEMBER OF is used like in this example.<\/p>\n
declare\r\n the_emp_ids_collection t_employee_ids_collection;\r\n\r\n procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is\r\n begin\r\n dbms_output.put_line('Starting to print');\r\n for r in (\r\n select *\r\n from employees\r\n where employee_id member of p_emp_ids_collection )\r\n loop\r\n dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );\r\n end loop;\r\n dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');\r\n end;\r\n\r\nbegin\r\n select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)\r\n into the_emp_ids_collection from employees;\r\n print_collection(the_emp_ids_collection);\r\n\r\n the_emp_ids_collection := t_employee_ids_collection (100,102,108);\r\n print_collection(the_emp_ids_collection);\r\n\r\nend;\r\n\/<\/pre>\nI 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<\/code> 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<\/code> or SET<\/code> ).<\/p>\nHowever 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.<\/p>\n
declare\r\n the_emp_ids_collection t_employee_ids_collection;\r\n\r\n procedure print_collection(p_emp_ids_collection t_employee_ids_collection) is\r\n begin\r\n dbms_output.put_line('Starting to print');\r\n if p_emp_ids_collection.length = 0 then\r\n null; -- no records, so no need to do the select at all\r\n elsif p_emp_ids_collection.length < 10 then\r\n for r in (\r\n select *\r\n from employees\r\nwhere employee_id in (select \/*+ cardinality(z,5) *\/ * from table(p_emp_ids_collection) z )\r\n loop\r\n dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );\r\n end loop;\r\n elsif p_emp_ids_collection.length < 100\r\n for r in (\r\n select *\r\n from employees\r\nwhere employee_id in (select \/*+ cardinality(z,50) *\/ * from table(p_emp_ids_collection) z )\r\n loop\r\n dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );\r\n end loop;\r\n elsif p_emp_ids_collection.length < 1000\r\n for r in (\r\n select *\r\n from employees\r\nwhere employee_id in (select \/*+ cardinality(z,500) *\/ * from table(p_emp_ids_collection) z )\r\n loop\r\n dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );\r\n end loop;\r\n else\r\n for r in (\r\n select *\r\n from employees\r\nwhere employee_id in (select \/*+ cardinality(z,5000) *\/ * from table(p_emp_ids_collection) z )\r\n loop\r\n dbms_output.put_line('Emp ID='||r.employee_id||' , name='||r.first_name||' '||r.last_name||', email='||r.email );\r\n end loop;\r\n end if;\r\n dbms_output.put_line('End of printing '||p_emp_ids_collection.count||' records.');\r\n end;\r\n\r\nbegin\r\n select cast (collect (cast (employee_id as number)) as t_employee_ids_collection)\r\n into the_emp_ids_collection from employees;\r\n print_collection(the_emp_ids_collection);\r\n\r\n the_emp_ids_collection := t_employee_ids_collection (100,102,108);\r\n print_collection(the_emp_ids_collection);\r\n\r\nend;\r\n\/<\/pre>\nPlease note that cardinality<\/code> 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) *\/<\/code> ), decision to use an index, or the join method – e.g. USE_NL for nested loops or HASH_SJ HASH SEMI join.<\/p>\n","protected":false},"excerpt":{"rendered":"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 … Continue reading