DATABASE INDEXING

1) Index is a table or a data structure, it is maintained to determine the location of rows in a file that satisfied to a particulate condition.

2) Database index is same as book index.

3) Index table has entry consisting of value of the key attribute for a particular record and the pointer to the location.

4) There are mainly two types of indexing that are used in database

i) Ordered Index
ii) Hashed Index

Ordered Index

It is based on stored order of the values of records.It is sorted on a search key values.
Ex. Authors , Catalog in library .

If any index search key specifies the same order as the sequential order of the file it is called as primary index. A primary index is an orders file whose record are fixed length with two fields.

The first field is same data type of order key field of the data file and second field is pointer to the address block.

Ordered Indexing are two types

1) Dense Indexing
2) Sparse Indexing.

1) Dense Indexing

i) Dense Indexing an index record or entry appears for every search-key value in the file.

ii) Index record contains the search -key value and a pointer to the first data record with the search key value.

iii) The rest of the records with the same search key value are stored sequentially after the first record.

iv) Record contains search key value and a pointer to the actual record.

v) To locate a record, index entry is found with the search key value then record pointed by the index entry and follow the pointers in the file until the desired record is found.

vii) Dense index is faster but it required large space as compared sparse.

Fig: Dense Indexing

2) Sparse Indexing

i) In this index record is created only for some search-key values.

ii) In this index record contains a search-key value and a pointer to the first data record with that search-key value for their desired record.

iii) To locate a record, the index entry with largest search-key value that is less than or equal to search key value for the desired record, is found.

iv)The search start from the record pointed by the index entry and follow the pointer in the file until the desired record is located.

v) Index records are created for some of the records in the file .

Fig: Sparse Index

Difference between Dense Index and Sparse Index

Milan Tomic

Hi. I’m Designer of Blog Magic. I’m CEO/Founder of ThemeXpose. I’m Creative Art Director, Web Designer, UI/UX Designer, Interaction Designer, Industrial Designer, Web Developer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Photographer. Inspired to make things looks better.