| Home
Page
Logic-dependent Aggregation (contd) |
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 ;-) ConclusionAlthough 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
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.
|