Introduction

Definitions

Basic Usage

Logic-driven Column Calculations

Mind the Trap!

Beyond Equality

Multi-part Logic

User-definable Report Ordering

A Real-world Example

The Down-side

The Up-side

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

Note - this document discusses the use of the DECODE function to develop more efficient SQL queries. However, if you're using Oracle 8.1.6 or greater, you should really be using the CASE function instead which is more flexible and results in more readable code. The CASE equivalent of this document can be accessed here.

Introduction

This paper is about one of the functions in Oracle's implementation of SQL. Doesn't sound too exciting, does it? Except that DECODE() is no ordinary function, but a core programming construct which adds enormous power to SQL. It allows us to include a small, but significant, amount of post-retrieval procedural logic into our SQL code, without the need to develop PL/SQL or use another 3GL. Over many years across a variety of Oracle versions, using a large number of different client tools, it hasn't changed. That's what I call core. I'm unhappy to report, though, that at almost every company I've worked for (including software houses), I've had to introduce people to the techniques outlined in this document.

But I'm not a programmer any more and functions, expressions and syntax diagrams bore me, so why take such an interest in a programming technique? For the same reasons that every DBA ends up becoming a part-time SQL consultant - Performance. There are some situations where, short of re-development using a 3GL, DECODE is simply the only way to make a complex report run quickly enough.

This paper will explain how to use DECODE to write extremely powerful individual SQL statements which perform one pass of the relevant data and then break it up and format it to suit your reports, instead of using multiple separate SQL statements. The performance improvements are near linear in many cases. By this I mean you can combine 10 queries, each of which performs a full table scan, into one query which will perform one full table scan and the single query will take about the same length of time as one of the original 10 queries. I've done this many times, it works fantastically well and yet I'm surprised how rarely I see the technique used. When I was preparing this paper, a reviewer asked the question 'So, why isn't it used more often?'. I don't have an answer to that question yet, but I hope this paper will help to improve the situation.

Remember those boring syntax diagrams I mentioned? Well, sometimes there is no substitute for technical detail, so let's get that bit out of the way first.