Re: Compare sql and dynamic sql

A copy of this was sent to Jay <mighty_dragon_at_hotmail.com> (if that email address didn't require changing) On Thu, 30 Sep 1999 10:20:53 -0500, you wrote:

>For a large number of sql statements w/c contain a pattern, for example:
>
> insert into foo_1 values (1, one);
> insert into foo_2 values (2, two);
> insert into foo_3 values (3, three);
> ...
> insert into foo_n values (n, n);
>
>Someone had claimed that the above execution is faster than any means
>like using stored procedures with pl/sql OR dynamic sql OR native
>dynamic sql.
>

all SQL in Oracle is dynamic sql -- even static sql. There is sql that is known when you compile a program -- it is 'static' sql. We can make it easier to write programs if we know the SQL at compile time. It runs no faster nor slower then 'dynamic' sql. Dynamic sql is just sql we don't know at compile time. We don't store compiled SQL plans (well, in 8i, release 8.1 there are query outlines....).

Dynamic sql is as fast as Static sql since they are the *same*.

>Can anyone explain any reason why using stored procedures would be
>slower.
>

Nope -- ask them to give an example.

>Is there any overhead using dynamic sql over straight sql???
>
>In any case, can anyone refer me to a document that will explain in some
>details how dynamic sql execute internally.
>

same as static. the client ships the sql to the srver, the server parses the query. client might add some bind variables and then ask the server to execute the compiled query.

>
>
>Thanks in advance for any help.

--
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 Received on Thu Sep 30 1999 - 12:17:30 CDT