Databases Reference
In-Depth Information
consider the index sequence as described earlier to reduce scans,
but then add the episode begin date.
Instead of creating a separate index for TRI parent-side
tables, we could try to minimize the number of indexes by re-
using the primary key index to:
(i) Support uniqueness for a row, because some DBMS
applications require a unique PK index for single-row
identification.
(ii) Help the AVF perform well when an object is queried by its
object identifier; and
(iii) Improve performance for the AVF during TRI enforcement.
So we recommend an index whose first column is the
object identifier of the parent table. Our proposed index is
now {oid,.....}.Next,weneedtodetermineifweexpectcur-
rent data reads to the table to outnumber non-current reads or
updates.
If we expect current data reads to dominate, then the next
column we might choose to use is the circa flag. If this flag is
used as a higher-level node in the index, then TRI maintenance
in the AVF can use the {circa_asr_flag
'Y'} predicate to ignore
most of the rows in past assertion time. This could significantly
help the performance of TRI maintenance. Using the circa flag,
our proposed index is now {oid, circa_asr_flag. . . . .}. The
assumption here is that the DBMS allows updates to a PK value
with no physical foreign key dependents because the circa flag
will be updated.
Just as in any physical data modeling effort, the DBA or Data
Architect will need to analyze the tradeoffs of indexing for reads
vs. indexing for updates. The decision might be to replace a sin-
gle multi-use index with several indexes each supporting a dif-
ferent pattern of access. But in constructing an index to help
the performance of TRI enforcement, the next column should
be the effective end date, for the reasons described earlier in this
chapter. Our proposed index is now {oid,
ΒΌ
circa_asr_flag,
eff_end_dt, .....}.
After that, the sequence of the columns doesn't matter much
because the effective end date is used with a range predicate, so
direct index matching stops there. However, other columns are
needed for uniqueness, and the optimizer will still likely use
any additional columns that are in the index and qualified as
criteria, filtering on everything it got during the index scan rather
than during the more expensive table scan.
If the circa flag is not included in the index, and the DBMS
allows the update of a primary key (with no physical
dependents), then the next column should be the assertion end
