{"id":939,"date":"2005-09-16T12:00:00","date_gmt":"2005-09-16T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=939"},"modified":"2005-09-16T12:00:00","modified_gmt":"2005-09-16T12:00:00","slug":"decode-paper","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2005\/09\/16\/decode-paper\/","title":{"rendered":"DECODE paper"},"content":{"rendered":"

Over the past few months I’ve occasionally pondered what to do about the DECODE paper<\/a> on this site.<\/p>\n

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

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.<\/p>\n

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<\/p>\n

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.<\/p>\n

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.<\/p>\n

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?<\/p>\n

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’.<\/p>\n

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<\/span> about was reducing trips to the server to improve performance. Maybe there’s a paper there too.<\/p>\n

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.<\/p>\n

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<\/a> up to date!<\/p>\n

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 …<\/p>\n

Cheers,<\/p>\n

Doug<\/p>\n

P.S. Don’t even start me on the XML or ADO papers!<\/p>\n","protected":false},"excerpt":{"rendered":"

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… Continue reading DECODE paper<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-939","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":932,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/17\/case-article\/","url_meta":{"origin":939,"position":0},"title":"CASE Article","date":"October 17, 2005","format":false,"excerpt":"I've uploaded the MS Word and PDF versions of a new paper about CASE expressions, which is really just a replacement for the earlier DECODE paper. This one has been an unexpected trial, probably because it was a bit of a chore that I knew I had to get round\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1047,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/05\/my-most-common-search-words\/","url_meta":{"origin":939,"position":1},"title":"My Most Common Search Words","date":"August 5, 2006","format":false,"excerpt":"The Top 12.36 15.52% animal porn18 7.76% sql decode6 2.59% decode function examples4 1.72% decode syntax3 1.29% px deq credit3 1.29% saved oracle stats3 1.29% oracle table statistics3 1.29% animal porn .com3 1.29% using code decode tables in oracle3 1.29% oracle restore previous statistics cbo2 0.86% select table_name, num_rows2 0.86%\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":925,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/28\/dbms_xplan-10gr2-autotrace-and-the-case-paper\/","url_meta":{"origin":939,"position":2},"title":"DBMS_XPLAN, 10gR2 Autotrace and the CASE paper","date":"October 28, 2005","format":false,"excerpt":"When I was putting the finishing touches to the CASE paper, Andy Campbell (one of the reviewers) pointed out that he preferred the DBMS_XPLAN output when looking at execution plans for SQL statements. I do too, but I wanted to use Autotrace, partly for consistency with the original DECODE paper,\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1046,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/05\/statcounter-blocking-cookie\/","url_meta":{"origin":939,"position":3},"title":"Statcounter Blocking Cookie","date":"August 5, 2006","format":false,"excerpt":"My website stats have shot up since making the case, decode and px papers accessible again using server side redirection from the old Tripod site. Spot the bit where the redirections were missing \ud83d\ude09In fairness to Pete S, though, I should point out that the number of page hits is\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1032,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/21\/im-no-1-on-msn-search\/","url_meta":{"origin":939,"position":4},"title":"I’m No. 1 on MSN Search!","date":"July 21, 2006","format":false,"excerpt":"For Animal Porn \ud83d\ude41Actually, my stats are looking a lot better since I put the Tripod site back in place with redirection pages to oracledoug.com. A decent percentage of my visits are Google searches for 'SQL decode' and the like, so I'd been missing them ....","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1402,"url":"http:\/\/orcldoug.com\/blog\/2008\/04\/17\/mmon-sampling-ash-data\/","url_meta":{"origin":939,"position":5},"title":"MMON Sampling ASH Data","date":"April 17, 2008","format":false,"excerpt":"When I was working on the course, I noticed this White Paper (PDF) on Oracle 10g Self-Management Framework Internals: Exploring the Automatic Workload Repository. The paper describes the way that MMON selects 1 in 10 of the ASH samples for storage in DBA_HIST_ACTIVE_SESS_HISTORY (I suppose it would be more correct\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/939","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=939"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/939\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=939"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=939"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=939"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}