Kjell Rilbe wrote:

>
>
> In this particular case, I would assume that it would be possible to
> have FB scan the appropriate index (an index on the selected column) and
> for each distinct value in the index, keep looking up records until 1)
> it finds one that's visible to the current transaction (the value is
> included in the result set) or 2) the scan is complete (the value is
> excluded from the result set).

That's pretty much the suggestion I outlined - and of course Kjell wrote
it first. Let me expound a bit more.

Firebird currently uses indexes in one of two ways - navigational and
two-stage bitmap. Navigational access uses indexes the way you expect
them to be used: read the index, find an entry that matches, retrieve
the record, return to the index, repeat until an entry doesn't match,
then quit. Then engine chooses navigational access when the index
matches the order clause in the query.

The two-stage bitmap access reads the index, finds the first match, then
marks matches in a bitmap of record locations until it finds an entry
that doesn't match, then uses the bitmap to lookup records. For reasons
I've explained a few times before, two-stage bitmap access avoids a lot
of database design headaches.

Neither method includes finding a non-match and continuing to look.
Distinct is not the only case that might be improved by adding another
(or two more) index access techniques that can stop and restart. One
that comes to mind is our handling of compound keys - for example, a key
on year, month and a query like this:

"year between 2002 and 2005 and month = 'January'

On the other hand, Firebird does allow you to put separate indexes on
year and month and use both of them...


Regards


Ann