binding in-lists in 10g

binding in-lists in 10g

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

Incidentally, how to bind IN-lists (i.e. how to pass a list of values in one parameter and have Oracle treat the single parameter as a list) is a common problem cited on many web forums. Unfortunately, what many developers do is construct a dynamic SQL statement and concatenate a delimited string to the end to represent the list of values. In addition to shifting from static to dynamic SQL, this approach also makes it impossible to use bind variables and, for form-based applications, also leaves the application wide open to SQL injection. An alternative to this is to turn the delimited string into a collection first and reference the collection in the SQL.

In this article, we will use examples that pass collections of data as IN-lists, rather than lists of values composed as delimited strings.

setup

First we will create some simple collection types to use in our examples, 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.

in-list binding in previous versions

The correct way to bind an in-list, regardless of Oracle version from 8.0 onwards, is to have the list-values passed as a collection. We can then cast the collection to a pseudo-table (using the TABLE operator). We will see an example of this below. First we will create a simple procedure to find all objects in a list of categories. The list of categories will be passed to our procedure as a collection.

SQL> CREATE PROCEDURE which_objects( which_types_in IN varchar2_ntt ) AS
  2  BEGIN
  3     FOR r IN (SELECT object_name
  4               FROM   user_objects
  5               WHERE  object_type IN (SELECT column_value
  6                                      FROM   TABLE(which_types_in)))
  7     LOOP
  8        DBMS_OUTPUT.PUT_LINE( r.object_name );
  9     END LOOP;
 10  END;
 11  /

Procedure created.

Our client program generates the "in-list" collection and calls the procedure, as follows.

SQL> DECLARE
  2     nt_types varchar2_ntt := varchar2_ntt('TABLE','TYPE','PACKAGE');
  3  BEGIN
  4     which_objects( which_types_in => nt_types );
  5  END;
  6  /
TGT
PLSQL_PROFILER_DATA
PLSQL_PROFILER_UNITS
SRC
PROF_REPORT_UTILITIES
PLSQL_PROFILER_RUNS
PKG
XPLAN_OT
XPLAN_NTT
XPLAN
NUMBER_NTT
VARCHAR2_NTT
ETL_PKG

PL/SQL procedure successfully completed.

in-list binding in 10g

We will now see the 10g alternative using the new MEMBER OF condition for collections. The syntax is very simple and doesn't require us to cast the collection to a pseudo-table.

SQL> CREATE PROCEDURE which_objects_10g( which_types_in IN varchar2_ntt ) AS
  2  BEGIN
  3     FOR r IN (SELECT object_name
  4               FROM   user_objects
  5               WHERE  object_type MEMBER OF which_types_in)
  6     LOOP
  7        DBMS_OUTPUT.PUT_LINE( r.object_name );
  8     END LOOP;
  9  END;
 10  /

Procedure created.

We call this version of the procedure in the same way, as follows.

SQL> DECLARE
  2     nt_types varchar2_ntt := varchar2_ntt('TABLE','TYPE','PACKAGE');
  3  BEGIN
  4     which_objects_10g( which_types_in => nt_types );
  5  END;
  6  /
TGT
PLSQL_PROFILER_DATA
PLSQL_PROFILER_UNITS
SRC
PROF_REPORT_UTILITIES
PLSQL_PROFILER_RUNS
PKG
XPLAN_OT
XPLAN_NTT
XPLAN
NUMBER_NTT
VARCHAR2_NTT
ETL_PKG

PL/SQL procedure successfully completed.

a simple performance test

We will compare the new and old methods for IN-list binding under a couple of simple scenarios, as follows:

• constraining a SQL statement as an IN-list; and
• testing a literal value against an IN-list in PL/SQL.

test one: filtering a table based on an in-list

For our first performance test, we will compare methods for binding an IN-list to a SQL statement, using a version of Tom Kyte's RUNSTATS utility. We will create a table with 1 million rows and query it with an IN-list collection of approximately 100 keys. First, we create the sample table as follows.

SQL> CREATE TABLE million_rows
  2  AS
  3     SELECT ROWNUM AS id
  4     ,      RPAD('x',100) AS data
  5     FROM   dual
  6     CONNECT BY ROWNUM < 1000000;

Table created.

