Re: Compare sql and dynamic sql

A copy of this was sent to Mike Burden <mburden_at_uk.att.com> (if that email address didn't require changing) On Fri, 01 Oct 1999 00:11:36 +0100, you wrote:

>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.

query outlines, new in Oracle8i release 8.1, allow you to tune a database in a certain way (get the plans you really really want for a set of queries) -- store those plans in the database (in the outlines table) and then make it so your application will use those plans -- regardless of the current stats on the table, regardless of the version of Oracle (eg: this feature is also known as "query plan stability" -- if you don't want the plans to change for queries due to patches or upgrades -- you can use this as well).

This is close to 'static' sql ala DB2. You will compile the plan into the database -- and have the data use that plan at runtime.

See the 'getting to know Oracle8i' for a more in depth overview of this feature. You can get that at http://technet.oracle.com/ (you need a password but the account is free)

>
>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
>
>

--
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 - 09:22:14 CDT