Types of indexes, Database Management System

Assignment Help:

Types Of Indexes

One of the words used during the file organisation is the term index. In this part, let us describe this term in more detail.

We find the index of keywords at the end of every book. Notice that this index is a sorted list of page numbers (address) and keywords (index values) where the keyword can be found. In databases also an index is defined in a same way, as the pair.

The basic benefit of having sorted index pages at the end of the book is that we can situate a desired keyword in the book. We could have used the topic and sub-topic listed in the table of contents, but it is not essential that the given keyword can be found there; also they are not in any sorted series. If a keyword is not even searched in the table of contents then we require to search each of the pages to find the necessary keyword, which is going to be very cumbersome. Therefore, an index at the back of the book helps in locating the needed keyword references very simply in the book.

The same is true for the databases that have very big number of records. A database index permits fast search on the index value in database records. It will be complex to locate an attribute value in a huge database, if index on that value is not given. In such a case the value is to be searched record-by-record in the whole database which is time consuming and cumbersome. It is essential to note here that for a large database the whole records cannot be contain in the main memory at a time, therefore, data needs to be transferred from the secondary storage device which is more time consuming. Therefore, without an index it may be complex to search a database.

An index has a pair consisting of index value and a list of pointers to disk block for the records that have the similar index value. An index has such information for each stored value of index attribute. An index file is extremely small compared to a data file that stores a relation. Also index entries are ordered; so that an index can be seek using an efficient search method like as binary search. In case an index file is extremely large, we can make a multi-level index, that is index on index.

There are many parts of indexes those are categorised as:

Primary index

Single level index

Spare index

Secondary index

Multi-level index

Dense index

Clustering index

 

 

 

A primary index is defined on the attributes in the order of which the file is kept. This field is known as the ordering field. A primary index can be on the primary key of a file. If an index is on attributes other than candidate key fields then various records may be related to one ordering field value. This is known as clustering index. It is to be noted that there can be only single physical ordering field. Therefore, a file can have either the clustering index or primary index, not both. Secondary indexes are definite on the non-ordering fields. Therefore there can be various secondary indexes in a file, but only clustering index or one primary.


Related Discussions:- Types of indexes

Abstract data structures, The C_Artist  application will be developed using...

The C_Artist  application will be developed using the C language (C99 standards) and will be able to compile without warnings and link and run in the Swinburne computer laboratorie

Explain the three data anomalies, What are the three data anomalies that ar...

What are the three data anomalies that are similarly to take place as a result of data redundancy?  Can data redundancy be completely eliminated in database approach?  Why or why n

What are advantages of object-oriented programming paradigm, What is the ma...

What is the major advantage of object-oriented programming paradigm? The ability to modify the explanation of an object without affecting the rest of the system is the main adv

Prepare entity relationship diagram for models your design, This project re...

This project requires you to perform a systems analysis and then design a system solution using database software. Ace Auto Dealers specializes in selling new vehicles from Subaru.

#title., losers with new information systems

losers with new information systems

Query processor in database management system, Query Processor The qu...

Query Processor The query language processor is responsible for taking query language statements or shifting them from the English-like syntax of the query language to a form

Explain the integrity constraints, Explain the integrity constraints: Not N...

Explain the integrity constraints: Not Null,  Unique, Primary Key with an example each. Is the combination 'Not Null, Primary Key' a valid combination. Justify. Not Null - Shou

Explain the two types of participation constraint, Explain the two types of...

Explain the two types of participation constraint.  Total: The participation of an entity set E in a relationship set R is said to be total if each entity in E participates i

Concurrency control, Give the two reasons for allowing concurrency

Give the two reasons for allowing concurrency

Serialisability, Serialisability: Any schedule that makes the similar resul...

Serialisability: Any schedule that makes the similar results as a serial schedule is known as a serialisable schedule. But how can a schedule are determined to be serialisable or n

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