> However, internally, Firebird could do a very fast lookup on the
> combination of a
> record number and a value, even if the selectivity of the index is
> poor based on the key.
Yes, this is possible since ODS 11.
> Could Firebird add another indexed record selection mechanism that used both the
> value and the record number? Then a join with one moderately selective indexed
> term and one other indexed term with poor selectivity would be done like this:
>
> 1) Develop the bitmap of records that match the moderately selective criteria -
> something like properties in New York City that are not in Queens or
> Staten Island.
>
> 2) For each record in that bitmap, dive into the second index using
> the value from
> the restriction term and the record number with from the bitmap.
I.e. you offer to replace one index scan by many index lookup's ?
It could be good (if we have small primary bitmap and a lot of entries
in secondary index satisfied search criteria) or bad (if we have opposite
case). I'd said we can use min and max record numbers from primary bitmap
to limit scanned number of secondary index entries, but it could be not so
effective...
> That's a unique
> key. If it's found, keep that record number in the primary bitmap, if
> not, drop it.
Sorry, we shouldn't clear record numbers in primary bitmap as there could
be another index entry at the secondary index with the same recno but
different key (remember record versions ?).