Information Technology Reference
In-Depth Information
necessary information for completing the construction of the secondary index with
updates done by transactions during the key-extraction process, so that maintaining
a separate side file might seem unnecessary. Evaluate the pros and cons of these two
solutions.
11.7
We cannot allow the secondary index to be dropped while transactions are
accessing it. Explain why. What actions are involved in dropping an index? What
locks and latches are needed to protect index dropping?
11.8
In the index-construction algorithm, it is possible to get rid of the short-
duration S lock acquired on the relation at the beginning of index-record extraction
if we include in the log record written for a tuple insertion or deletion a field that
indicates the current state of the secondary index. What changes are needed in
the algorithms? Also note that the no-append-to-side-file log records are no longer
needed.
11.9
Generalize the online index-construction algorithm to the case in which
several secondary indexes can exist on a relation.
11.10
With a B-tree secondary index having records of the form .y;x;p/ in its
leaf pages, the page-id p of the data page holding the indexed tuple .x; y;
v
/ is
not maintained when the tuple migrates to another data page p
0
. Outline an online
reorganization algorithm for the secondary index that updates the page-ids left
outdated due to tuple migration.
11.11
Assume that relation r is reorganized using the online reorganization algo-
rithm outlined in Sect.
11.7
. When can the old structure of r be deallocated?
11.12
The online reorganization algorithm outlined in Sect.
11.7
does not observe
secondary indexes that may exist on the relation whose primary B-tree index is being
reorganized. Modify the algorithm so as to observe secondary indexes.
Bibliographical Notes
The organization of secondary indexes presented in this chapter follows that used
for index-organized tables in Oracle8i [Srinivasan et al.,
2000
]. The key-specific
locking protocol is our adaption of the index-specific locking protocol
ARIES/KVL
by Mohan [
1990a
,
1996a
]. We prefer the attribute key-specific to index-specific so
as to emphasize that the locks are logical, not physical.
The problem of online index construction is addressed by Stonebraker [
1989
],
who proposes partial indexes as a solution. A partial index is an index under
construction with an attached tuple-id-range or primary-key-range predicate stating
the subset of tuples it currently indexes. Other methods for online index construction
are proposed by Srinivasan and Carey [
1991
,
1992
] and Mohan and Narang [
1992
],
among others. These algorithms are surveyed by Sockut and Iyer [
2009
].
