How many block accesses average saved on using secondryindex, Database Management System

Assignment Help:

An un- ordered student file has 20,000 records stored on a disk having the Block size as 1 K. Suppose that each student record is of 100 bytes, the secondary index field is of 8 bytes, and block pointer is also of 8 bytes, find how many block accesses on average may be saved on using secondary index on enrolment number.

Answer:

Number of accesses without using Secondary Index:

Number of records in the file = 20000

Block size = 1024 bytes

Record size = 100 bytes

Number of records per block = integer value of [1024 / 100] = 10

Number of disk blocks acquired by the file = [Number of records / records per block]

= [20000/10] = 2000

 

As the file is un-ordered any search on an average will require about half of the above blocks to be accessed. Thus, average number of block accesses = 1000

Number of accesses with Secondary Index:

Size of an index entry = 8+8 = 16 bytes

Number of index entries that can be stored per block

= integer value of [1024 / 16] = 64

Number of index entries = number of records = 20000

Number of index blocks = ceiling of [20000/ 64] = 320

Number of index block transfers to find the value in index blocks = ceiling of [log2320] = 9

One block move will be needed to get the data records using the index pointer after the needed index value has been located. So total number of block transmits with secondary index = 9 + 1 = 10

Therefore,, the Secondary index would save about 1990 block transmit for the given case. This is a huge saving compared to primary index. Please also compare the size of secondary index to primary index.

Let us now see an illustration of a secondary index that is on an attribute that is not an alternate key.

 

 

 

 

                                269_how many block accesses average saved on using Secondryindex.png


Related Discussions:- How many block accesses average saved on using secondryindex

Determine the advantage of object-orientation, Determine the advantage of o...

Determine the advantage of object-orientation Finally, object-orientation has advantage of continuity throughout analysis, persistent representation and design implementation.

Speedup query processing, Speedup Query Processing: A query that includes d...

Speedup Query Processing: A query that includes data at various sites can be split into sub-queries. These sub- queries can be implemented in parallel by various sites. Such parall

Using sql create a view rs for the relations r and s, Using SQL create a vi...

Using SQL create a view RS for the relations R and S. The view consists of the columns A and D renamed as X and Y in that order. Insert a tuple into it. Show the contents of t

Farm management system, draw er diagram using entities crop,employee,depart...

draw er diagram using entities crop,employee,department,plot,cultivation plan,machinery,seed,fertilizer

Define- relational algebra, Define- relational algebra. The relational ...

Define- relational algebra. The relational algebra is a procedural query language. It having of a set of operations that take one or two relation as input and make a new relati

Define check point and its impact on data base recovery, Define check point...

Define check point and its impact on data base recovery.                  Ans:  There could be hundreds of transactions handled per minute, in a large on-line database system.

Explain weak and strong entity sets, Explain weak and strong entity sets? ...

Explain weak and strong entity sets? Weak and Strong entity sets: A physically powerful entity set has a primary key. All tuples in the group are distinguishable through that k

Define shared locks, Define Shared locks? Ans: Shared lock: Throughout ...

Define Shared locks? Ans: Shared lock: Throughout concurrent execution of transactions, earlier than a transaction can access a data item, it has to obtain a lock on it. Now he

Describe queries with complex types, Describe queries with complex types? ...

Describe queries with complex types? The present extension of the SQL query language deal with the complex type Let us start with the simple example: Find the title and t

What is a foreign key, What is a foreign key, and what is it used for? ...

What is a foreign key, and what is it used for? A foreign key is used to establish relationships between relations in the relational model. Technically, a foreign key is a colu

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd