Databases Reference
In-Depth Information
out most withdrawn assertions and also versions no longer in
effect as of the desired period of time. Another way to use the
circa flag is to make it the first column in this index or in another
index, and use it to create a separate partition for those past
assertions whose circa flag also designates them as past. As we
have said, this may not be all past assertions; but it will be most
of them.
This will keep the index entries for current and deferred
assertions together, and also separate from the index entries for
assertions definitely known to be past assertions, resulting in a
better buffer hit ratio. In fact, the index could be used as both
a clustering and a partitioning index, in which case it would also
keep more of the current rows in the target table in memory. To
the circa flag eliminating definitely past assertions, and the oid
column specifying the objects of interest, we also recommend
adding the effective end date which will filter out past versions.
The recommended clustering and partitioning index, then, is:
{circa_asr_flag, oid, eff_end_dt}.
The circa flag can also be added to other search and foreign
key indexes to help improve performance for current data. For
example, a specialized index could be created to optimize
searches for current Social Security Number data (currently
asserted current versions of that data). The index would be:
{SSN, circa_asr_flag, eff_end_dt}.
In this example, we have placed the circa flag after the SSN
column so that index entries for all asserted version rows for
the same SSN are grouped together. This means that the index
will provide a slightly lower level of performance for queries
looking for current SSN data than a {circa_asr_flag, oid,
eff_end_dt} index, assuming we know the oid in addition to the
SSN. But unlike that circa-first index, this index is also helpful
for queries looking for as-was asserted data, that data being the
mistakes we have made in our SSN data.
If we are looking for past assertions, it may also improve per-
formance to code the circa flag using an IN clause. Some
optimizers will manage short IN clause lists in an index look-
aside buffer, effectively utilizing the predicate as though it were
a match predicate rather than a range predicate.
In the following example, we follow standard conventions in
showing program variables (e.g. those in a COBOL program's
WORKING STORAGE section) as variable names preceded by
the colon character. Also following COBOL conventions, we use
hyphens in those variables. This convention was used, rather
than generic Java or other dynamically prepared SQL with “?”
parameter markers, to give an idea of the variables' contents.
