OracleBlog

There is a great debate in the database application development community. I have encountered many developers who prefer putting all their business logic in the application code and using the database only as a glorified file system.

You pick your battles carefully with these developers. I always thought the "line of last defense" was data integrity rules. When I encountered a particularly stubborn developer, I decided that I would focus on convincing him to, at the very least, use his expensive RDBMS to protect his data's integrity (through use of constraints and keys).

However, I have learned that there is actually a whole other battlefield to my rear. I just came across a database application that overrides the optimizer. Every database query this application ever executes includes a forced index.

What do I mean? Why does that bother me? Let me take a short step back to explain what I mean.

Think about when you execute a query to access some data. That data you are requesting could be from several tables, spread all over the disk. The optimizer does many things, among them to choose how every query is executed. That means, for instance, it selects in which order the tables will be joined, and which indexes will be used to access the data.

In the past, presumably when this application was written, Oracle used a rule-based optimizer (RBO), which used a set of rules to determine how to access the data. The RBO did not always make ideal choices. Neither did the early versions of the cost-based optimizer (CBO). That is why many developers took advantage of their ability to override the optimizers and tell it (for example) which join order or indexes to use.

But that's the past. Let's talk about the present.

For several years now, I've never experienced a legitimate need to override the optimizer. Usually re-generating my DBMS_STATS is all I need to do in order to guarantee good choices.

As Jonathan Lewis describes in his latest book Cost-Based Oracle Fundamentals, the CBO has come a long way. You are doing yourself a great disservice when you aren't updating your applications to take advantage of it.

Both his book, and Oracle's Database Performance and Tuning Guide describe the optimizer in more detail, and present the various ways you can leverage its power. I really can't think of any reason why we still need to fight these battles with even the most stubborn of developers.