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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to cope with nasty side effects of bind variable peeking

Re: How to cope with nasty side effects of bind variable peeking

From: jan van mourik <jan.vanmourik_at_gmail.com>
Date: Fri, 25 Aug 2006 08:23:48 -0500
Message-ID: <396f1c9e0608250623x7a41240al39b87600426776a3@mail.gmail.com>


On 8/24/06, jaromir nemec <jaromir_at_db-nemec.com> wrote:
>
> > is executed once with v1:= 200000 and v2:=200000, so the index on
> > order_number is used in the explain plan as it should be and performance
> > is great for this execution. But, then the same exact query is executed
> > with v1:=0 and v2:=999999 and this query gets stuck with the same
> > execution plan.
>
> If bind variables are unavoidable (performance reasons), a pragmatic
> solution is in my opinion that the application check the range an generates
> different statements for a small and a big range.
>

That's exactly what I had in mind. I'd think your application might want to react differently anyway for cases like the one in your example. Or does the application really want to react the same to a result set of 1 rows vs one of up to 100,000 rows?

Regards,

Jan van Mourik

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 25 2006 - 08:23:48 CDT

Original text of this message

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