Databases Reference
In-Depth Information
The following query will correctly filter and select the cur-
rently asserted current version of object 55 regardless of when
the query is executed, and regardless of when the flag reset pro-
cess is run. This is a query against the table shown in
Figure 15.2
,
and let's assume that it is now September 2011.
SELECT data
FROM mytable
WHERE oid
¼
55
AND circa_asr_flag
¼
'Y'
AND eff_beg_dt
<¼
Now() AND eff_end_dt
>
Now()
AND asr_beg_dt
<¼
Now() AND asr_end_dt
>
Now()
Processing this query, and using the index, the optimizer will:
(i) Match exactly on the predicate {oid
55}
(ii) Match exactly on the predicate {circa_asr_flag
¼
'Y'}; and
¼
>
Now()}, it
will position and start the index scan on the row with the
first effective end date later than now, that row being row 8.
We have reached the first range predicate value, and have
done so using only the index tree. At this point, an index scan
begins; but we have already eliminated a large number of rows
from the query's result set without doing any scanning at all.
When there are no more future effective versions found in the
index scan, we will have assembled a list of index pointers to all
rows which the index scan did not disqualify. But in this example,
there is one more rowwith a future effective begin date, that being
row 7. So, from its scan starting point, the index will scan rows 8, 7
and 9 and apply the other criteria. If some of the other columns
are in the index, it will probably apply those filters via the index.
If no other columns are in the index, it will go to the target table
itself and apply the criteria that are not included in the index.
Doing so, it will return a result set containing only row 7. Row
7's assertion end date has not yet been reached, so it is still cur-
rently asserted. And the assertion begin dates for rows 8 and 9
have not yet been reached, so they are not yet currently asserted.
In many cases, there will be no deferred assertions or future
versions, and so the first row matched on the three indexed
columns will be the only qualifying row. Whenever that is the
case, we won't need the other temporal columns in the index.
So restricting the index to just these three columns will keep
the index smaller, enabling us to keep more of it in memory. This
will improve performance for queries that retrieve the current
row of objects that have no deferred assertions or future vers-
ions, but will be slightly slower when retrieving the current rows
of objects that have either or both.
(iii) Then, using its first range predicate, {eff_end_dt
