3 ms·
Table scans aren't the enemy; sometimes, they're actually cheaper than index scans. Random IO is expensive, especially with spinning platters. It's likely that,
by rosser 17y ago
Table scans aren't the enemy; sometimes, they're actually cheaper than index scans. Random IO is expensive, especially with spinning platters. It's likely that, if you're pulling enough rows off disk, you'll end up in a situation where you're hopping back and forth across the table, seeking left and right, and "head-thrashing". Sometimes it really is cheaper to simply read the entire table and deal with finding the rows you're interested in once it's buffered -- particularly if your disk controller is smart about readahead.
Sure, for simple cases where you're pulling a row or two from the heap, an index scan will be vastly cheaper, but just because they're sometimes better, doesn't make them always better.
- mullr 17y agoBut for those cases where you are just pulling a row or two, this is a nice technique. It hints at something more interesting, I think. Fundamentally it wants to be an assertion against the schema. It would be better to be able to ask the database about the worst-case complexity of a particular query, then write assertions against that. (I don't think this is the same as 'explain', it's more like 'explain as if' with some structure behind it) Something like: assertEqual( O(log(n)), schema.worstCaseComplexityOf('SELECT text FROM response WHERE questionId = 27 AND participantId = 38')) Even better would be a schema language that lets you build in these assertions directly. This is really something you should be thinking about while designing the schema, after all.
- msluyter 17y agoIndeed. Also, a full table scan can take advantage of multi-block I/O, so you really can't rely on grand generalizations. For anyone interested in these topics in an Oracle context, I recommend Tom Kyte's books.