Rick Caminiti dotCOM

I’m trying to understand what the difference is between an Index Seek and an Index Scan. It would seem that a Seek is better than a Scan, but I wanted to verify.

It would appear that an Index Scan has to quickly read every single row in the index to determine which rows qualify. In Oracle, I believe this is comparable to a Full-Index Scan. In most cases, I think this is still better than a full table scan, because index files are not nearly as “fat” as a table would be, typically. So it’s easier and faster to read the entire index that it would be to read the entire table. Once the qualifying rows are determined in the index scan, the SQL engine must then go find those rows in the actual table to get the remaining data for the SELECT portion of the query.

An Index Seek only reads rows in the index that qualify based on the filters in the query. It’s able to determine the qualifying rows in the index immediately, and only read those rows, and then if needed, filter those results further. On the surface, it would seem that an Index Seek is much more optimal than an Index Scan, simply because it’s got less of a footprint; it doesn’t have to read or touch as much of the data.
There are ways to trick the optimizer into using an Index Seek with hints and certain ways of formatting your where clause such as using something like “WHERE PKID” > 0, knowing that the ID is always greater than 0.

I’ve also come to find is that you can use an INCLUDE statement in your INDEX to avoid the trip back to the Table. So if you had an Index seek that found a recordset of 500 rows, it would then normally have to go back to the table and find those 500 rows to get the additional columns from the Table for the SELECT statement. For instance, if you were looking up firstname and lastname on the persontable by personID, the engine would find the personID in the index, then find the row(s) in the table so it would return the firstname and lastname. To eliminate that round trip, you could create an index in PersonID with an INCLUDE on firstname and lastname.