Yes, Virginia, full table scans are bad

I hate being whacked on the head with Tom Kyte. Some coworkers - at multiple companies - are fond of telling me that full table scans aren't a problem because Tom Kyte says they aren't. What is in his head??? Well, Tom - if you're out there - you're probably thinking about how so many of these "one-size-fits-all" dogmas can put development or administration in a strait-jacket. So what's my problem?  The fact is, over years of intensive database application development, I have met very few full table scans I would care to put into production software.  What does that mean?  Prohibit them?  No. Look, in any decent size database application you have to look for trouble before it looks for you - and finds you.  Full table scans are trouble that hunts you down and hurts you badly. Even if you don't 'prohibit' FTSes, you must audit them.  Like you do all of your application and your database.  Some FTSes don't grow beyond linear complexity.  (Whew!)  But when you find them inside a nested loop or a hash join, well, God help us. There is no "one-size-fits-all" that will allow you to skip the details.  Scrutinize things, and since FTSes can be so disastrous for performance you must scrutinize them. When do you really allow an FTS? Well, frankly, I don't ever like them.  I try very hard to eliminate them in favor of some range scan or skip scan.  Why?  FTSes are brittle.  They usually indicate you either didn't think through the query or that you don't care about what happens if something breaks in the middle.  Diagnosing a bum query with an FTS is like finding a needle in a haystack.  You can't confirm the query means what you meant if the optimizer pulls an FTS any way nearly as clearly as you can if it follows an index. Never say never, but - sorry Tom - full table scans really, really are bad.