Databases Reference
In-Depth Information
state
pa
pa
pa
pe
pe
pe
fa
cc
fa
row #
oid
circa
eff-end
asr-beg
asr-end
eff-beg
data
1
3
5
2
4
6
8
7
9
55
55
55
55
55
55
55
55
55
N
N
N
Y
Y
Y
Y
Y
Y
9999
9999
9999
Mar09
Jun09
Aug09
Dec13
9999
9999
Jan09
Feb09
Jun09
Feb09
Jun09
Aug09
Oct12
Aug09
Oct12
Feb09
Jun09
Aug09
9999
9999
9999
9999
Oct12
9999
Jan09
Mar09
Jun09
Jan09
Mar09
Jun09
Aug09
Aug09
Dec13
Apples
Berries
Cherries
Apples
Berries
Cherries
Kiwi
Kiwi
Grapes
Figure 15.2 A Bi-Temporal Table with a Circa Flag.
in the index, for example: {oid, circa_asr_flag, eff_end_dt}. If the
assertion end date were used instead of the circa flag, then the
effective end date would require an index scan, prior to reaching
the desired index entries. But by replacing the assertion end date
with a match predicate, the effective end date becomes the first
range predicate following the match predicates, and conse-
quently can be processed without doing a scan.
Let's assume that it is now September 2011, and that the table
we are querying is as shown in
Figure 15.2
. The circa flag has
been added to the table shown in
Figure 15.1
, columns have
been rearranged, and the rows from the original table have been
resequenced on the index columns. Those columns are shown
with their column headings shaded.
Note that row 7 has a non-12/31/9999 assertion end date. Its
assertion end date is still in the future because the AVF processed
a deferred temporal update against that row. That deferred temporal
update created the deferred assertion which is row 8. In a year and
amonth, onOctober 2012, two rows will change their assertion time
status, and will do so “quietly”, simply because of the passage of
time. Row 7 will fall into the assertion time past and, at the same
moment, row 8 will fall into the assertion time present.
Row 7 will cease to be currently asserted on that date. How-
ever, its circa flag will remain unchanged. As far as the flag can
tell us, it remains a possibly current row. Also, row 8 will become
currently asserted on that date. It was a possibly current row all
along, and now it has become an actually current one. But its
circa flag remains unchanged. That flag does not attempt to dis-
tinguish possibly current rows from actually current ones.
At some point, the SQL statement shown earlier will run. It
will change the circa flag on row 7 to 'N', indicating that row 7
is definitely not a currently asserted row, and can never become
one.
