Re: any difference in term of performance

Daud wrote:

> Hi
>
> I came across this code inside a stored procedure written by one of our developers.
>
> ----start----
> sql_stmt := 'select t.column_one, t.column_two
> from my_table t
> where t.column_pk = :1';
>
> execute immediate sql_stmt
> into tmpC1, tmpC2
> using varPK;
>
> ----end-----
>
> I would normally just write it as:
>
> ----start----
> select t.column_one, t.column_two
> into tmpC1, tmpC2
> from my_table t
> where t.column_pk = varPK;
>
> ----end-----
>
> Is there any difference in term of performance for the 2 methods above?
> If yes, which is faster and why?
>
> regards
> Daud
>
> ---------------------------
> Oracle 8.1.7.4 on HPUX 11.0
> ---------------------------

Only use native dynamic SQL when you can not write the same statement as DML. Tom Kyte has published, numerous times, on why this is so (http://asktom.oracle.com). It is certainly more expensive in terms of latches consumed.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)