Question

Assume that you have built a dense B+-tree index on SSN, and the B+-tree's leaf nodes contain record ids pointing to data records in data file. Assume 10-byte long. Assume also that you built the tree by using bulk loading so that the nodes at each level were filled up as much as possible. 4. a. b. How many levels does the resulting tree have? For each level of the tree, how many nodes are at that level? How many block I/Os are needed to search for and retrieve a record from the file given its SSN value using the index How many levels would the resulting tree has with all pages 60% full only? c. d. 5. Repeat the above exercise when the B+ tree index is built on NAME. 6. Repeat the exercise when the B+ tree index is built on SSN, and the B+-tree's leaf nodes directly contain data records (there is no separate data file)

Answer 1

Answer:

Answer:

4)

Given: Size of data pointer is 10 bytes

a. We assume each node in B+- tree is of the order of m. SSN being a 9 digit number as a key will take 9 bytes. Hence,

Size of key = 9

Each node in a B+- tree corresponds to a disk block. Let size of the block be B.

Then

m*size_of_pointer+(m-1)*size_of_key <= size_of_disk_block

m*10+(m-1)*9 <= B

10m+9m-9 <=B

19m <=B+9

m <=(B+9)/19

Hence maximum order of B+- tree will be (B+9)/19.

If there are N keys, then a B+- tree of order m can have a maximum of 1+log_(m/2)((N+1)/2) levels.

There are approximate 450+ million SSNs i.e > 4.5e+9 SSNs.

Lets assume N = 4.5*10^9

Hence max level number = 1+log_(m/2)((N+1)/2)

                                    = 1+log_(m/2)((4.5e+9)+1)/2)

                                    = 1+log_(m/2)(2.25e+9)

For a typical block size of 512 bytes, we will have m = (512+9)/19 = 27.42 approx. pointers

So max level number will be 1+log_(27/2)(2.25e+9) = 9.3955638307 = 9 approx.

2. Assuming order m, as calculated is 27.

Then

Level	Nodes	Entries	Pointers
0	1	26	27
1	27	702	729
2	729	511758	511785
3	511785	261910068030	261910068057
4	261910068057	68596883742550799917710	68596883742550799917737
5	68596883742550799917737	4.7055325e+45	4.7055325e+45
6	4.7055325e+45	2.2142036e+91	2.2142036e+91
7
8

Note: From this calculation, it is clear that a B+- tree of height 5-6 will be enough for the exiting numbers of SSNs

c) Since, each node will contain one datablock, there for atleast 2 I/O (one Input and one Output) will be required for search and retrieval.

d) For 60% full tree, a node has approx. 0.60*27 = 16.2 = 16 approx. data pointers. In this case, tree can have a maximum of 11 levels.