Hi all,

Following this thread, I want to make a comment... I remember this being
discussed some time...

MSSQL has and index type that is named "clustered index".

You can only have one index of this type for each table, this kind of index
is good for low selectivity indexes. This index is a special type, that
make the table physical ordered, the leaf pages of the index is the data
page instead of a page of pointers to datapages.

When you select using this index the db start at the first page and read
continuosly next pages to retrieve teh records, since a bunch of records is
"reached" in each page (because it is physically ordered) you can do
selects on low selectivity index in a good way.

I hope this can bring some ideas...

see you

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

P.S. I never used but I read sometime that Oracle has Bitmap indexes to be
used on low selectivity indexes




At 19:19 26/09/2003 +0000, you wrote:

> >
> > 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.

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003


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