Information Technology Reference
In-Depth Information
Instead, a page-action-consistent version of the page should somehow be recovered.
How could this be possible? What should be done with the write latch? Observe that
the write latch survives even if the thread fails.
2.5
Assume that the physical structure of relation r.
A
;B;C/ is a sparse B-tree
index on .B; A/ and that the physical structure of relation s.
D
;E/ isasparseB-
tree index on D, where the leaf pages (i.e., the data pages containing the tuples)
of the B-tree are sideways linked in ascending key order: the
next-page
field in the
page header of a leaf page contains the page id of the next leaf page in key order.
Give the sequence of latchings and unlatchings of B-tree pages needed in executing
the transaction generated from the program fragment:
exec sql select
B,
count
.
/
from
r , s
where
B
D
D
group by
B;
exec sql commit
.
How many latches at most are held at a time?
2.6
The
system catalog
, also called the
data dictionary
, of a relational database
consists of several relations whose contents together contain the logical schema
(i.e., the relational schema) and the physical schema (description of storage and
index structures) of the database. A very simple system catalog might contain a
relation f with tuples such as .r; X;
b-tree
;p/ stating that for relation r there is a
B-tree index on attribute X with root-page identifier p. Obviously, in executing an
SQL
statement such as
update
r
set
V
D
V
C
1
where
X
D
x
the tuple for r must first be retrieved from the catalog relation f .Thismeansthat
some pages of f need to be latched. In what mode those pages are latched? How
long are the pages kept latched? Actually, f is not the only catalog relation that
needs to be inspected in executing the operation. How would you represent the
additional information needed here?
How are system catalog pages latched in performing the following
SQL
state-
ments?
create table
r.X;V /;
alter table
r
add primary key
.X /;
create index
I
on
r.X/.
Bibliographical Notes
The basic organization of the physical database described in this chapter already
appears in early database management systems such as System R [Astrahan et al.
1976
]and
INGRES
[Stonebraker et al.
1976
, Stonebraker,
1986
]. In System R, the
logical database is stored in logical segment spaces mapped to extents on disk
storage, with each segment consisting of equal-sized pages mapped to disk blocks.
