Consider a database implemented using B+ tree for file indexing and in...
Block size = 4 KB
Search key = 12 bytes
Tree pointer = 8 bytes
DB records 1 million = 1000000
B+ tree index each record fits into one block
⇒ Keys given to find levels of B+ tree : Bulk loading B+ tree design we can use
⇒ Order of B tree nodes P × B
p + (P – 1) Keys ≤ Block size
P × 8 + (P – 1)12 ≤ 4096
20P ≤ 4108
P = [4108/20] = 205
⇒ Minimum levels and index: Max fill factor of each node.
⇒ Minimum 4 block access required to access record.
View all questions of this test
Consider a database implemented using B+ tree for file indexing and in...
Solution:
Given:
Size of search key = 12 bytes
Size of tree/disk pointer = 8 bytes
Block size = 4 KB
Number of records = 1 million
To retrieve a record from the database, we need to traverse the B-tree from the root to the leaf node containing the record. The minimum number of disk accesses required to retrieve any record in the database can be calculated as follows:
1. Calculation of B-tree order:
The order of the B-tree can be calculated using the formula:
2 * pointer_size + (block_size - search_key_size) / (search_key_size + pointer_size)
Substituting the given values, we get:
2 * 8 + (4096 - 12) / (12 + 8) = 170
Therefore, the order of the B-tree is 170.
2. Calculation of height of the B-tree:
The height of the B-tree can be calculated using the formula:
log_base_(order/2) (number of records)
Substituting the given values, we get:
log_base_(170/2) (1 million) = log_base_85 (1 million) = 4.39
Therefore, the height of the B-tree is 5.
3. Calculation of minimum number of disk accesses:
To retrieve any record in the database, we need to traverse the B-tree from the root to the leaf node containing the record. Since the B-tree has a height of 5, we need to make 5 disk accesses to retrieve any record in the database. Therefore, the minimum number of disk accesses required to retrieve any record in the database is 4.
Hence, the correct answer is 4.