Improving SQL efficiency using CASE

Introduction

Some time ago I wrote "The Power of Decode", a paper on using the DECODE function to improve report performance. I was aware at the time that DECODE was being replaced by CASE but wanted to make sure that the paper applied to as many Oracle versions as possible. CASE was introduced in Oracle 8.1.6, however, and is a much better option because it is

1) More flexible than DECODE

2) Easier to read

3) ANSI-compatible (if that matters to you)

However, CASE is essentially a better implementation of DECODE so the reasons for using either are similar. In this article I'll focus on improving application performance by improving the efficiency of your code. One of the first and most valuable lessons I learnt about Oracle performance is to do as much work in as few steps as possible. The Oracle server engine is designed to handle large data sets efficiently but sometimes developers try to break them up into smaller discrete pieces of work (the row-by-row approach). I suspect that they feel they have more control this way and it maps on to a typical developer's procedural approach, but it normally isn't the most efficient way of accessing an Oracle database.

I often see reports developed using reporting tools or by embedding SQL in other languages, that include several SQL statements accessing the same tables in slightly different ways to retrieve individual pieces of data in the report layout. Each of the individual SQL statements is a separate request to the database and causes work at the server end.

To give you a trivial example, why do this?

SELECT deptno, SUM(sal)
FROM emp WHERE deptno = 10
GROUP BY deptno;
SELECT deptno, SUM(sal)
FROM emp WHERE deptno = 20
GROUP BY deptno;

When you could retrieve the same results using this.

SELECT deptno, SUM(sal)
FROM emp WHERE deptno IN (10,20)
GROUP BY deptno;

Any technique that offers the possibility of using fewer SQL statements to achieve the same end result may have a beneficial effect on performance. Analytic functions can be a big help in this area but CASE and DECODE have their place too.