TABLE OF CONTENTS 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 parse a query and execute it in the order in which it was written, perhaps doing some reorganization of the query into an equivalent form using some syntax rules. Basically, it is no optimizer at all.
A cost-based optimizer looks at both the query and the statistical data about the database itself before deciding the best way to execute the query. These decisions involve whether to use indexes, whether to use hashing, which tables to bring into main storage, what sorting technique to use, and so forth. Most of the time (but not all!), it will make better decisions than a human programmer would have, simply because it has more information.
