Databases Reference
In-Depth Information
SELECT data
FROM mytable
WHERE SSN
:my-ssn
AND eff_beg_dt
¼
:my-as-of-dt
AND eff_end_dt
>
:my-as-of-dt
AND asr_beg_dt
<¼
:my-as-of-dt
AND assertion end date
>
:my-as-of-dt
AND circa_asr_flag IN ('Y', 'N')
<¼
In processing this query, a DB2 optimizer will first match on
SSN. After that, still using the index tree rather than a scan, it will
look aside for the effective end date under the 'Y' value for the
circa flag, and then repeat the process for the 'N' value. This uses
a matchcols of three; whereas without the IN clause, an index
scan would begin right after the SSN match. However, we only
recommend this for SQL where :my_as_of_dt is not guaranteed
to be Now(). When that as-of date is Now(), using the EQUALS
predicate ({circa_asr_flag
'Y'}) will perform much better since
the 'N's do not need to be analyzed.
Query-enhancing indexes like these are not always needed.
For the most part, as we said earlier, these indexes are specifi-
cally designed to improve the performance of queries that are
looking for the currently asserted current versions of the objects
they are interested in, and in systems that require extremely high
read performance.
¼
Indexes to Optimize Temporal Referential Integrity
Temporal referential integrity (TRI) is enforced in two direct-
ions. On the insert or temporal expansion of a child managed
object, or on a change in the parent object designated by its tem-
poral foreign key, we must insure that the parent object is pres-
ent in every clock tick in which the child object is about to be
present. On the deletion or temporal contraction of a parent
managed object, we must RESTRICT, CASCADE or SET NULL
that transformation so that it does not leave any “temporal
orphans” after the transaction is complete.
In this section, we will discuss the performance con-
siderations involved in creating indexes that support TRI checks
on both parent and child managed objects.
Asserted Versioning's Non-Unique Primary Keys
First, and most obviously, each parent table needs an index
whose initial column will be that table's object identifier (oid).
The object identifier is also the initial column of the primary
