Thursday, March 25, 2010

DECODE/CASE vs. Mapping Tables

I was helping a colleague recently with some SQL. He had about a dozen SQL queries that all looked something like this:

SELECT
(some stuff),
DECODE (status, 'A', 1, 'I', 0),
(more stuff)
FROM
(wherever)

I made a few observations about his DECODE statement:
1. Sometimes he used DECODE and sometimes he used CASE
2. Sometimes he would map ' ' to 0, and sometimes it would go to the default value of NULL, like in this example.

Decode vs Case

First, he asked me whether he should use DECODE or CASE. I remember getting into discussions about that with Doug Burns back in the days when I was more active in the community. Without getting into the whole debate again, let me just say that between the two, CASE is ANSI-standard, newer, easier to maintain, can do more things, and is generally the way to go.

That being said, there's no real difference between DECODE and CASE in this example. I'd just pick one and be consistent.

Use Mapping Tables

My primary suggestion was to consider eliminating the DECODE and CASE altogether. Since several SQL queries were doing the same thing, and accidentally doing so inconsistently, I recommended replacing it with a mapping table. Here's how:

1. Create a table with two columns, one of the same type as "status", the other as a number.

CREATE TABLE mapper (status VARCHAR2(1), val NUMBER(1));

2. Populate the table with all possible "status", and to which number you want it mapped.

INSERT INTO mapper (status, val) VALUES ('A', 1);
INSERT INTO mapper (status, val) VALUES ('I', 0);
INSERT INTO mapper (status, val) VALUES (' ', 0);
commit;

3. It's not always possible to modify the original table to make its status column a foreign key on this one (it might be tied to a vendor application, as it is in this case), but if it is possible, it's worth considering.
4. In the SQL queries, add a join condition on "status" to this new table you created

FROM table1 t1 JOIN mapper m USING (status)

5. Replace the DECODE/CASE statement with the "number" column of this new table.

SELECT m.value

The Pros and Cons

The advantages are that it's very simple, easy to understand, and you know it's consistent across all your queries. Should you ever want to change the mapping, you can easily update that table, and know that all your queries have been updated.

Also, you can more easily take advantage of indexes now (function-based indexes notwithstanding). That's not really applicable in this example, but it may be in other cases.

The disadvantage is having another join. But as Tom Kyte once said "joins are not evil. Databases were born to join." There are reportedly some cases (in Oracle 10, for example) where sometimes adding another table causes the optimizer to make really bad choices, and it really slows down your query, almost as if you've hit a threshold. Chances are that you can address this concern either with patches or by talking with your DBA about some of the optimizer settings. In general, you can fear the reaper, and don't fear joins.

Wrap Up


If you've got several queries that are all using DECODE and/or CASE to map one value to another, consider creating a mapping table and joining that into your queries instead. It will make them easier to maintain, and depending on your indexes it might even speed up some queries.

Comments:
I think a mapping table also makes it much easier to see what happened if there's a problem.
If I see my A converting to B and I think that's wrong, I can go to the mapping table and see it was at least deliberate and not a bit of bad code somewhere.
We will often use mapping tables for historical record as well. If we suddenly decide to change all our As to Bs, we create the mapping table and then we can refer back to it a year later.
Also, depending on the mapping, you may even be able to reverse whatever happened.
 
Robert --

A corollary that I want to bring up. Say you had a table with the following structure:

CREATE TABLE TEST(ID NUMBER, TYPE VARCHAR2(1));

Say the TYPE column is limited to the values 'A', 'B' and 'C'.

Would you prefer a CHECK CONSTRAINT, or a mapping table with a FOREIGN KEY constraint?
 
An excellent point, and an excellent question, thank you both for your comments.

While there are many other relevant factors, ultimately my opinion on a CHECK vs FOREIGN KEY constraint in this situation boils down to two things:
1. Do many tables share this same constraint
2. Does this constraint change often?

Both YES: Foreign key
Both NO: Check
YES/NO or NO/YES: Hmm ... now it might be time to look at those many other factors. Topic for another post maybe...
 
This comment has been removed by a blog administrator.
 
Good stuff. Thanks!

Programmer's and Developer's Forum
 
Thanks for using my site to promote your forum. Where should I send the bill for the advertisement?
 
nice summary on this. Preciate it
 
Thanks for the information u shared.

Oracle Applications
 
sdfsdf dsf sdf sdf sdf sdf sd
 
Other Options: You could use a function to convert the data. Just use the function everywhere. You can also use a view with the function. In Oracle 11g, there are virtual columns which can be functions. Just create the virtual columns using the function.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?