Databases Reference
In-Depth Information
date. Otherwise, the next column should be the assertion begin
date. In either case, we now have a unique index, which can be
used as the PK index, for queries and also for TRI enforcement.
Finally, to help with TRI enforcement, we recommend adding
the episode begin date. This is because the parent managed
object in any TRI relationship is always an episode.
Depending on whether or not the circa flag is included, this
unique index is either
{oid, circa_asr_flag, eff_end_dt, asr_beg_dt, epis_beg_dt}
or
{oid, eff_end_dt, asr_end_dt, epis_beg_dt}
Let's be sure we understand why both indexes are unique. The
unique identifier of any object is the combination of its oid, asser-
tion time period and effective time period. In the primary key of
asserted version tables, those two time periods are represented
by their respective begin dates. But because the AVF enforces
temporal entity integrity, no two rows for the same object can
share both an assertion clock tick and an effective clock tick. So
in the case of these two indexes, while the assertion begin date
represents the assertion time period, the effective end date
represents the effective time period. Both indexes contain an
object identifier and one delimiter date representing each of the
two time periods, and so both indexes are unique.
Indexes on TRI Children
Some DBMSs automatically create indexes for foreign keys
declared to the DBMS, but others do not. Regardless, since
Asserted Versioning does not declare its temporal foreign keys
using SQL's Data Definition Language (DDL), we must create
our own indexes to improve the performance of TRI enforce-
ment on TFKs.
Each table that contains a TFK should have an index on the TFK
columns primarily to assist with delete rule enforcement, such as
ON DELETE RESTRICT, CASCADE or SET NULL. These indexes
can be multi-purpose as well, also being used to assist with general
queries that use the oid value of the TFK. We should try to design
these indexes to support both cases in order tominimize the system
overhead otherwise required to maintain multiple indexes.
When a temporal delete rule is fired from the parent, it will
look at every dependent table that uses the parent's oid.Itwill
also use the four temporal dates to find rows that fall within
the assertion and effective periods of the related parent.
