Databases Reference
In-Depth Information
The predicate to find dependents in any contained clock tick
would look something like this:
WHERE parent_oid
¼
:parent-oid
AND eff_beg_dt
<
:parent-eff-end-dt
AND eff_end_dt
>
:parent-eff-beg-dt
AND circa_asr_flag
¼
'Y' (if used)
AND asr_end_dt
>¼
Now()
(might have deferred assertion criteria, too)
In this SQL, the columns designated as parent dates are the
effective begin and end dates
specified on the delete
transaction.
In an index designed to enhance the performance of the
search for TRI parent-child relationships, the first column
should be the TFK. This is the oid value that relates a child to a
parent.
Temporal referential integrity checks are never concerned
with withdrawn assertions, so this is another index in which
the circa flag will help performance. If we use this flag, it should
be the next column in the index. However, if this is the column
that will be used for clustering or partitioning, the circa flag
should be listed first, before the oid.
For TRI enforcement, the AVF does not use a simple
BETWEEN predicate because it needs to find dependents with
any overlapping clock ticks. Instead,
it uses an [
intersects]
predicate.
Two rules used during TRI delete enforcement are that the
effective begin date on the episode must be less than the effec-
tive end date specified on the delete transaction, and that the
effective end date on the episode must be greater than the effec-
tive begin date on the transaction.
Earlier, we pointed out that for current data queries, there are
usually many more historical rows than current and future rows,
and for that reason we made the next column the effective end
date rather than the effective begin date. These same con-
siderations hold true for indexes assisting with temporal delete
transactions.
Therefore, our recommended index structure for TFK
indexes, which can be used for both TRI enforcement by the
AVF, and also for any queries looking for parent object and
child object relationships, where the oid mentioned is the TFK
value, is either {parent_oid, circa_asr_flag, eff_end_dt.....}or
{parent_oid, eff_end_dt, asr_end_dt. ....}.
Other temporal columns could be added, depending on
application-specific uses for the index.
