CS554, Homework 5

Question 1 (20 pts)

Given:

The content of a relation R is as follows: d d d d .... d a a a a ... a c c c c .... c b b b b ....b ^^^^^^^^^^^^^^ ^^^^^^^^^^^^^ ^^^^^^^^^^^^^^ ^^^^^^^^^^^^^ 100 tuples 100 tuples 100 tuples 100 tuples R has a sorted clustering index file on its tuples: (a b c d) The index file contains the record location of the first tuple with that given value

We use the sorted index to access all tuples of relation R in a ordered manner as follows:

Read index file to get the location of the tuple with the next smallest value Access all (clustering) tuples with the next smallest value

Questions:

If each data block can store exactly 100 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file)

d: 1 blocks a: 1 blocks c: 1 blocks b: 1 blocks --------------------- Total: 4 block accesses

If each data block can store exactly 50 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file)

d: 2 blocks a: 2 blocks c: 2 blocks b: 2 blocks --------------------- Total: 8 block accesses

If each data block can store exactly 51 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file)

Storage structure: 51 49 2 51 47 4 51 46 6 | d d .. d | d d .. d a a | a a .. a | a .. a c c c | c .. c | c .. c b..b | b .. b | b .. b | blk 1 blk 2 blk 3 blk 4 blk 5 blk 6 blk 7 blk 8 Access: a: 3 blocks (block 2, 3 and 4) b: 3 blocks (block 6, 7 and 8) c: 3 blocks (block 4, 5 and 6) d: 2 blocks (block 1 and 2) --------------------- Total: 11 block accesses

If each data block can store exactly 49 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file)

Storage structure: 49 49 2 47 49 4 45 49 6 43 | d .. d | d .. d | d d a..a | a .. a | a..a c..c | c .. c | c..c b..b | b..b | b..b | blk 1 blk 2 blk 3 blk 4 blk 5 blk 6 blk 7 blk 8 blk 9 a: 3 blocks (block 3, 4, 5) b: 3 blocks (block 7, 8, 9) c: 3 blocks (block 5, 6, 7) d: 3 blocks (block 1, 2, 3) --------------------- Total: 12 block accesses

Question 2 (40 pts)

Given:

R = R(A, B, C) // R has attributes A, B, C B(R) = 1000 block T(R) = 10000 tuples V(R, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 10000/100 = 100 for any value a S = S(A, D, E) // S has attributes A, D, E B(S) = 200 block T(S) = 4000 tuples V(S, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 4000/100 = 40 for any value a

What is the minimum memory requirement for the following algorithms:

One-pass join R ⋈ S:

M ≥ B(S) + 1 = 200 + 1 = 201

The IO-efficient version of the Two-pass TPMMS based join R ⋈ S:

M ≥ sqrt(B(R) + B(S)) = sqrt(1000+200) = 35 (round up)

The IO-intensive version of the Two-pass TPMMS based join R ⋈ S:

Sort R: M ≥ sqrt( 1000 ) = 32 (round up) Sort S: M ≥ sqrt( 200 ) = 15 (round up) Join: 1 buffer to read R 1 buffer to read S # blocks in R with "A = a" = B(R)/100 = 1000/10 = 10 # blocks in S with "A = a" = B(S)/100 = 200/10 = 2 M ≥ 1 + 1 + 10 + 2 = 14 Answer: M ≥ max ( sqrt( 1000 ), sqrt( 200 ), 14 ) = sqrt( 1000 ) = 32

Two-pass Hashing based join R ⋈ S:

