Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: different SQL approaches

Re: different SQL approaches

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 16 Oct 2004 22:06:42 -0700
Message-ID: <1097989548.579029@yasure>


AK wrote:
>>And then, of course, there is the issue of bind variables as neither
>>statement should ever be written for any reason. What should be written
>>is:
>>
>>DECLARE
>>
>>rtype sc.rec_type%TYPE;
>>ctype sc.code_type%TYPE;
>>
>>BEGIN
>> rtype := 'S';
>> ctype := '502';
>>
>>SELECT ...
>>FROM ...
>>WHERE ...
>>AND sc_rec_type = rtype
>>AND sc.code_type = ctype;
>>
>>END;
>>/

> 
> 
> what do you mean by "neither statement should ever be written for any
> reason"?
> (if you wrote 'usually' rather than 'ever', I would completely agree)
> 
> let's recall that there are quite a few exceptions:
> 
> 1. many shops do use CURSOR_SHARING=FORCE, which replaces all the
> literals with bind variables no matter what
> 
> 2. sometimes replacing all the literals with bind variables results in
> very inefficient execution plans. that's why we sometimes need to use
> a hint to hava a query recompiled each time, so that the optimizer
> takes in account the actual literal values every time the query is
> executed

Ok ok ok. You are correct. Though as I recall Tom Kyte has made comments with respect to CURSOR_SHARING=FORCE but I can't put my finger on it right now to confirm what I think are negative comments.

While I will agree that you are correct that I used the absolute when I could have used a softer term ... it is equally true that Oracle, and other companies, often offer us the ability to do stupid things. No doubt Howard Rogers will gladly jump in and provide a long list of examples.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sun Oct 17 2004 - 00:06:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US