Databases Reference
In-Depth Information
To understand how this index produces correct results
whether it is run before or after the circa flag update process
changes any flag values, let's assume that it is now November
2012, and the flag update process has not yet adjusted any flag
values. In September, row 7 was the current row, and our use
of the index correctly led us to that row. Now it is October, and
the current row is row 9. Without any changes having been made
to flag values, how does the index correctly lead us to this differ-
ent result?
Prior to this tick of the clock, the table contained a current
assertion with an October 2012 end date, and a deferred assertion
with an October 2012 begin date. Because flag values haven't
changed, our first three predicates will qualify the same three
rows, rows 7, 8 and 9. But now row 7 will be filtered out because
right now, November 2012 is past the assertion end date of Octo-
ber 2012. Row 9 will be filtered out because the effective begin
date of December 2013 has not yet been reached. But row 8 meets
all of the criteria and is therefore returned in the result set.
If the update of the circa flag is run on January 2013, let's
say, it will change row 7's flag from 'Y' to 'N' because the asser-
tion end date on that row is, when the process is run, in the
past. Now, if our same query is run again, there will only be
two rows to scan, two currently asserted rows. The SQL will
correctly filter those two rows by their effective time periods,
returning only the one row which is, at that time, also currently
in effect.
Recall that the purpose of the circa flag is to optimize access
to the most frequently requested data, that being current
assertions about what things are currently like, i.e. currently
asserted and currently effective rows. We note again that rows
which make current assertions about what things are currently
like are precisely the rows we find in non-temporal tables. Rather
than being some exotic kind of bi-temporal construct, they are
actually the “plain vanilla” data that is the only data found in
most of the tables in a conventional database. For queries to
such data, asserted version tables containing a circa flag, and
having the index just described, will nearly match the perfor-
mance of non-temporal tables.
Other Uses of the Circa Flag
While we have said that the purpose of this flag is to improve
the performance of queries for currently asserted and currently
effective data, it will also help the performance of queries for
currently asserted but not currently effective versions by filtering
