In any database application, for a table there exists both the Primary Key Indexes and Secondary Indexes.

This is a common prescription by normal basis consultant to sort a table according to the index. This sorting is actually aimed to reduce the number of buffers. Now when one is sorting the according to the primary key index fields, it is fine, most of the modules accessing the table will get benefit out of it.

But consider the case, of sorting the table according to one of the available secondary indices, will definitely give a boost to the module which requires the secondary index for fetching records, but it will create problem for many other existing modules which fetch records either in the order on the primary index or in the order of other secondary indices.

There is a common tendency of expert basis advising you to sort one of the underlying table according to the secondary index for giving  a facelift to a particular transaction / program but it can prove fatal as a whole.

Secondly, remember sorting of table is not a permanent cure, because after a considerable amount of transaction your table may need re-sort.

Permannent cure is to rectify either program code or program logic.

Advertisements