Re: Compare sql and dynamic sql

I often here the terms 'static' and 'dynamic' used in conversation and it always reminds me of DB2 were it has meaning. When it's applied to Oracle I get confused, because (as you say) all SQL is dynamic.

Query outlines.... tell me more.

Thomas Kyte wrote:

> 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 Fri Oct 01 1999 - 01:11:36 CEST