SQL> ALTER TABLE million_rows ADD
  2     CONSTRAINT million_rows_pk
  3     PRIMARY KEY (id);

Table altered.

SQL> BEGIN
  2     DBMS_STATS.GATHER_TABLE_STATS(
  3        USER, 'MILLION_ROWS', estimate_percent=>100
  4        );
  5  END;
  6  /

PL/SQL procedure successfully completed.

We will now create a small procedure to compare the new and old methods. This procedure will load a collection with a parameterised sample of IDs to test the TABLE operator and MEMBER OF condition at different volumes.

SQL> CREATE PROCEDURE compare_in_list( sample_in IN NUMBER ) IS
  2
  3     nt_ids number_ntt := number_ntt();
  4
  5     TYPE million_rows_aat IS TABLE OF million_rows%ROWTYPE
  6        INDEX BY PLS_INTEGER;
  7     aa_rows million_rows_aat;
  8
  9  BEGIN
 10
 11     /* Fetch a sample of data to use as the in-list... */
 12     EXECUTE IMMEDIATE
 13        'SELECT id FROM million_rows SAMPLE (' || sample_in || ')'
 14     BULK COLLECT INTO nt_ids;
 15
 16     DBMS_OUTPUT.PUT_LINE( 'IN-list size : ' || nt_ids.COUNT );
 17
 18     runstats_pkg.rs_start;
 19
 20     /* Fetch the matching records using the TABLE method... */
 21     SELECT * BULK COLLECT INTO aa_rows
 22     FROM   million_rows
 23     WHERE  id IN (SELECT column_value FROM TABLE(CAST(nt_ids AS number_ntt)));
 24
 25     runstats_pkg.rs_middle;
 26
 27     /* ...and the 10g MEMBER method... */
 28     SELECT * BULK COLLECT INTO aa_rows
 29     FROM   million_rows
 30     WHERE  id MEMBER OF nt_ids;
 31
 32     runstats_pkg.rs_stop(1000);
 33
 34  END;
 35  /

Procedure created.

We will test with a small sample of the MILLION_ROWS table (representing an IN-list of approximately 100 rows), as follows.

SQL> exec compare_in_list( 0.01 );
IN-list size : 107
Run1 ran in 110 hsecs
Run2 ran in 1606 hsecs
Run1 ran in 6.85% of the time




Name                                  Run1        Run2        Diff
STAT..recursive cpu usage                3       1,230       1,227
LATCH.multiblock read objects            0       2,156       2,156
LATCH.simulator hash latch             111       3,907       3,796
STAT..physical reads cache pre           0      14,112      14,112
STAT..free buffer inspected            128      15,169      15,041
STAT..consistent gets                  323      15,396      15,073
STAT..consistent gets from cac         323      15,396      15,073
STAT..session logical reads            323      15,396      15,073
STAT..free buffer requested            101      15,195      15,094
STAT..physical reads                   101      15,195      15,094
STAT..physical reads cache             101      15,195      15,094
LATCH.cache buffers lru chain          105      15,264      15,159
STAT..no work - consistent rea           1      15,385      15,384
STAT..table scan blocks gotten           0      15,385      15,385
LATCH.object queue header oper         210      30,458      30,248
LATCH.cache buffers chains             623      46,141      45,518
STAT..table scan rows gotten             0   1,000,000   1,000,000


Run1 latches total versus run2 -- difference and pct
        Run1        Run2        Diff        Pct
       1,435      99,876      98,441      1.44%

PL/SQL procedure successfully completed.

We can see a dramatic difference in performance between the two methods, with the new MEMBER OF condition taking 16 times longer than when we queried the collection as a pseudo-table. The key to this lack of performance is evident in the statistics, particularly the "table scan rows gotten". This indicates that Oracle is using a full table scan with the MEMBER OF method. We can verify this below with EXPLAIN PLAN (although we will use a literal collection rather than a bind variable for convenience).

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MEMBER' FOR
  2     SELECT *
  3     FROM   million_rows
  4     WHERE  id MEMBER OF number_ntt(1,2,3);

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'MEMBER'));

PLAN_TABLE_OUTPUT
--------------------------------------------------
Plan hash value: 1095651555

