Reduce page accesses of query

Question

Assume that we have a secondary index for an attribute in a table and we want to execute a range query on that attribute. Recommend a possible approach to reduce the number of page accesses for this query.

This is from my advanced databases course.

Answer 1

A range query will read from every index page that contains data within the range (plus a couple of non-leaf pages to find the start of the range) so to reduce the number of pages touched you need to make sure there are as many rows squeezed into each page as possible. This can be achieved by simply having the index not cover or include anything except that column.

This is only definitely true if only touching that column though: for queries like SELECT col1 FROM theTable WHERE col1 BETWEEN 4000 AND 9000 or SELECT COUNT(*) FROM theTable WHERE col1 BETWEEN 4000 AND 9000.

When considering anything more complex it is less clear-cut. SELECT col1, col2 FROM theTable WHERE col1 BETWEEN 4000 AND 9000 might be better served in terms of reducing page reads by an index covering col1 and col2 (or one covering col1 and INCLUDEing col2) because otherwise there are extra pages needing to be read to bring in the values from col2.

If you are selecting all, or most of, the columns from the table, and your database engine supports clustered indexes^[1] or similar^[2], then that will be the best way to reduce page accesses for that sort of query. This is an instance where you will deviate from the default practise of having your primary key as the clustering key, as for obvious reasons you can only have one clustered index. If clustering is not supported, or you want the one clustered index on the table for something else, then again you'd need to use a wide covering index or an index that INCLUDEs all the other columns. Though in this latter case be careful you are not wasting space, and affecting write performance, with a wide index that isn't really needed.

^{[1] directly called a clustered index in SQL Server, what Oracle calls an "index organized table" is the same as a table with a clustered index in SQL Server}

^{[2] postgres allows you to reorganize a table index order with the CLUSTER command}