Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: different SQL approaches
Andy Hassall wrote:
> 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?
>>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?
Not for constants ... but the SQL in the original post gave no reason to believe they were constants and how often does one actually work with constants in DML? Once every blue moon?
Lets say, for example, you have a resport that returns the names of all active accounts. Is it reasonable to write:
WHERE account_status = 'A'?
I would argue that it is not. Because even if your specific query does not look at inactive accounts there is undoubtedly some other query that does or will be in the not too distant future.
Thus the clause:
WHERE account_status = x;
Makes more sense unless you feel somehow imposed upon by being forced to declare a variable.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)Received on Sat Oct 16 2004 - 12:53:25 CDT