I'm fully aware of that, which is why I said, Depending on what your query does... In fact not only am I aware of it as a theoretical point, but within the last month I sped a query up from taking around an hour to 3 minutes by providing a hint that, among other things, convinced it to do a full table scan on a large table.

However normally, particularly with straightforward queries, indexes are good. The simplest case of all would be that he's querying a large table and doing something like looking for the handful of items in a particular status. If it really is a handful of items that would be returned, then adding an index would certainly be a huge win.