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?

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.

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

Ascending

Good questions,

Ann