-------------------------------------------------- ...
| Id  | Operation         | Name         | Rows  | ...
-------------------------------------------------- ...
|   0 | SELECT STATEMENT  |              | 50000 | ...
|*  1 |  TABLE ACCESS FULL| MILLION_ROWS | 50000 | ...
-------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"MEMBER OF"NUMBER_NTT"(1,2,3))

13 rows selected.

As indicated by the RUNSTATS results, Oracle has chosen a full table scan for the query, with an estimated cardinality of 50,000 (a 20% selectivity) for the filter. We can compare this with the CBO's treatment of a normal IN-list, as follows.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'IN' FOR
  2     SELECT *
  3     FROM   million_rows
  4     WHERE  id IN (1,2,3);

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'IN'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Plan hash value: 127701680

---------------------------------------------------------------- ...
| Id  | Operation                    | Name            | Rows  | ...
---------------------------------------------------------------- ...
|   0 | SELECT STATEMENT             |                 |     3 | ...
|   1 |  INLIST ITERATOR             |                 |       | ...
|   2 |   TABLE ACCESS BY INDEX ROWID| MILLION_ROWS    |     3 | ...
|*  3 |    INDEX RANGE SCAN          | MILLION_ROWS_PK |     3 | ...
---------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"=1 OR "ID"=2 OR "ID"=3)

15 rows selected.

Oracle is able to recognise the list of literal values in this case and make the correct cardinality estimates, leading to an index range scan. Of course, this article is about parameterised IN-lists, so if we compare the new MEMBER method with the original TABLE operator method, we can see that Oracle handles the collection in a very different way.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TABLE' FOR
  2     SELECT *
  3     FROM   million_rows
  4     WHERE  id IN (SELECT column_value FROM TABLE(number_ntt(1,2,3)));

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'TABLE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3090652353

--------------------------------------------------------------------------- ...
| Id  | Operation                               | Name            | Rows  | ...
--------------------------------------------------------------------------- ...
|   0 | SELECT STATEMENT                        |                 |   255 | ...
|   1 |  NESTED LOOPS                           |                 |   255 | ...
|   2 |   SORT UNIQUE                           |                 |       | ...
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|                 |       | ...
|   4 |   TABLE ACCESS BY INDEX ROWID           | MILLION_ROWS    |     1 | ...
|*  5 |    INDEX UNIQUE SCAN                    | MILLION_ROWS_PK |     1 | ...
--------------------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ID"=VALUE(KOKBF$))

17 rows selected.

With the TABLE operator method, Oracle chooses a nested loops join using the unique index on MILLION_ROWS, hence the major performance improvement. This is due to the semantic difference between the two methods. With the TABLE operator, Oracle unnests the values from the collection and then uses these to probe the MILLION_ROWS_PK index. With the MEMBER condition, however, we only have a filter condition, not a join. We are asking for records from MILLION_ROWS where the ID is in the collection, rather than the other way round. This means that Oracle must probe the collection with values from the MILLION_ROWS.ID column. This has a dramatic effect on performance and there seems to be no way to reverse this behaviour.

There are a number of ways we can try to correct the cardinality or force Oracle to use an index, for example:

• OPT_ESTIMATE hint, e.g. /*+ OPT_ESTIMATE(table, million_rows, scale_rows=0.0001) */ ;
• CARDINALITY hint, e.g. /*+ CARDINALITY(million_rows, 3) */ ;
• FIRST_ROWS hint;
• INDEX or INDEX_RS hints, e.g. INDEX(_RS)(million_rows, million_rows_pk) ;
• OPTIMIZER_INDEX_COST_ADJ parameter, e.g. ALTER SESSION SET optimizer_index_cost_adj = 5 .

With any of these methods, Oracle will not rewrite our query in any way. There simply is no way around the fact that we are filtering MILLION_ROWS against the collection. The best we can achieve is an index full scan, forced by an appropriate hint, as follows.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'INDEX' FOR
  2     SELECT /*+ INDEX(million_rows) */ *
  3     FROM   million_rows
  4     WHERE  id MEMBER OF number_ntt(1,2,3);

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'INDEX'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
Plan hash value: 857746450

