DECODE paper

Over the past few months I’ve occasionally pondered what to do about the DECODE paper on this site.

On the one hand, it was written 6 years ago when DECODE was more prevalent than CASE so it’s out of date.

On the other, it’s popular and is the main reason by far that people visit the site, partly due to its high search engine rankings. The feedback is universally positive and I think it’s the closest I’ve come to what I’d want to achieve in a technical article.

In fact I haven’t just been pondering, I’ve played around with transforming the article to use CASE exclusively but the further I’ve gone with that, the trickier it’s become. Changing the examples was trivial and everything worked as expected. As I’ve started to change the surrounding text, it’s become more of a nightmare. A few of the problems are

1) Some sections are redundant because CASE is more flexible than DECODE. Then, once those sections start to be removed or transformed, surrounding sections don’t make sense.

2) I made an early decision to force Oracle to use the rule based optimiser to make the execution plans more stable and reproducible across versions so readers could play around with the examples and get the same results as shown in the paper. The time that’s passed since the original makes that decision harder to defend.

3) Analytic functions are probably a better approach in some cases. Another way of putting this is that there are more ways to solve the same problem now, so maybe DECODE has become much less useful anyway?

Every place I look, I find a new problem. Yet I wouldn’t say the paper is incorrect as such and is probably still useful when people come across old code that they need to maintain. There certainly seem to be a few Google searches for ‘sql decode examples’.

Maybe the solution is a whole new paper dedicated to CASE, taking a completely different approach from scratch? I think that’s what I’ve decided on. Then, when I think about that paper, I realise that what the original paper was really about was reducing trips to the server to improve performance. Maybe there’s a paper there too.

I realise this might seem a big song and dance to make over one little paper, but I’m keen to do something that works as well for people as the DECODE paper obviously does.

My heart goes out to book authors writing new editions that deal with new versions of Oracle because they have this problem multiplied by 20 or 30 chapters. Imagine what it’s going to be like for Jonathan Lewis keeping his CBO work up to date!

Anyway, I’m going to head off and try to come up with a result. Oh, and if anyone has any views on this, I’m all ears …

Cheers,

Doug

P.S. Don’t even start me on the XML or ADO papers!