Michael Ludwig wrote:
Could you briefly sketch out for the interested layman why Firebird's indexes are directional (history?), what are the advantages, and what particularity of the index structure makes it suitable for just one direction, and not both?
Ann W. Harrison answers:
Brief, maybe not. Firebird does not use an external log for clean-up after a crash. Instead, it uses a technique called "careful write" to keep the database consistent on disk at all times. "Careful write" means that before you write a pointer to a thing, you write the thing . Before you delete a thing, you delete all pointers to it.
For example, before Firebird writes an index entry, it must have written the record the entry references. Before it writes the page inventory page saying that a page is free, all references to that page from the table/index or whatever it formerly belonged to must be cleared.
Indexes are trees linked down and across...
top
/ \
nextLevel <-> nextLevel
Each level is linked both left to right and right to left. That's not compatible with careful write - if two things point at each other, both must be written first. So the right left linkage is considered unsafe for general use. Descending indexes have inverted keys, so they are also read left to right.
Michael Ludwig wrote:
Second point of interest, what kind of indexes and with what directionality are used to back up the following CONSTRAINTs:
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE
Ann W. Harrison answers:
Ascending