Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: different SQL approaches
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.
-- Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk> <http://www.andyhsoftware.co.uk/space> Space: disk usage analysis toolReceived on Fri Oct 15 2004 - 19:51:21 CDT