Wednesday, March 29, 2006


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.

Try this for size: forget tables, indexes, primary keys, foreign keys (don't even mention that optimiser word) and all that other old fashioned nonsense. Why on earth would developers need to know about all that stuff? It's just boring and tedious having to get to know about it.

Instead just redirect your development effort into building applications that hold all the logic and link back to denormalised "tables". Let's call it a "query layer" approach. This way you can save on trivial things like database training and concentrate on all the important stuff like xml and agile programming... it will be ok won't it?!

I don't agree with the approach described above... where did I leave my spreadsheet?

the word verification seems sort of ironic: uiokj (or am I just paranoid?)
I've fought this battle too. It gets frustrating after a while. At least half the time after arguing to put all the business logic in the application, they come back later and ask for it in the database anyway becuase they can't do something or some other application needs the same functionality. Argh!

c'est la vie, I guess.

At least ours are consistent. If they choose to implement some functionality in the app layer, and then need it for another app they will put that functionality in the second app as well. Not the same code obviously it will be rewritten...
This comment has been removed by a blog administrator.
I think you mean 'last line of defense'. Perhaps you are thinking of 'path of least resistance'.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?