Databases Reference
In-Depth Information
entities, where the effective end dates are usually not known in
advance, many withdrawn assertions will contain an effective
end date equal to that of the currently asserted current version,
specifically the 12/31/9999 effective end date. This means that
there is greater need for the assertion end date in the index, to
push all those past assertions aside and allow us to get to the
currently asserted current version more directly.
Generalizing from this specific case, our conclusion is that
the sequence of columns for an asserted version table should
begin with the match predicates for that table, starting with the
most frequently used ones. After that, the effective end date
should be the next column in the index. For tables in which most
rows are created with a known (non-12/31/9999) effective end
date, nothing else is needed in the index. But for tables in which
most rows are created with a 12/31/9999, “until further notice”,
effective end date, we recommend that the assertion end date
be added to the index, right after the effective end date.
Currency Flags
Given the sensitivity of index use to range predicates, and the
fact that currently asserted current versions will be the most fre-
quently accessed (and frequently updated) rows in an asserted
version table, it is tempting to consider the use of flags rather than
dates to indicate currency. Flags can be used as match predicates,
performing much better than dates used as range predicates.
Some implementations of historical data do use a flag to
mark current rows. But this doesn't work for versions. For one
thing, a current version can cease to be current with the passage
of time. For another thing, if future versions are supported, they
can become current with the passage of time. And it is impossi-
ble to guarantee that whenever a current version ceases to be
current, the flag marking it as current will be changed on the
exact clock tick when it stopped being current. Similarly, it is
impossible to guarantee that whenever a future version becomes
current, the flag marking it as non-current will be changed on
the exact clock tick when it first becomes current.
For these reasons, currency flags are unreliable for versioned
data. We cannot count on them to always tell us exactly which
rows are current right now, and which rows are not. This may
be acceptable for some business data requirements, but our
implementation of Asserted Versioning is an enterprise solution,
and must also work for databases where a request for current
data will return current data no matter how recently it became
current.
