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

Define seek time, Define seek time. The time for repositioning the arm ...

Define seek time. The time for repositioning the arm is known as the seek time and it enhances with the distance that the arm is known as the seek time.

Define deadlock prevention, Define Deadlock prevention These protocols...

Define Deadlock prevention These protocols make sure that the system will never enter a deadlock state. There are two methods to deadlock prevention. One approach makes sure t

What is serialization and where it is used, What is serialization? Where it...

What is serialization? Where it is used and why?  Serialization is a basic term used for mechanisms which enable objects and object structures to be converted into portable for

Cluster analysis project, (a) Data Mining Process : In the context of t...

(a) Data Mining Process : In the context of this cluster analysis project , and in your own words , explain how you would execute the first stage of data mining, namely the "P

Develop database academic assignment, Develop Database academic assignment ...

Develop Database academic assignment Project Description: For a provided set of instructions in a case study design Entity Relationship Diagram. - Entity Relationship Diag

Cartisian product, Explain the cartesian product in Database Management Sys...

Explain the cartesian product in Database Management System

Explain naivebayessimple algorithem, Use the IRIS dataset that comes with W...

Use the IRIS dataset that comes with Weka to compare the classification performance of the following algorithms. 1. NaiveBayesSimple 2. MultiLayerPerceptron J48 (

Define query optimization, Define query optimization. Query optimizatio...

Define query optimization. Query optimization refers to the process of search the lowest -cost method of evaluating a given query.

Ddl compiler-database management system, DDL Compiler The DDL compile...

DDL Compiler The DDL compiler changes the data definition statements (such as CREATE TABLE .... in SQL) into a set of tables having metadata tables. These tables have infor

What is nested relational model, What is nested relational model? The n...

What is nested relational model? The nested relational model is an extension of relational model in which domains might be either atomic or relation valued.

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