Databases Reference
In-Depth Information
one. So for a policy table, there would be fewer rows with an
effective end date greater than Now(), because there would be
fewer rows with a 12/31/9999 effective end date to withdraw into
past assertion time. For a client table, it would be a toss-up.
Since one withdrawn row is created for every temporal update,
the number of rows for that object with an assertion end date
greater than Now(), and the number of rows with an effective
end date greater than Now() would tend to be roughly equal.
There is also an update performance issue with including the
assertion end date anywhere in the index. Every time an episode
is updated, a currently asserted row is withdrawn; and so its
assertion end date is changed. This would require an update to
the index, if the assertion end date is in that index; and it would
happen every time a temporal update or a temporal delete is
processed. By leaving the assertion end date out of the index,
these frequent updates will not affect the index.
By a process of elimination, we have come to {oid, eff_end_dt}
as the sequence of columns that will best optimize the perfor-
mance of queries looking for the currently asserted current vers-
ions of objects. In this case, the optimizer will match on the 55,
and then apply the GREATER THAN predicate to the second
indexed column, eff_end_dt such as “eff_end_dt
Now()”. But
for tables whose updates usually result in a version with a 12/
31/9999 effective end date, the effective end date will not sepa-
rate the currently asserted current version from the withdrawn
versions for the same object. The best way to do that is to add
the assertion end date as the last column in the index, giving
us {oid, eff_end_dt, asr_end_dt}. Even though it will require an
index scan to filter the assertions, doing so will often reduce
the number of I/Os to the main table.
As we noted earlier, however, the assertion end date is
updated every time a temporal update is carried out. It is
updated as the then-current row is withdrawn into past assertion
time, making room for the row or rows that replace it, or else
replace and supercede it. So these physical updates will require
a physical update to the corresponding index entry as well.
The decision of whether or not to include the assertion end
date in an index designed to optimize access to the currently
asserted current versions of objects, therefore, requires careful
analysis of the specific situation. For policies and similar kinds
of entities, where the effective end dates are usually known in
advance, most withdrawn assertions will have an effective end
date less than that of the currently asserted current version for
the policy. This means that there is less need for the assertion
end date in the index. But for clients and similar kinds of
>
