Was this helpful?
Two Secondary Indexes
There is no reason for having two secondary indexes on the same column, for example, one hash and one ISAM. Instead, use the index giving you the most versatile access path because the overhead of maintaining and using two indexes is more than the disk I/O saved for a few queries.
If you need two access paths, and you want one to be hash and the other to be ISAM or B-tree, you can use ISAM (or B-tree) for the base table access method and hash for the index. ISAM and B-tree cluster similar data on the same data page, while hash randomizes data, so that ranges of values are not clustered. With the base table as ISAM or B-tree, range retrievals find the physical rows clustered on the same data pages, reducing the amount of disk I/O needed to execute range queries. If the base table is hash, the ISAM index points to the qualifying rows, but these rows are spread randomly about the table instead of being clustered on the same data pages.
Last modified date: 01/30/2023