Question

Database

a) Why is a B+ tree suitable for even very large files and indexes?

b) In a B+ tree, is the number of levels fixed, or can it grow dynamically as more data is added?

c) In a B+ tree, is the number of levels proportional to the amount of data, inversely proportional, roughly proportional to the logarithm of the amount of data, proportional to the square root of the amount of data, or what?          Why is this the case?

d) What (qualitatively) is the fan-out ratio of a B+ tree?

e) What factors mainly determine the fan-out ratio?

f) For a given amount of data to be stored (and searched), would increasing the fan-out ratio result in more levels in the tree, fewer levels in the tree, or have no effect the number of levels?   Explain your answer.

g) How, roughly, is a B+ tree different from a binary tree?     Which do you think is “better,” and why?

Problem 7:    (12 Points)   

Poking around in the Oracle system to explore various DBMS structures…

a) Do DESCRIBE on v$BUFFER_POOL.   No need to show that here. Then select BLOCK_SIZE and BUFFERS from v$BUFFER_POOL and show the values of those parameters here:

b) How many buffers are in our pool?

c) What is the total amount of RAM in our buffer pool? Show how you calculate this figure.

d) Express your answer to part c in MB (megabytes). You may use either definition of megabytes—one million bytes, or 1024 * 1024 bytes. Your answer can be approximate.

e) Suppose the buffer pool contains “all the blocks you really want” for your work doing some SQL homework.   Suppose that a giant report or query done by user GODZILLA then comes along and fetches new (and very different) data into about 40,000 buffers.   What will happen to your own precious data in the buffer pool?     Why?

f) In the scenario of part (e) above, would Godzilla make the number of physical disk reads required to do your own precious queries increase, decrease, or remain about the same?   Explain you answer.

Answer 1