2.2 Indexing

Indexing is another common method for making retrievals faster.

Consider the example of CUSTOMER table used above. The following query is based on Customer's city.

“Retrieve the records of all customers who reside in Delhi”

Here a sequential search on the CUSTOMER table has to be carried out and all records with the value 'Delhi' in the Cust_City field have to be retrieved. The time taken for this operation depends on the number of pages to be accessed. If the records are randomly stored, the page accesses depends on the volume of data. If the records are stored physically together, the number of pages depends on the size of each record also.

If such queries based on Cust_City field are very frequent in the application, steps can be taken to improve the performance of these queries. Creating an Index on Cust_City is one such method. This results in the scenario as shown below.

A new index file is created. The number of records in the index file is same as that of the data file. The index file has two fields in each record. One field contains the value of the Cust_City field and the second contains a pointer to the actual data record in the CUSTOMER table.

Whenever a query based on Cust_City field occurs, a search is carried out on the Index file. Here, it is to be noted that this search will be much faster than a sequential search in the CUSTOMER table, if the records are stored physically together. This is because of the much smaller size of the index record due to which each page will be able to contain more number of records.

When the records with value 'Delhi' in the Cust_City field in the index file are located, the pointer in the second field of the records can be followed to directly retrieve the corresponding CUSTOMER records.

Thus the access involves a Sequential access on the index file and a Direct access on the actual data file.

Retrieval Speed v/s Update Speed : Though indexes help making retrievals faster, they slow down updates on the table since updates on the base table demand update on the index field as well.

It is possible to create an index with multiple fields i.e., index on field combinations. Multiple indexes can also be created on the same table simultaneously though there may be a limit on the maximum number of indexes that can be created on a table.

Q: In which of the following situations will indexes be ineffective ?

a) When the percentage of rows being retrieved is large

b) When the data table is small and the index record is of almost the same size as of the actual data record.

c) In queries involving NULL / Not NULL in the indexed field.

d)All of the above

A: d) All of the above

Q: Can a clustering based on one field and indexing on another field exist on the same table simultaneously ?

A: Yes