CS245 PROBLEM SET #2 Solutions

Problem 1: Conventional Indexes (20 points)

Q1:

select * from R where R.A > a1 and R.A < a2

Q2:

select * from R, S where R.B = S.B

a)

What is the type of the index (dense, sparse, or multi-level)?

b)

Which attribute is the index built on?

c)

Is it a primary or a secondary index?

d)

How should we use the index for the queries?

Solution:

We can build three indexes as follows:

• Index 1 -- a sparse primary index on R.A.
  
• Index 2 -- a dense secondary index on R.B.
  
• Index 3 -- a dense primary index on S.B.
  

We use Index 2 and Index 3 for Q2. When executing Q2, we intersect Index 2 and Index 3 to find all pointers to the joined tuples in R and S, then fetch the tuples and return the join result.

NOTE: We store all indexes in memory to achieve the best query performance. In this case, a sparse index on S.B is not as good as a dense index, because not all keys appear in the sparse index, and we may have to fetch a data block for each R.B value, even if it is not in the sparse index on S.B.

Problem 2: B+-tree and B-tree (20 points)

• 2KB fixed-size blocks
• 12-byte pointers
• 56-byte block headers

a)

a 3-level B+ tree (2 levels plus the root)

b)

a 3-level B tree

Solution:

a)

Let each node of a B+-tree contain at most n pointers and n-1 keys. 8 * (n-1) + 12 * n + 56 <= 2048. Therefore, n <= 100. The leaf level of a B+-tree can hold at most 99 * 100 * 100 record pointers. Therefore, the maximum number of records that can be indexed is 990000.

b)

Let each inner node of a B-tree contain at most n index pointers, n-1 keys, and n-1 record pointers. 8 * (n-1) + 12 * (2n-1) + 56 <= 2048. Therefore, n <= 62. The first level of a B-tree can hold at most 61 record pointers. The second level can hold at most 62 * 61 record pointers. The leaf level can hold at most 62 * 62 * 61 record pointers. Therefore, the maximum number of records that can be indexed is 61 + 62 * 61 + 62 * 62 * 61 = 238327.

Problem 3: B+-tree Insertions and Deletions (20 points)

Show the B+ tree that results after inserting (in the given order) 56, 50, 75, 87, 48. From that resulting B+ tree we delete (in the given order) 50, 24, 65, 93, 75. What is the resulting final B+ tree?

Solution:

After insertions:

After deletions:

NOTE: There are alternative correct answers to this problem. However, you need to make sure that your solution follows the insertion and deletion algorithm described in the class.

Problem 4: Extensible and Linear Hashing (20 points)

   a [010000]
   b [011010]
   c [111100]
   d [001110]
   e [010111]
   f [011010]
   g [101001]
   h [010111]
   i [000110]
   j [101001]

a)

Show the extensible hash structure after these records have been inserted.

b)

Show the linear hash structure after these records have been inserted. Assume that the threshold value is 2. (i.e., when the average number of keys per non-overflow bucket is greater than 2, we allocate another bucket).

Solution

a)

b)

Problem 5: Multiple-Key Index (20 points)

1)

C is "X = x" in 20% of the cases

2)

C is "Y = y" in 30% of the cases

3)

C is "X = x and Y = y" in 50% of the cases

a)

Build a multiple-key index with X as the first attribute

b)

Build a multiple-key index with Y as the first attribute

c)

Build two secondary indexes on X and Y, using the "pointer bucket" technique (described in the lecture) to deal with duplicate values.

Solution

Assume that there are totally 1024 X values and 1024 Y values. Further assume that the index entries are stored in contiguous blocks. Each block can hold 1K pointers or 0.5K pairs.

a)

The first level index contains two blocks. For each X value, the second-level index on Y also contains two blocks. For query 1), we first search X = x in the first level index. The average cost for this step is 1.5 I/Os. We then fetch both block of second level index on Y for the X value, which takes 2 I/Os. Finally, we fetch the result tuples, which takes 1024 I/Os. For query 2), we first scan both blocks of the first level index on X. For each X value, we search Y = y in the second level index of that X value. The average cost for this step is 1.5 I/Os. Finally, we fetch the result tuples, which takes 1024 I/Os. Therefore, the average query cost is

20% * (1.5 + 2 + 1024) + 30% * (2 + 1.5 * 1024 + 1024) + 50% * (1.5 + 1.5 + 1) = 976.1 I/Os

b)

Similarly to a), the average query cost is

20% * (2 + 1.5 * 1024 + 1024) + 30% * (1.5 + 2 + 1024) + 50% * (1.5 + 1.5 + 1) = 822.65 I/Os

c)

For query 1), we use the secondary index on X to find all pointers to tuples with X = x. This takes 1.5 + 1 = 2.5 I/Os. We then fetch the result tuples which takes 1024 I/Os. For query 2), we use the secondary index on Y to find all pointers to tuples with Y = y. This takes 1.5 + 1 = 2.5 I/Os. We then fetch the result tuples which takes 1024 I/Os. For query 3), we first use the secondary index on X to find all pointers to tuples with X = x, which takes 2.5 I/Os. We then use the secondary index on Y to find all pointers to tuples with Y = y., which takes 2.5 I/Os. Finally, we intersect the two buckets of pointers in memory, and fetch the result tuples, which takes 1 I/O. The average query cost is

20% * (1.5 + 1 + 1024) + 30% * (1.5 + 1 + 1024) + 50% * (1.5 + 1 + 1.5 + 1 + 1) = 516.25 I/Os