Databases Reference
In-Depth Information
time flag, and code a {circa_eff_flag
¼
'Y'} predicate in the MQT
definition.
MQTs could also be helpful when joining multiple asserted
version tables together.
With most MQT tables, we have a choice of refreshing them
either periodically or immediately. Our choice depends on what
the DBMS permits, and the requirements of specific applications
and specific MQTs.
Standard Tuning Techniques
In addition to tuning techniques specific to Asserted
Versioning databases, there are general tuning techniques that
are just as applicable to temporal tables as to conventional or
non-temporal ones.
Use Cache Profligately. Per-unit memory costs, like per-unit
costs for other hardware components, are falling. Multi-gigabyte
memory is now commonplace on personal computers, and tera-
byte memories are now found on mainframe computers. Try to
get as many and as much of your indexes in cached buffers as
you can. Reducing physical I/O is essential to good performance.
Use Parameter Markers. If we cannot use static SQL for a fre-
quently executed large data volume query, then the next best
thing is to prepare the SQL with parameter markers. Many
optimizers will perform a hashed compare of the SQL to the
database dynamic prepared SQL cache, then a direct compare
of the SQL being prepared, looking for a match. If it finds a
match, it will avoid the expensive access path determination
optimization process, and will instead use the previously deter-
mined access path rather than trying to re-optimize it.
The reason for the use of parameter markers rather than
literals for local predicates is that with cache matching, the opti-
mizer is much more likely to find a match. For example, a
prepared statement of
SELECT * FROM mytable WHERE oid
¼
55
does not match
SELECT * FROM mytable WHERE oid
¼
44
causing the statement to be re-optimized. But a prepared SQL
statement of
SELECT * FROM mytable WHERE oid
¼
?
will find a match whether the value of the parameter marker is
44, 55, or any other number, and in most cases will not need to
be re-optimized.
