binding in-lists

출처 : http://www.oracle-developer.net/display.php?id=301

This article demonstrates a new method for binding IN-lists in 10g,using the new collection condition MEMBER OF. Also included is a commonpre-10g method and a performance comparison (with dramatic resultswhich leads me to conclude that MEMBER OF is much simpler to use butshould only be used with very small sets or lists).

Incidentally, how to bind IN-lists (i.e. how to pass a list ofvalues in one parameter and have Oracle treat the single parameter as alist) is a common problem requested on many web forums. Unfortunately,what many developers do is construct a dynamic SQL statement andconcatenate the list string to the end. In addition to shifting fromstatic to dynamic SQL, this approach also makes it impossible to usebind variables and, for form-based applications, also leaves theapplication wide open to SQL injection.

setup

First we create some simple collection types as follows.

SQL> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);

  2  /


Type created.

SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;

  2  /


Type created.

Next we create a simple function to parse a delimited string into a collection.

SQL> CREATE FUNCTION to_table (

  2                  string_in    IN VARCHAR2,

  3                  delimiter_in IN VARCHAR2 DEFAULT ','

  4                  ) RETURN varchar2_ntt IS

  5        v_wkg_str VARCHAR2(32767) := string_in || delimiter_in;

  6        v_pos     PLS_INTEGER;

  7        nt_return varchar2_ntt := varchar2_ntt();

  8     BEGIN

  9        LOOP

 10           v_pos := INSTR(v_wkg_str,delimiter_in);

 11           EXIT WHEN NVL(v_pos,0) = 0;

 12           nt_return.EXTEND;

 13           nt_return(nt_return.LAST) := TRIM(SUBSTR(v_wkg_str,1,v_pos-1));

 14           v_wkg_str := SUBSTR(v_wkg_str,v_pos+1);

 15        END LOOP;

 16        RETURN nt_return;

 17     END to_table;

 18  /


Function created.

in-list binding in previous versions

There are numerous examples on the web of a solution to this inreleases prior to 10g. They all essentially include a transformation ofthe list string to a collection and then have SQL interrogate it as anIN-list, statically. The conversion of string to collection can betreated as a bind operation.

For the first simple example, we'll recount the technique used inprevious versions. This technique is still very common. First, we'llsetup a sqlplus bind variable and give it a delimited string value.

SQL> VAR string_list VARCHAR2(128);

SQL> exec :string_list := 'PACKAGE,TYPE,INDEX';


PL/SQL procedure successfully completed.

We can now bind this string using the TABLE operator for 9i andTABLE(CAST AS varchar2_ntt)) for 8i. Note that the TIMER packagereferenced below can found on the Utilities page of this site.

SQL> BEGIN

  2     timer.snap();

  3     FOR r IN (SELECT object_name

  4               FROM user_objects

  5               WHERE object_type IN (SELECT column_value

  6                                     FROM   TABLE(TO_TABLE(:string_list))))

  7     LOOP

  8        DBMS_OUTPUT.PUT_LINE( r.object_name );

  9     END LOOP;

 10     timer.show('IN SUBQUERY');

 11  END;

 12  /

COMPSTAT

TIMER

STRING_AGG_TYPE

OUTER_T

INNER_T

DATE_NTT

VARCHAR2_NTT

NUMBER_NTT

[IN SUBQUERY] 0.03 seconds


PL/SQL procedure successfully completed.

in-list binding in 10g

Now we can see the 10g alternative using the new MEMBER OF conditionfor collections. The syntax is very simple. Note, however, that if thestring list represents numbers or dates, then an appropriate version ofthe TO_TABLE function will be required to cast the in-list elements totheir correct datatypes in the collection. If this is a consideration,it might help to have an overloaded function in a package which allowsa delimited string to be parsed into a VARCHAR2, DATE or NUMBERcollection, depending on the requirement. For this simple example,however, we'll continue with a VARCHAR2 collection as follows.

SQL> BEGIN

  2     timer.snap();

  3     FOR r IN (SELECT object_name

  4               FROM user_objects

  5               WHERE object_type MEMBER OF TO_TABLE(:string_list))

  6     LOOP

  7        DBMS_OUTPUT.PUT_LINE( r.object_name );

  8     END LOOP;

  9     timer.show('MEMBER OF');

 10  END;

 11  /

COMPSTAT

TIMER

STRING_AGG_TYPE

OUTER_T

INNER_T

DATE_NTT

VARCHAR2_NTT

NUMBER_NTT

[MEMBER OF] 0.02 seconds


PL/SQL procedure successfully completed.

a simple performance test

Finally, we can test the new and old methods for IN-list binding ina straight head-to-head. For this simple comparison, we'll just look atthe wall-clock. We'll load up a collection with most of the OBJECT_IDsfrom DBA_OBJECTS and then repeatedly test against it. Note that thisexample is not an IN-list bind from a string as such, but is rather astraight performance comparison of SQL and collection operations.

SQL> DECLARE

  2  

  3     nt_oids number_ntt;

  4     v_count PLS_INTEGER;

  5  

  6  BEGIN

  7  

  8     /* Load up the collection of names... */

  9     SELECT object_id BULK COLLECT INTO nt_oids

 10     FROM (

 11           SELECT object_id

 12           FROM   dba_objects

 13           ORDER  BY

 14                  DBMS_RANDOM.RANDOM

 15          )

 16     WHERE ROWNUM <= 20000;

 17  

 18     /* Get a count of matching oids using the subquery method... */

 19     timer.snap();

 20     SELECT COUNT(*)

 21     INTO   v_count

 22     FROM   dba_objects

 23     WHERE  object_id IN (SELECT column_value

 24                          FROM TABLE(nt_oids));

 25     timer.show('SUBQUERY (' || v_count || ')');

 26  

 27     /* And the 10g method... */

 28     timer.snap();

 29     SELECT COUNT(*)

 30     INTO   v_count

 31     FROM   dba_objects

 32     WHERE  object_id MEMBER OF nt_oids;

 33     timer.show('MEMBER OF (' || v_count || ')');

 34  

 35  END;

 36  /

[SUBQUERY (20000)]> 0.19 seconds

[MEMBER OF (20000)]> 65.26 seconds


PL/SQL procedure successfully completed.

We can see that the MEMBER condition is considerably slower for larger collections.