Subject Re: High selectivity
Author Alex
> As I have a similiar use case, I thought about this.
> So perhaps, fill the state columns with a generator, and when
> state goes from 0 to 1, add a large enough constant, say
> 1,000,000,000.
> Somewhere in your front app you discard the lower 9 decimal digits on
> display, and replace
> WHERE state = 0
> with
> WHERE state BETWEEN 0 AND 999999
> for retrieval
> Regards,
> Peter Jacobi

this does not help, even with this "trick" you will get the same huge
amount of hits when doing the select!
You should use an index only when the number of records you are
searching with select are approximately less than 2% percent of all
records in this table (low selectivitiy like helen said)!

in the case of two stats (0 / 1, male/female, yes/no) it does not
make sense to use an binary tree index because the binary tree will
have only 2 leaves, each containing a sequential list.

the only kind of index which would help is a bitmap index. a
technology of 60th years. dont know if any DBS is really using it.