At 09:07 PM 26/09/2003 -0300, you wrote:

>At 22:14 26/09/2003 +0000, you wrote:
> >sorry you mixed up again some things:
> >
> >bitmap indexes (yes oracle has them, but only the enterprise
> >edition!!) are good for HIGH selectivity (e.g. table with 1 000 000
> >records and you often select a few 100 000 records! low selectivity
> >means that you search/select only a few records maybe 100 or 200. and
> >for this LOW selectivity you normally use b-trees or b*-trees (if i
> >am right firebird knows only b-trees).

Erm, Firebird index trees use a fascinating bitmap algorithms. Ann has
described them here more than once; and I'm sure you'll find at least one
Jim Starkey posting on the subject in the fb-architect archives. For a
summary, read Ann's brief description in
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_assumptions

But if you're stating that you can't *choose* the indexing algorithm,
that's true. There is only one.

>Hi Alex,
>
>
>I don't think so....
>
>Index with High Selectivity means few records... low selectivity means a
>lot of records...
>Of course Helen have a better didactic and a better english to explain
>that... see her post...

It seems more like a confusion in terminology.
I *have* seen the term "selectivity" used to refer to a query specification
- to mean that a query designed to return 1 or a few rows is "more
selective" than, say "select * from" with no WHERE clause...

But that's not the selectivity we are talking about here. We talk of an
index having some degree of selectivity. An index with "high" selectivity
points to few rows (highest = a single row) whereas one with "low"
selectivity points to a large percentage of all rows. The lower the
selectivity, the more likely it will be that the machinery for scanning the
index will be slower than scanning the rows themselves. Also, high
distribution of a single value in *any* indexed column will wreck (reduce,
destroy) the selectivity of even an otherwise reasonable index (hence the
war-story about the transaction table where every row had the same COUNTRY
code..)

There is one trick that sometimes helps to speed up searches on state
switch columns. Pick the column in the table with the highest selectivity,
e.g. a single-column primary key column or a non-nullable timestamp column,
and form a composite index involving it and the switch column. Try it
first with the highly selective column first, e.g.
create index true_false on atable(p_key, switchcol) ;

and then try it around the other way. Either should speed things up, but
one sometimes beats the other, and I don't know a catch-all rule. Ann
probably knows one (hope u r watching, Ann).

(and don't forget to drop the existing indexes that you have on
state-switch columns. I promise you that they are a rock in the rucksack!)

heLen