Re: different SQL approaches

On Fri, 15 Oct 2004 18:54:50 -0700, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>Andy Hassall wrote:
>
>> On Thu, 14 Oct 2004 18:02:32 -0700, Daniel Morgan <damorgan_at_x.washington.edu>
>> wrote:
>>
>>>And then, of course, there is the issue of bind variables as neither
>>>statement should ever be written for any reason.
>>
>> Problem with saying never is there's usually exceptions. You're saying that
>> there should never be literals in SQL statements, and they should always be
>> bind variables even when they're constants? Not enough information to say
>> whether the OP's query makes sense or not with either of the two values
>> changing, so maybe they're candidates for bind variables, but maybe not.
>>
>> And what about the cases where using bind variables produces worse plans? e.g.
>> bind variable peeking producing one plan for a skewed-value column, where a
>> later execution with a different value could benefit from a different plan.
>>
>> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/optimops.htm#79381
>>
>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7832114438832#9915391826680
>>
>> Certainly bind variables are appropriate for the variable values in an SQL
>> statement the majority of time - but there are reasons for exceptions.
>
>And the 'reasons' are?

 The reasons are stated in the post and the URLs above...

>Please don't keep us in suspense.
>
>I've always had an aversion to filling the cache with duplicate
>statements ... is there some reason why I might want to do so?

 Here they are again:

(1) If the values are constants, then there's no duplicate statements in the first place. Do you always use bind variables, even for constants, and if so, why? Bind variable peeking isn't even in all versions of Oracle; and without that, it can't take full advantage of histograms if it doesn't know what the value is. Why add additional variables and extra steps to bind them if it's a constant across all executions?

(2) The URLs above, particularly the asktom one, explain and demonstrate where execution plans may turn out worse with use of bind variables.

 And, if you actually read my post, I'm saying these are the exceptions to the general rule that bind variables are good, countering your blanket assertion that "neither statement should ever be written for any reason."

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool