Re: [sqr-users] query runs slow with integer variables inwhere clause?

["George Jansen" <GJANSEN@aflcio.org>]

This sounds like an optimizer issue. With the variable the cursor is
parsed and the access plan selected before the value is known; with a
literal the value is known.

My guess would be that you have a non-selective index on the
column--the same value occurs in too many places in the table for the
index to be more help than hindrance. In the literal case, the optimizer
can see that the index won't help (it knows something about the data
distribution). In the function case, well, what is the "signature" of
convert? I'd expect it returning the wrong sort of datatype to help by
disabling index access.

My experience is almost entirely with Oracle, so I can't really tell
you where to start looking. But check to see how selective the index is;
check to see whether the index needs rebuilding; maybe see whether you
can get rid of it.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users