Bulk Operations on B-Trees - Transaction Processing - page 241

Example 10.8 In the case of the query

select sum ( sales-amount ) from sales
where sales-date “2015-07-15” and sales-date “2015-08-15”
we have, with the partition granularities of Example 10.7 ,
g 1 .2015-07-15;s;i/ D .2015; 3/ D g 1 .2015-08-15;s 0 ;i 0 /,but
g 2 .2015-07-15;s;i/ D .2015; 7; 15/ 6D .2015; 8; 15/ D g 2 .2015-08-15;s 0 ;i 0 /.
Thus, the sales relation is IS-locked and the fragment . sales ; 2015; 3/ is S-locked.
In this case the key of the tuple next to the last tuple read belongs to the S-locked
fragment, so that no additional locks need be acquired. t

When a key range spans two coarse-granule fragments, an unnecessarily wide
range may be locked. Maintaining knowledge about the structure of multi-attribute
keys in the system catalog may help the query processor to split such a range
predicate into a disjunction of range predicates that only involve finer-granule
fragments.

Example 10.9 In the case of the query

select sum ( sales-amount ) from sales
where sales-date “2015-12-15” and sales-date “2016-01-15”
we have
g 0 .2015-12-15;s;i/ D ./ D g 0 .2016-01-15;s 0 ;i 0 /,but
g 1 .2015-12-15;s;i/ D .2015; 4/ 6D .2016; 1/ D g 2 .2016-01-15;s 0 ;i 0 /.
Thus, the entire sales relation would be S-locked. However, with suitable infor-
mation stored in the system catalog, it can easily be inferred from the partition
specifications and the known properties of the SQL data type date that it is wise
to rewrite the selection predicate as the following disjunction:

sales-date “2015-12-15” and sales-date “2015-12-31”
or sales-date “2016-01-01” and sales-date “2016-01-15”.
The former disjunct leads to S-locking the fragment . sales ; 2015; 4/ and the latter
the fragment . sales ; 2016; 1/. t

In the case of a bulk-insert action IŒs XV , the source of the insertion, the relation
s XV , is queried in order to find out the fragments that need be locked. To that end,
for each partition granularity g i , i D 0;:::;m 1, we determine a temporary
relation temp- s i that tells the keys of g i -fragments and the number of contained
g iC1 -fragments: each tuple in temp- s i consists of the key of one fragment and the
count of tuples in the input belonging to that fragment.
Using the above temporary relations together with a specified upper limit on
the number of X locks to be acquired, we can prune the sets of fragments until
the number of X locks to be acquired falls below the limit. The pruning proceeds
from finer-granule fragments to coarser-granule fragments. First, if a fragment g i .x/
contains more g iC1 -fragments than the specified limit, then those g iC1 -fragments