Databases Reference
In-Depth Information
B-tree indexes for bi-temporal tables work no differently
than B-tree indexes for non-temporal tables. Knowing how
these indexes work, our design objective is to construct indexes
that will optimize the speed of access to the most frequently
accessed data. In bi-temporal tables, we believe, that will
almost always be the currently asserted current versions of
the objects represented in those tables. As index designers,
our task is two-fold. First, we need to determine the best
columns to index on. Then we need to arrange those columns
in the best sequence.
General Considerations
The physical sequence of columns within an index has a sig-
nificant impact on the performance of queries that use that
index. Our objective is to get to the desired row in a table with
the minimum amount of I/O activity against the index, followed
by a single direct read to the table itself. So in determining the
sequence of columns in an index, a good idea is to put the most
frequently used lookup columns in the leftmost (initial) nodes of
the index. These columns are often the columns that make up
the business key, or perhaps some other identifier such as the
primary key, or a foreign key.
Against asserted version tables, most queries will be similar to
queries against non-temporal tables except that a few temporal
predicates will be added to the queries. These temporal pre-
dicates eliminate rows whose assertion time periods and/or
effective time periods are not what the query is looking for.
An object that is represented by exactly one row in a non-
temporal table may be represented by any number of rows in a
temporal table. But for normal business use, the one current
row in the temporal table, i.e. the row which corresponds to that
one row in the non-temporal table, is likely to be accessed much
more frequently than any of the other rows. Unless we properly
combine temporal columns with non-temporal columns in the
index, access to that current row may require us to scan through
many past or future rows to get to it.
Of course, we are talking about both a scan of index leaf
pages, as well as the more expensive scan of the table itself.
When specific rows are being searched for, and when they may
or may not be clustered close to one another in physical storage,
we want to minimize any type of scan.
Another important consideration in determining the optimal
sequence of columns in an index is that optimizers may decide