M ≥ sqrt( min( B(R), B(S) ) + 1 = sqrt( 1000, 200 ) + 1 = sqrt(200) + 1 = 15 + 1 = 16 (round up) (missing + 1 is OK)

Three-pass Hashing based join R ⋈ S:

M ≥ CubeSqrt( min( B(R), B(S) ) + 1 = CubeSqrt( min(1000, 200) ) + 1 = CubeSqrt( 200 ) + 1 = 6 + 1 = 7 (round up) (missing + 1 is OK)

Block-based nested loop join R ⋈ S:
Suppose S has an ordered clustering index on their join attribute.
What's the minimum # buffers needed for the Index join algorithm (you can ignore the buffers to read the index file):
Suppose S has an ordered non-clustering index on their join attribute.
What's the minimum # buffers needed for the Index join algorithm (you can ignore the buffers to read the index file):

Suppose both R and S have an ordered clustering index on their join attribute.

What's the minimum # buffers needed for the Zig-zag join algorithm (you can ignore the buffers to read the index file):

Suppose R and S have a sorted non-clustering index on their join attribute.

What's the minimum # buffers needed for the Zig-zag join algorithm (you can ignore the buffers to read the index file):

Zig-zag join is a one-pass algorithm; Alg will join on the smallest values first before moving to next smallest value. To compute the join, we need to read all joining tuples from both one relation into memory buffers and then use 1 buffer to scan tuples in the other relation. However, the zig-zag join algorithm did not specify whether you should (1) "extract" tuples with index key = min value from blocks and store them seperately (i.e., "compact" the tuples). (2) don't extract - i.e.: use the data blocks as is. So the min. buffer requirement will depend on the chosen strategy.
If you do not extract the tuples (use data blocks as is), you will get: If you use R as first relation: # blocks in R with "A = a" = T(R)/100 = 10000/100 = 100 Scan S: need 1 buffer M ≥ 101 If you use S as first relation: # blocks in S with "A = a" = T(S)/100 = 4000/100 = 40 Scan R: need 1 buffer M ≥ 41 Correct answer: M ≥ 41 <------------- Acceptable answer If answer is: M ≥ 101, take off 1 pt
If you extract the joining tuples into a separate buffer, you will get: If you use R as first relation: Use 1 buffer to read tuples from R Extract out the joining tuples with "A=a" and store them: # buffers with tuples "A = a" ~= B(R)/100 = 1000/100 = 10 Scan S: need 1 buffer - you can re-use the buffer for reading R M ≥ 11 If you use S as first relation: Use 1 buffer to read tuples from S Extract out the joining tuples with "A=a" and store them: # buffers with tuples "A = a" ~= B(S)/100 = 200/100 = 2 Scan R: need 1 buffer - you can re-use the buffer for reading S M ≥ 3 Correct answer: M ≥ 3 <------------ Acceptable answer If answer is: M ≥ 11, take off 1 pt

Question 3 (40 pts)

Given the same database:

R = R(A, B, C) // R has attributes A, B, C B(R) = 1000 block T(R) = 10000 tuples V(R, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 10000/100 = 100 for any value a S = S(A, D, E) // S has attributes A, D, E B(S) = 200 block T(S) = 4000 tuples V(R, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 4000/100 = 40 for any value a

What is the # disk I/O operations for the following algorithms:

One-pass join R ⋈ S:

# I/Os = B(R) + B(S) = 1000 + 200 = 1200 blocks

The IO-efficient version of the Two-pass TPMMS based join R ⋈ S:

# I/Os = 3 B(R) + 3 B(S) = 3 ( 1000 + 200 ) = 3600 blocks

The IO-intensive version of the Two-pass TPMMS based join R ⋈ S:

# I/Os = 5 B(R) + 5 B(S) = 5 ( 1000 + 200 ) = 6000 blocks

Two-pass Hashing based join R ⋈ S:

# I/Os = 3 B(R) + 3 B(S) = 3 ( 1000 + 200 ) = 3600 blocks

Three-pass Hashing based join R ⋈ S:

# I/Os = 5 B(R) + 5 B(S) = 5 ( 1000 + 200 ) = 6000 blocks

Block-based nested loop join R ⋈ S using M = 101 buffers :

B(S) # I/Os = B(S) + ------ B(R) M-1 200 = 200 + ----- 1000 100 = 200 + 2000 = 2200 Blocks

Suppose S has a ordered clustering index on its join attribute.

What's the I/O cost of the index join algorithm (you can ignore the I/O cost to read the index file):

You can assume that R is clustered ==================================== B(S) B(S) B(R) + T(R) * ------- (or T(R) + T(R) * ------- if you assumed not clustered) V(S,A) V(S,A) 200 = 1000 + 10000 * ----- = 21000 100 Permissible: B(S) T(R) * ------- V(S,A) 200 = 10000 * ----- = 20000 100

Suppose S has a ordered non-clustering index on its join attribute.

What's the I/O cost of the Index join algorithm (you can ignore the I/O cost to read the index file):

You can assume that R is clustered ==================================== T(S) T(S) B(R) + T(R) * ------- (or T(R) + T(R) * ------- if you assumed not clustered) V(S,A) V(S,A) 4000 = 1000 + 10000 * ----- = 401000 100 Permissible: T(S) T(R) * ------- V(S,A) 4000 = 10000 * ----- = 400000 100

Suppose R and S are not sorted but has a sorted clustering index on their join attribute.

What's the I/O cost of the "zig-zag" join algorithm: (you can ignore the I/O cost to read the index file)

We read R once, with "broad" zig zagging: IO cost = ~ B(R) blocks For each join value t ∈ R, we find tuples in S to join. We will end up reading S once with "broad" zig zagging: IO cost = ~ B(S) blocks (Question 1 illustrates this approximation) Total: Approximately: B(R) + B(S) = 1000 + 200 = 1200 blocks

Suppose R and S are not sorted but has a sorted non-clustering index on their join attribute.
What's the I/O cost of the "zig-zag" join algorithm: (you can ignore the I/O cost to read the index file)