Re: How to turn this query to use BOUND variables?

Hi,

 SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID IN (:b1,:b2,:b3,:b4,:b5,:b6)

 parse, bind, execute, fetch.

 Note, there is an upper limit on the number of elements in an in list.  (255, i think)

 If you have varying length lists, always have the same statement  (max number of list elements) and bind the same value multiple times.  That way you don't use sga for diffeent-looking statements.

 one more note - IMO it is not a good practice to use select *  programatically - that way you break your application by adding  columns to your table.

HTH
Karsten

In article <80a5ba$j3f$1_at_nnrp1.deja.com>,   Deja User <dejacom_at_my-deja.com> wrote:
> I was reading a book on Oracle Performance Tuning. In there they
suggest
> to use bind variables as much as possible to avoid having parse done
of
> same query over and over again. There were many other benefits listed
if
> Oracle could use a query that it has already parsed. A lot of queries
> are easy to modify to use bind variables. For example,
> select * from a_table where a_field >= :a_criteria
>
> But what do you do in the case where you have a multiple values for
the
> where clause. For example, how would you modify the following query to
> use the bind variables?
> SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID IN (1,2,3,4,5,6)
>
> etc etc. Any help would be greatly appreciated!
>
> Regards,
> Mike.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 10 1999 - 04:24:41 CST