Re: Dynamic SQL vs Embedded

A copy of this was sent to pauldb <luapdb_at_yahoo.com> (if that email address didn't require changing) On Fri, 25 Jun 1999 02:32:39 -0800, you wrote:

>Is there a significant performance gain to be had by using
>Dynamic SQL instead of normal embedded SQL?
>

nope. they are the same (in a 3gl). there is no truely 'static' sql in Oracle. static sql is a programming technique only -- its easier to code. It is all dynamic under the covers.

as long as you use bind variables and make sure the queries are textually the same (eg: select * from emp is not the same as Select * from emp -- the select <> Select) they will perform the same...

You'll write lots less code with 'static' sql.

the one case where 'static' sql is perhaps faster then dynamic sql is in plsql. If plsql can process the query up front at compile time, it has less work to do at run time to actually execute it. If you dynamically execute it at run time, it has a little more work to do.

>If the shared_pool_size is set to be large enough- the
>query should not have to reparse and therefore should be
>quite fast?
>
>Any advice here?
>
>
>
>**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

--
See http://govt.us.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 Received on Fri Jun 25 1999 - 06:46:37 CDT