Performance of subquery vs. item list with IN operator

[Oracle9i Enterprise Edition Release 9.2.0.3.0]

Hi,

I've got a question about the performance of a subquery in a select statement versus a static list of values when using the IN operator. Because of the variablity of the WHERE clause, I'm using Native Dynamic SQL. I'm working on an application where the users are either buyers or suppliers. Each buyer can see their own data and additionally add other buyers to their preferences (which are stored as a WHERE clause) to see the data of other buyers. With each buyer is associated a various number of suppliers from which they can select from. So one type of query I can have is:

   SELECT * from <my_view>
    WHERE buyer_id IN(<selected buyer list>)       AND supplier_number IN(<selected supplier list>);

The buyer list may vary from 1 to as many as 50+ (a manager can be selected and all their buyers are added) but the supplier list could be hundreds or potentially over a thousand (some individual buyers have 100-200 suppliers). The view I'm selecting from joins 7 tables and the buyer/supplier tables are just a userid and associated buyer/supplier id. Right now I'm using a function to generate the buyer list as individual delimited items and a subquery for the supplier list:

   SELECT * from <my_view>
    WHERE buyer_id IN('a','b','c','d',...)

      AND supplier_id IN(SELECT supplier_id 
                           FROM supplier_table
                          WHERE userid = <current userid>);

The buyer clause will always be there, but the supplier clause may not (if user does not select individual suppliers, they see all data for selected buyers).

To use bind variables I would like to do something like:

   SELECT * from <my_view>
    WHERE buyer_id IN(SELECT buyer_id

                        FROM buyer_table
                       WHERE userid = :buyerid)
      AND supplier_id IN(SELECT supplier_id 
                           FROM supplier_table
                          WHERE userid = :supplier_id);

I've tried using a normal subquery as above and collection methods like:

   SELECT * FROM TABLE (SELECT CAST(str2tbl('1, 3, 5, 7, 99') AS

                                 mytabletype) FROM dual))

as a replacement for the normal subquery and also use of WITH.

Bottom line, the individual item list always out performs any version of subquery I've tried, even after it's been executed several times.

Is there a better means of making the subquery method perform better or another method to accomplish the same effect?

Thanks,

Ed Holloman Received on Sun Apr 18 2004 - 22:53:11 CDT