Hello,

Don't know if this can help, i try also to resume the problem here
http://svanderclock.blog.com/2012/01/03/4/

Stéphane

On 1/2/2012 9:26 PM, Ann Harrison wrote:
>
> Sometime last week Stephane Vander Clock mentioned that for his
> application,
> compound indexes were much more efficient than combining indexes on the
> individual columns. His database has several columns with really poor
> selectivity -
> how many houses have fewer than one or more than 10 bedrooms? - so the
> cost
> of building the bitmap of matching rows on each of those columns is
> significant.
> On the other hand, compound indexes don't work if the first columns
> are selected
> on a range, rather than an equality match.
>
> 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.
>
> 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.
> That's a unique
> key. If it's found, keep that record number in the primary bitmap, if
> not, drop it.
>
> Obviously if there's more than one secondary index that could be used,
> use it
> the same way. So you start with all the records numbers in New York City,
> drop out all the ones that cost less than $500,000 or more than
> $1,500,000,
> drop out all the ones that have fewer than 3 or more than 6 bedrooms, etc.
>
> The same mechanism could be used with navigational access on the first
> index
> for queries that use the FIRST... mechanism.
>
> Cheers,
>
> Ann
>
>


[Non-text portions of this message have been removed]