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

  • From: "jan van mourik" <jan.vanmourik@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 25 Aug 2006 08:23:48 -0500

On 8/24/06, jaromir nemec <jaromir@xxxxxxxxxxxx> 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

Other related posts: