Databases Reference
In-Depth Information
state
pa
pe
pa
pe
pa
pe
cc
fa
fa
row #
oid
eff-beg
eff-end
asr-beg
asr-end
data
1
2
3
4
5
6
7
8
9
55
Jan09
Jan09
Mar09
Mar09
Jun09
Jun09
Aug09
Aug09
Dec13
9999
Mar09
9999
Jun09
9999
Aug09
9999
Dec13
9999
Jan09
Feb09
Feb09
Jun09
Jun09
Aug09
Aug09
Oct12
Oct12
Feb09
9999
Jun09
9999
Aug09
9999
Oct12
9999
9999
Apples
Apples
Berries
Berries
Cherries
Cherries
Kiwi
Kiwi
Grapes
55
55
55
55
55
55
55
55
Figure 15.1 A Bi-Temporal Table.
Two of those rows are not yet asserted. They are deferred
assertions. We are not yet willing to claim that the statements
made by those rows are true statements. We designate these
rows with “fa” (future asserted) in the state column of the table.
There is one current row representing the object whose iden-
tifier is 55. This row is currently asserted and, within current
assertion time, became effective in August 2009 and will remain
in effect until further notice. Note, however, that it will remain
asserted only until October 2012. At that time, if nothing in the
data changes, the database will cease to say that the data for
object 55 is Kiwi from August 2009 until further notice. Instead,
it will say that data for object 55 is Kiwi from August 2009 to
December 2013, and that from December 2013 until further
notice, it will be Grapes. We designate this earlier, but current,
row with “cc” (currently asserted current version) in the state
metadata column of the table.
The SQL to retrieve the one current row for object 55 is:
SELECT data
FROM mytable
WHERE oid
¼
55
AND eff_beg_dt
<¼
Now() AND eff_end_dt
>
Now()
AND asr_beg_dt
<¼
Now() AND asr_end_dt
>
Now()
Most optimizers will use the index tree to locate the row id
(rid) of the qualifying row or rows using, first of all, the columns
that have direct matching predicates, such as EQUALS or IN,
columns which are sometimes called match columns. These
optimizers will also use the index tree for a column with a range
predicate, such as BETWEEN or LESS THAN OR EQUAL TO
(
<¼
), provided that it is the first column in the index or the first
column following the direct match columns.
