Information Technology Reference
In-Depth Information
In
SQL
, bulk reads, deletes, and updates on relation r.
XYV
/ can take more
complicated forms such as
select
from
r
where
C
1
.X /
and
C
2
.
XY
/,
delete from
r
where
C
1
.X /
and
C
2
.
XY
/,
update
r
set
V
D
f.V/
where
C
1
.X /
and
C
2
.
XY
/,
where C
1
.X / is a disjunction of range predicates on the primary key X and C
2
.
XY
/
is a
data predicate
that depends on the values of the attributes in
XY
of the tuple to
be deleted. Moreover, bulk deletes and updates are often
cursor-based
such as in the
following:
exec sql declare
t
cursor for
select
from
r
where
C
1
.X /
and
C
2
.
XY
/
exec sql open
t
while
unfetched tuples exist
do
exec sql fetch
t
into
:x,:y,:
v
if
C
3
.
XYV
/ holds for x, y and
v
then
exec sql delete from
r
where current of
t
end if
end while
Here C
3
.
XYV
/ is a
residual predicate
that depends both on the attribute values of
the fetched tuple and on the values of some host-program variables (or, in the case
of an internal
system cursor
, on some query-processor variables).
In all these cases, however, if a B-tree index exists on the primary-key attribute
X , the algorithms to be presented for the bulk actions in our transaction model
can easily be augmented to also evaluate any data predicates C
2
.
XY
/ or residual
predicates C
3
.
XYV
/ during the index scan.
The model of bulk-action transactions differs significantly from our key-range
transaction model. A single bulk action can span any number of data pages and
hence can no longer be implemented as an atomic action accomplished by latching
one data page or two successive data pages of a sparse B-tree index. A bulk-insert,
bulk-delete, or bulk-update action must be logged with several log records. The state
of a transaction cannot be represented with sufficient precision as a sequence of bulk
actions.
As a bulk action can be left incomplete due to a failure, we need to fix an order
in which the tuples in the bulk are processed when executing the action. Obviously,
the only meaningful order is the key order. Thus, the execution of a bulk-read action
RŒs
X
;s
XV
, bulk-delete action DŒs
X
;s
XV
, or bulk-update action WŒs
X
;f;s
XV V
0
involves sorting the key set s
X
, and the execution of a bulk-insert action IŒs
XV
involves sorting the tuple set s
XV
in key order before starting the B-tree traversal.
In defining the state of a bulk-action transaction T as a sequence of actions
executed, we view each bulk action as
expanded
into a sequence of tuple-wise
actions, that is, single-tuple reads, inserts, and deletes. In such a state, the last bulk-
action may be incomplete, with only a prefix of the key-ordered tuple-wise action
sequence executed.
