Home Page

Back to Papers



Basic Usage

Logic-dependent Aggregation

An Interlude

Logic-dependent Aggregation (contd)

Pivot Tables and Multi-part Logic

Beyond Equality


Additional Resources

Please note this page has moved to http://oracledoug.com/case9.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)


Although this paper only shows a few simple examples, it should be clear that CASE expressions are a powerful tool when developing complex reports that perform efficiently. There are no practical limits on the complexity of the conditions you can test.

The Down Side

Before we summarise the strengths of CASE expressions, let's focus on some of the potential weaknesses if we don't use them appropriately. Most of these are related to coding style and are therefore under our control. The first is that of code readability. Even a simple query such as Example 3c can be a little difficult to take in at first. The longer queries that you’re likely to come across in business applications can become difficult to understand or maintain. The best approach is to develop clear coding standards from the start, which should include some form of alignment of indentations to make the individual components of the CASE expressions very clear.

The second, which I mentioned earlier, is that CASE is a post-retrieval function and it is easy to write code which is spectacularly inefficient but functionally correct. Remember the golden rule

Use the WHERE clause to eliminate all unnecessary data first and then use CASE for additional processing.

The Up Side

CASE expressions give us the power to not just retune the access paths of our SQL queries, but to take a step back from our code, look at the requirement and take a completely different approach to the task. Instead of limiting our tuning efforts to improving the speed of individual queries by investigating access paths and join methods, we can reduce the overall number of queries to retrieve all the data we require and then use CASE to perform certain post-retrieval tasks. This reminds me of one of the first pieces of Oracle tuning advice I heard, which still holds true today. Reduce the number of 'trips' to the database to the minimum required to achieve the objective. If a report is performing multiple accesses against the same tables it is worth examining whether these might be combined.

CASE bridges the gap between pure SQL and embedding SQL in 3GLs. In some cases, the only reason that we use a 3GL is to perform cursor loops that allow us to apply additional conditional processing to the data, row by row, based on the column values. We can often use CASE to perform that additional processing instead, using more efficient set-based SQL.

CASE works with all modern versions of Oracle and isn't dependent on optimiser improvements in newer versions. This is because the performance advantage comes from taking a different approach to the problem that requires Oracle to perform less work, regardless of which optimiser is in use. The way I see it, the optimiser can only really be expected to optimise your access paths, not attempt to rewrite your algorithm more efficiently (although I'm sure this will happen in time). Like most performance tuning activities, the big improvements come from making smart decisions about your approach before you begin work.



Technical Papers Utilities and Scripts Book Reviews Links
My ResumeFun & GamesEmailHome