Week 4
Database Administration
IT-404
Indexing in Databases
Indexing improves database performance by minimizing the number of disc visits required to
fulfill a query. It is a data structure technique used to locate and quickly access data in
databases. Several database fields are used to generate indexes. The main key or candidate
key of the table is duplicated in the first column, which is the Search key. To speed up data
retrieval, the values are also kept in sorted order. It should be highlighted that sorting the
data is not required. The second column is the Data Reference or Pointer which contains a
set of pointers holding the address of the disk block where that particular key value can be
found.
Structure of Index in Database
Attributes of Indexing
Access Types: This refers to the type of access such as value-based search, range access, etc.
Access Time: It refers to the time needed to find a particular data element or set of
elements.
Insertion Time: It refers to the time taken to find the appropriate space and insert new data.
, Deletion Time: Time taken to find an item and delete it as well as update the index
structure.
Space Overhead: It refers to the additional space required by the index.
Structure of Index in Database
In general, there are two types of file organization mechanisms that are followed by the
indexing methods to store the data:
Sequential File Organization or Ordered Index File
In this, the indices are based on a sorted ordering of the values. These are generally fast and
a more traditional type of storing mechanism. These Ordered or Sequential file organizations
might store the data in a dense or sparse format.
Dense Index
For every search key value in the data file, there is an index record.
This record contains the search key and also a reference to the first data record with
that search key value.
Database Administration
IT-404
Indexing in Databases
Indexing improves database performance by minimizing the number of disc visits required to
fulfill a query. It is a data structure technique used to locate and quickly access data in
databases. Several database fields are used to generate indexes. The main key or candidate
key of the table is duplicated in the first column, which is the Search key. To speed up data
retrieval, the values are also kept in sorted order. It should be highlighted that sorting the
data is not required. The second column is the Data Reference or Pointer which contains a
set of pointers holding the address of the disk block where that particular key value can be
found.
Structure of Index in Database
Attributes of Indexing
Access Types: This refers to the type of access such as value-based search, range access, etc.
Access Time: It refers to the time needed to find a particular data element or set of
elements.
Insertion Time: It refers to the time taken to find the appropriate space and insert new data.
, Deletion Time: Time taken to find an item and delete it as well as update the index
structure.
Space Overhead: It refers to the additional space required by the index.
Structure of Index in Database
In general, there are two types of file organization mechanisms that are followed by the
indexing methods to store the data:
Sequential File Organization or Ordered Index File
In this, the indices are based on a sorted ordering of the values. These are generally fast and
a more traditional type of storing mechanism. These Ordered or Sequential file organizations
might store the data in a dense or sparse format.
Dense Index
For every search key value in the data file, there is an index record.
This record contains the search key and also a reference to the first data record with
that search key value.
