Index merging is an implementation strategy in an SQL Engine, enhancings query performance when no single index is available to resolve the requirements of a query.
Index merging is really pretty simple. It works a lot like joining two different tables. Since an index is much like a table, it can be utilized in a similar fashion. Let’s use an orders table as an example, having a foreign key to a customer table, and another foreign key to a sales region table. There is no composite index for both the CustomerID and the SalesRegion ID. However, there are individual indexes on both CustomerID and SalesRegionID.
Now you have a query asking for a list of sales order where the CustomerID = 23, and the SalesRegionID = 422. An SQL Engine supporting index merging can take advantage of the independent indexes. It will gather a set of records using the index on CustomerID to resolve the CustomerID = 23 part of the query. It will also execute a query to get the records from the SalesRegionID index based on the clause SalesRegionID = 422. Once both sets are returned, it performs an intersection of the two result sets, returning the pointer to the base table data (in SQL Server the clustered index pointer).
If parallel processing is supported, both of these separate queries may be executed simultaneously, resulting in even better performance. For SQL Server, the query optimizer may not choose to use a merge query if it determines that too many records may be returned from one index or another. It may simply be faster to get the records from one index, and then perform a scan for the other filtering value.
Some database designs attempt to provide the most functionality for index coverage by having a number of single column indexes, rather than having multiple multi-column indexes, which take up more space, and are more complicated to maintain. In some scenarios this works well. Perhaps in an OLTP database it has more value. But index merging requires a lot more work than a composite index tuned for specific queries. You are walking the tightrope of having enough indexes to make your application responsive, and having few enough indexes so that your application remains responsive by not having to maintain too many indexes.
Have you found many smaller indexes to be valuable, and under what conditions? Share your experience here, or by email to btaylor@sswug.org.
Cheers,
Ben