Thanks for the comments.

POINT #1. Selectivity being a value.

The points raised regarding expounding on selectivity as a value rather
than a feature are taken. (But honestly, I would rather have AVOID
SELECTIVITY term in my mind map rather than AVOID INDEX keys with
SELECTIVITY values of more than 100 per key value -- or whatever. As
long as the semantics (meaning) are defined and explained before hand,
then maybe, I can safely use the two words to refer to the problem of
avoiding indexes with key values that have high cardinality.)

Change to article: Carefully define what I mean by Avoid Index
Selectivity. Illustrate even. How about, Avoid poor selectivity?

POINT #2. Multikey indexes

>This handicap forces Interbase developers to use multi-key indexes

which

>cannot be joined

Ann:
Hunh? Multi-key indexes should work in joins. Why do you think they
can't?

Me:
I mean, suppose you had an multi key index of:

BILL_TYPE, AREACODE, PERIOD_YEAR, PERIOD_MONTH

This index is totally useless in a query like:

SELECT * FROM BILL WHERE PERIOD_YEAR=2001

Or

SELECT * FROM ACCTG_PERIOD A, BILL B
WHERE A.PERIOD_YEAR=B.PERIOD_YEAR
AND A.PERIOD_MONTH=B.PERIOD_MONTH

Or even a simple order like

SELECT * FROM BILL
ORDER BY PERIOD_YEAR, PERIOD_MONTH, AREACODE, BILL_TYPE

That's because the sequence of the key starts with BILL_TYPE.

So, you must have multiple multi-key indexes where the sequence of your
keys align with your needs.


But if you just use a single key index for BILL_TYPE column, another for
AREACODE, etc... however you jumble up these columns, Interbase is
nimble enough to optimally join these indices for every possible
combination.

POINT #3. Oracle and Interbase index characteristics.

I did not mean to imply that Oracle and Interbase used the same index
structure or algorithm. That's why I took care to use the term
characteristics.

By this I meant to point out that in version 8, Oracle proudly announced
that this new bitmap index is so nimble that you can make single key
indexes on every column that could be involved in a WHERE, JOIN, or
ORDER clause, and the bitmap index will be able to optimize these by
combining the single key indexes. This is what I meant by
characteristics.

But, is it not the same optimal characteristic that Interbase indexes
have? It is, right?

And what is so ironic is that while Interbase had this characteristic a
long time ago (which Oracle just recently boasts about), we in the
Interbase community AVOID this feature because we have the AVOID INDEX
SELECTIVITY mantra which forces us to use multi-key index, instead of
single key indexes.

And this is the whole point of my article.

Point #4. Regarding Ann's suggestion of having a new improved index
version that could result to larger indexes but avoid selectivity
problems.

Maybe Interbase can take a page from Oracle and have another index
(Ann's index) on top of the old index. To specify that we intend to
create this type of index, we can add the keyword 'SELECTIVITY'.

e.g.

CREATE INDEX BILL_TYPE ON BILL(BILL_TYPE) SELECTIVITY

Without SELECTIVITY at the end, then Interbase will create the old style
bitmap index which is smaller in size but has problems with selectivity.


Joseph Alba
jalba@...