--------------------------------------------------------------- ...
| Id  | Operation                   | Name            | Rows  | ...
--------------------------------------------------------------- ...
|   0 | SELECT STATEMENT            |                 | 50000 | ...
|   1 |  TABLE ACCESS BY INDEX ROWID| MILLION_ROWS    | 50000 | ...
|*  2 |   INDEX FULL SCAN           | MILLION_ROWS_PK | 50000 | ...
--------------------------------------------------------------- ...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"MEMBER OF"NUMBER_NTT"(1,2,3))

14 rows selected.

It appears that an index range scan is simply not available as an access path when using this new condition, because Oracle will not reverse the direction of the filter.

The above performance tests were conducted using many probes of a small collection. It follows, therefore, that the larger the collection, the greater the disparity in performance between TABLE and MEMBER, with the latter becoming almost unusable. Some example timings are as follows.

Sample Size	IN-List Size	IN TABLE() (s)	MEMBER OF (s)
0.001	5	0.1	5.1
0.01	107	1.1	16.1
0.1	984	13.7	107.2
1.0	10,000	21.4	>1,200

Note that this behaviour was last tested on an 11g Release 1 database (11.1.0.6) and it is unchanged.

test two: probing an in-list with a single value

The previous tests measured the performance of a parameterised IN-list in SQL, which is the most common application for this technique. However, we can also use the MEMBER condition in PL/SQL tests. For our second performance test, we will repeatedly probe a parameterised list with a single value, again comparing the original TABLE operator with the new MEMBER condition. For completeness, we will also measure the performance of a loop through the parameterised list to search for the single value. We will use an anonymous block for these tests, as follows.

SQL> DECLARE
  2     in_list  number_ntt := number_ntt();
  3     v_cnt    PLS_INTEGER;
  4     v_hits   PLS_INTEGER := 0;
  5  BEGIN
  6
  7     /* Add 100 elements to the collection "IN-list"... */
  8     in_list.EXTEND(100);
  9     FOR i IN 1 .. 100 LOOP
 10        in_list(i) := i;
 11     END LOOP;
 12
 13     /* TABLE operator... */
 14     timer.snap;
 15     FOR i IN 1 .. 10000 LOOP
 16        SELECT COUNT(*) INTO v_cnt FROM TABLE(in_list) WHERE column_value = i;
 17        IF v_cnt > 0 THEN
 18           v_hits := v_hits + 1;
 19        END IF;
 20     END LOOP;
 21     timer.show('TABLE  (hits=' || v_hits || ')');
 22
 23     /* MEMBER condition... */
 24     v_hits := 0;
 25     timer.snap;
 26     FOR i IN 1 .. 10000 LOOP
 27        IF i MEMBER OF in_list THEN
 28           v_hits := v_hits + 1;
 29        END IF;
 30     END LOOP;
 31     timer.show('MEMBER (hits=' || v_hits || ')');
 32
 33     /* Collection loop... */
 34     v_hits := 0;
 35     timer.snap;
 36     FOR i IN 1 .. 10000 LOOP
 37        FOR ii IN 1 .. in_list.COUNT LOOP
 38           IF i = in_list(ii) THEN
 39              v_hits := v_hits + 1;
 40              EXIT;
 41           END IF;
 42        END LOOP;
 43     END LOOP;
 44     timer.show('LOOP   (hits=' || v_hits || ')');
 45
 46  END;
 47  /
[TABLE  (hits=100)] 1.22 seconds
[MEMBER (hits=100)] 0.04 seconds
[LOOP   (hits=100)] 0.21 seconds

PL/SQL procedure successfully completed.

As there is no table access involved, we have used a simple TIMER package to measure relative performance. We can see that for this type of IN-list probing, the new MEMBER condition is more efficient and effective than any other method, so it might be worth using this technique under certain circumstances.

further reading

For a good discussion of techniques for binding IN-lists prior to 10g, particularly when they are supplied as delimited strings instead of collections, see this article by William Robertson. For more information on new collection conditions and operators in 10g, see this oracle-developer.net article . The TIMER and RUNSTATS packages used in the performance tests for this article can be found on the Utilities page of this site.

source code

The source code for the examples in this article can be downloaded from here .

Adrian Billington, June 2004 (updated September 2008)

 

 

 

原文：http://www.oracle-developer.net/display.php?id=301