Re: bind variables

Does the Oracle optimizer do expression simplication? i.e.

SELECT ename
  FROM emp
 WHERE salary >= :b1 * 1.1;

Will the the Oracle kernel evaluate ':b1 * 1.1' for each row in the emp table? Or does it know the expression will evalulate to a constant value, evaluate it once and then use the computed value over and over again?

Of course if the expression uses a column value, then it must evaluate for each row.

Phil Bolduc

In article <jPcvOLLbxnuZhxjLUTVbj1G4LbLQ_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to lance sturla <lsturla_at_ix.netcom.com>
> (if that email address didn't require changing)
> On Sun, 14 Nov 1999 21:40:00 -0800, you wrote:
>
> >Can someone explain what a bind variable is?
> >a beginner lance
>
> A bind variable is like a replaceable parameter in a SQL statement.
It allows
> us to compile and optimize a sql statement once and execute it for
many people
> over and over and over again with different values. As the time to
> parse/optimize sql queries can take most of the execution time (eg:
for a simple
> single table query that reads a table by a primary key 70-90% of the
execution
> time of the query might be spent in parsing/optimizing. if we
instead use a
> bind variable, we can execute that query many times with different
values and we
> only pay the price of parsing/optimizing once).
>
> So, the query:
>
> select * from emp where ename='KING';
>
> does not use a bind variable. the query:
>
> select * from emp where ename=:x;
>
> does. PLSQL is an 'autobinding' language. If you write a procedure
such as:
>
> ...
> l_ename varchar2(30) default 'KING';
> l_empno number;
> begin
> select empno into l_empno from emp where ename = l_ename;
> exception
> ...
> end;
>
> That sql statement will automagically use bind variables for us -- it
is just
> like submitting "select empno from emp where ename = :x"
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 26 1999 - 16:13:06 CST