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

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.


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

Posted Date: 3/8/2013 6:09:28 AM | Location : United States

Related Discussions:- How many block accesses average saved on using secondryindex, Assignment Help, Ask Question on How many block accesses average saved on using secondryindex, Get Answer, Expert's Help, How many block accesses average saved on using secondryindex Discussions

Write discussion on How many block accesses average saved on using secondryindex
Your posts are moderated
Related Questions
Define deadlock avoidance. An alternative method for avoiding deadlocks is to needs additional information about how resources are to be requested. Each request requires the s

What is data independence? Data Independence: Techniques which permit data to be changed without affecting the applications in which procedure it. There are two types of data

he SQL query that will return the order number, quantity ordered, price each and total cost of an order (quantity * price each) from the order details table. Label the calculated c

We face problem of stock Opening balance and closing balance in Minus

Unrepeatable reads : Assume T7 reads X twice during its implementation. If it did not update X itself it could be very disturbing to see a dissimilar value of X in its next read. B

Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security l

Problem 1. Describe the Oracle Architecture with a neat labeled diagram, Also explain the individual components of the Architecture. 2. Write about the following Oracle 9i o

ADVANCE OBJECT DESIGN Introduction Analysis is the initial step of the OMT methodology. It is apprehensive with devising a precise, concise, correct and understandable mo

The Concurrent Transactions  Almost every commercial DBMS support multi-user environment. Therefore, allowing multiple transactions to proceed concurrently. The DBMS must make

How does Oracle act as ODBC and give examples of front end uses with ODBC? ODBC achieve portability at the level of the executable by introducing an extra level of indirection.