>I don't believe that are any dense/sparse index scan issues with
>the current scheme. I realize that you do,

No. I don't. It's been fifteen years since my school days when I dabbled
into language/compiler design programming, file
structures and B-Trees on Apple II, CP/M, and Turbo Pascal (and a bit later,
IBM PC/XT),

These days, its mostly database design and staid accounting . So sorry about
those primitive dense/non-dense labels.I'm
sure you'll recognize this as textbook amateurism. Honestly, that's about
all I remember from school. So, I'm sorry if I sound annoying ignorant.

>but you don't seem to know how the current scheme works.

Yes, I don't. I'm actually grasping at straws. I have a vague idea about IB
indexes to avoid rattling of those disk heads (joke only, okay).

>it is rather difficult to know if you have a serious suggestion or are just

blowing smoke.

Okay, here's the smoke.

Several months back, I migrated an XBase setup to Interbase. (the database
contained millions of billing/collection records of more
than five years of an electric company that bills and collects 80,000
clients per month),The first few minutes processed thousands of records per
minute. But as the record count reached the hundred thousand records it
ground to a very slow 1 record per minute insertion, so that the computer
had been running for two days, but still it was not finished, after four
days, the computer ground to a halt of one record insertion every hour..

I tried going down to the API level, but still the same. Finally, through
the patient guidance of Ann and Bill, I got wind of selectivity issues of
indexes, and I realized that I had a foreign key referencial integrity
constraint on these millions of billing records, because the AREACODE field
of these records was referencing an AREA table which had only 15 tuples.


I cancelled the referential integrity constraint, and disabled all indexes
and the transfer got through in a few hours. Without any change in the API
program. I tried my FIB version and it finished at about the same time as
the API program. The BDE version was a bit slower but still the transfer was
finished within the day.


So, with indexes / referential integrity constraint on, the transfer went
though a very bad graph which reminded me of a graph that zoomed upwards
infinitely as it approached its upper limit.

So, is there a problem with just one type of indexes or what?

Is it alright to dream about the day when IB would have an indexing
structure that won't have selectivity problems, and database designers can
naturally design referential integrity constraints of foreign keys, without
being concerned with selectivity issues?

So, you see, I'm not suggesting an alternative. I'm pointing out a problem I
experienced it with IB. (for which I do have a workaround using primary key
suffixes, and implementing referential integrity through triggers instead of
the natural FOREIGN key syntax.

>Disks haven't changed as much as you might think.

When I was in school, our microNova minicomputer had removable 10 megabyte
disks, and it was such a big deal.
The database operations which took days to finish, now only takes a few
minutes.
Now, it is ordinary to have 9 gigabyte hard disk on a PC.

Joseph Alba
jalba@...