Rick Debay wrote:

>>Firebird can use more than one index per table for a query
>
>
> In what cases could FB use more than one index on a table? My guess
> would be only for OR logic in the JOIN or WHERE clause. Are there any
> documents I could read on this, besides the FB Book?

You could look in the knowledge base at IBPhoenix. I suspect that the
archives for this list (also there) have a couple of answers of mine
about how Firebird does index lookups.

Suppose you have a query like this:

select m.title from movies m
where m.director = "Stanley Kubrick"
and m.year = 1964


There's an index on director and on year. The optimizer will pick the
director index for the main path, but the engine will use both. One of
the things Arno has done is teach the engine that it can stop when it's
identified a unique index - others aren't going to reduce the results set.

Firebird index lookups are two-phase. First it reads the relevant part
of the index and creates a bitmap of the qualifying record ids. Then it
takes the next index and creates a bit map of it. The two bit maps are
anded together and the surviving record ids are read.


Regards,


Ann