Joe Celko's SQL for Smarties, 3rd Edition

Chapter 33. Optimizing SQL

THERE IS NO SET of rules for writing code that will take the best advantage of every query optimizer on every SQL product. The query optimizers depend on the underlying architecture and are simply too different for universal rules; however, we can make some general statements. Just remember that you have to test code. What would improve performance in one SQL implementation might have no effect in another or make the performance worse.

There are two kinds of optimizers: cost-based and rule-based. A rule-based optimizer (such as Oracle before version 7.0) looks at the syntax of the query and plans how to execute the query without considering the size of the tables or the statistical distribution of the data. It will ...