OK, I see the problem

Richard,

Bear with me, it's been a couple of years since I did any work with pre-11.9.x ASE so my memory may be a bit dim.

This looks like you're hitting bug 122635. What this does is make the optimizer be overly optimistic about the cost of a 16K table scan when compared to a 2K non-clustered index scan. This is not always the best decision. When it does use decide to use the index the cost is relatively low. But, when you used a different sarg value the estimated cost (in pages) increased and triggered the bug.

So, how to get around the bug? Try trace flag 330. To be honest I forget if this can be used at the session level (I believe it can be) but I know you can boot with it. Try it at the session level:

dbcc traceon(330)
select blah, blah,blah

What you'll see in 302 is that the non-clustered index is costed using 16K I/O, but showplan will show that it was actually executed using 2K. This is not an issue in 11.9.x because there we use cluster ratios to cost large I/O usage.

Hope this helps

Eric
p.s.Sorry but I have to throw this in - all you Oracle and MSSQL users reading this. How often do you get to talk to one of their engineers?

>
Received on Tue Nov 16 1999 - 00:00:00 CET