Re: There is NOT value in using BOUND variables!!! ???

But, bind variables _can_ have a negative performance impact, when the same statement is used with two different sets of values that would lead to different execution plans... if the statements are coded with literal values, the optimizer can use those values (and look at histograms) to determine the best execution path, but when it compiles a statement with bind variables, it has to use a "best guess" execution path.

Think of a table with 100 records for customer ABC, and 15,000 records for customer DEF, out of a total of 200,000 records. A query for all records for ABC would likely use an index on the customer id as the fastest path, but the query on DEF would likely use a full table scan. But if the query is done using bind variables, only one path will be used for both queries.

Erwin Dondorp <erwindon_at_wxs.nl> wrote in message news:382A0256.A731A6BC_at_wxs.nl...
> > So what does it tell me? I remember that the perception about bound
> > variables forced developers in our shop to change queries NOT to use
> > bound variables (a few months ago) and todays test just confirms it.
> >
> > Yours thoughts?
>
> Bound variables should never have a lower performance as far as I know.
>
> 2 arguments in favor of bound variables:
>
> 1) In an environment where many SQL statements touch only a few (or one)
> record
> [as in a data entry application] there is the benefit that Oracle
> does not recompile
> all statements because in fact everyone is using the same statement.
> This might be as high a 50% CPU time saved.
> For queries that hit a lot of records the saving is negligable.
>
> 2) Bound variables never suffer from errors with illegal characters.
> When the data contains a single quote, do all programmers expand this
> to 2 single quotes
> in all their code when an SQL statement is created?
>
> --
> Erwin Dondorp
> <http://www.dondorp.com/>
Received on Wed Nov 10 1999 - 18:57:40 CST