At 08:55 AM 1/15/01 -0700, Jason Wharton wrote:

>If you are in a stored procedure I suggest that you use an IF ( ) THEN and
>make two totally separate statements and get rid of the OR totally. I think
>somehow the OR prevents use of the index.
>
>I would be curious to know why since I have battled this issue before as
>well.
>

An even better solution would be to fix the optimizer.

This is the gist of the problem. There are two optimizers strategies,
heuristic and cost based. The former tried to use insight to solve
the problem. The later estimates the cost of various alternatives
and picks the cheapest (many pitfalls, however).

Neophytes always start with the heuristic model. If they live
long enough, they see the error of their ways and switch.

The Firebird optimizer began life cost based (I made my mistakes
on a previous product). Someone else, trying to make it do something
else or something better, rather than figuring out the cost
estimation mechanism thought he could do better with the odd
heuristic here and there. The result was neither fish nor fowl,
and not much of an optimizer either.

Plans were introduced as a work around for a busted optimizer.

The solution is to fix the optimizer. Take your query and walk
through the optimizer. You'll find a slightly confusing world
of rivers, streams, and tributaries. You'll probably find a
typo where it screws up your query. A couple days of instructive
investment and you will save oodles of work by future Firebird
users.

Jim Starkey