Test by Bikram | Databases | Test 1 | Question: 14

Question

Let there is a Table named Stud_records  with $3000$ records of fixed length, each record size is $50$ Bytes and records are stored in a block of size $512$ Bytes, If secondary index is built on the key field of size $10$ Bytes and a block pointer of size $5$ Bytes , then the number of first level index blocks are ______

Comments

@Bikram sir, can you please point out mistake in the following approach.

 

Since it is a secondary index, the index file will contain entry for each record in the table. 

No. of entries in the index file = 3000

Each index entry = (10+5) B= 15 B

$\therefore$ size of index file required = 3000*15 = 45000 B

Block size = 512 B

$\therefore$  No. of blocks at 1st level = 45000/512 = 87.89 $\approx$ 88 blocks

 

Answer 1

index record size =10+5=15 B,  no of records /block = 512/50=10 records/block , No of index records/block= 512/15=34,no of index records 3000 as given,  then no of index block= 3000/34=89 blocks

Comments

@Bikram Sir sir it is mentioned whether it is spanned or unSpanned strategy???[is Record of equal size a hint to consider unspanned startegy??] 

A.so if we do by assuming spanned strategy 

 record space 3000

Bs=512

block in first level (3000*15)/512=87.89==88.

B.unspanned strategy

each index=15 byte

no of index in one block=512/15=34.33 i.e 34 records so for 3000 record >3000/34=88.23==89

Am i correct or not???

---

@Abhisek Tiwari 4  catch is "records of fixed length" which is generally used for unspanned category

---

Why block pointer is given in question, I get that the secondary index is necessarily dense but then the record pointer size should be given??? @Bikram sir

Answer 2

If secondary index is built on the key field

Means dense indexing.

Hence, we'll need an index pointer for each record.

 

Total records = 3000.

In 1 block, index pointers possible = $\frac{512}{15}= 34.13=34$ indices are possible.

So, we need $\frac{3000}{34}= 88.2=89$ blocks.

Comments

Why block pointer is given in question, I get that the secondary index is necessarily dense but then the record pointer size should be given??? @JashanArora sir