Not sure whether to call this Query Tuning or Index Tuning, anyway, all that matters is we get improved performance. Life is just so easy when you have to deal with single-column indexes. They are lovable. In contrast, multi-column indexes bring so many complexities along with them. Let’s dig a bit deeper. In a multi-column index (col1, col2), the data is sorted by the first column. So if you try to order by col2, the optimizer will deploy a SORT operator to sort the data. And of course, if you are ordering by Col1, that data is pre-sorted and the SORT operator is not needed. In a multi-column index, the order of columns is important. Col1, Col2 is not the same as Col2, Col1. Let’s say the structure is Col1, Col2. Now if both Col1 & Col2 are used as predicates (they are in your WHERE clause), the engine can seek on Col2 only if it can seek on Col1. Yes, that’s a condition and a rule of the optimizer, so to say. If you omit Col1 from your query, you cannot seek on Col2. The optimizer will resort to an Index Scan, not Index Seek. Also, the equality operator plays a critical role here. If both the columns are in the WHERE clause, the optimizer can seek on Col2 only if Col1 is using the equality comparison operator. So there is a concept of partial seek vs full seek. Unfortunately, the execution plan does not depict this clearly. So if the optimizer cannot seek on a column, it is called a residual predicate. In this video you will see how that importance of column order in an index, especially the SORT mechanism.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.