Helen Borrie wrote:

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

This is _extremely_ confusing! :-)
The reason I called it "high" selectivity, is simply because the value
given by the index stats is... high! This value,
from what I understood, corresponds to the average number of records
given by each value of the index, which explains
why unique indexes always have value 1. Anyway, never mind about
terminology.

I still don't quite understand why in my case, an index wouldn't make
things faster. Of course, if I want to select value 1,
the database will have to look through lots of records, and I understand
that this would be slower. But selecting value 0 should be much faster.
If I have 20 000 records, of which 50 have state 0 and 19 950 have state
1, then an index on the state column ought to make selecting value 0
much faster,
and selecting value 1 much slower. Isn't that right?

>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) ;
>
>

Why would this help? if I do SELECT * FROM TABLE WHERE SWITCHCOL =
VALUE, how could this index be any help?

>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!)
>
>

Yes, but...

Jonathan Neve.


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