Ian,
Having the optimizer change all actual values in a dynamic SQL with
parameter markers at optimization time would not be a very good idea,
particularly in the case when you have a non-uniform distribution of data.
For instance, if
select col2, col3 from table1 where col1 = 'A'
returned 50% of the data in my table, I would want the optimizer to choose
a table scan as the access path for this particular statement. On the
other hand, if
select col2, col3 from table1 where col1 = 'B'
returned only 1% of the data in my table, I would want the optimizer to use
a matching index scan as the access path. By asking the optimizer to
ignore actual supplied values in dynamic SQL, and treat all dynamic SQL as
if it was written with parameter markers, then you'll never be able to
efficiently process against tables with non-uniform distribution of data,
There's a reason why DB2 offers three methods of writing SQL (static,
dynamic with parameter markers, and dynamic without parameter markers), as
each offers it's own advantages and disadvantages. It's up to the
developers to know and understand the differences between each one, and
choose the method that's most appropriate for their application and their
data.
Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT 06083
"Ian D. Bjorhovde"
<[EMAIL PROTECTED]> To: [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: DB2EUG: Overhead for
Dynamic SQL in UDB/AIX?
a.best.com
03/22/01 12:50 PM
Please respond to
db2eug
----- Original Message -----
From: "Pierre Saint-Jacques" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, March 21, 2001 4:26 PM
Subject: Re: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?
> Not only will the package cache be hit to insert the second statement
> but the statement will also have to recompiled because the two strings
> making the statements do not scan equal.
> Dynamic statements are stored in the cache in compiled versions and raw
> sql versions. At next request, strings are compared. They will not be
> equal so dB2 will recompile or reprpep the second one and insert it in
> the cache taking twice the amount of space.
Is it just me or does this seem like something that could (should) be
enhanced in the optimizer?
With everything that the optimizer can do, it seems like a pretty trivial
task to replace actual values in a dynamic SQL statement with parameter
markers prior to compiling the access plan.
i.e.
insert into x values (1,2);
could be rewritten as
insert into x values (?, ?);
before it's prepared / compiled. This could really help Dynamic SQL
applications such as the one that Bill wrote in about.
ian d. bjorhovde cephalad corporation
[EMAIL PROTECTED] intelligent solutions for
http://cephalad.com systems and data management
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see
http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
