Oracle and DECODE- updated

A few words about the DECODE function:

UPDATE is a bad thing to do. It runs slow, slow, slow. While UPDATE cuts the mustard just fine for small datasets, it is a killer for very large datasets being accessed by multiple users. When possible, it is preferrable to use DECODE statements.

A simplified example:
There exists a table, tbl_1. Tbl_1 has columns a,b,c and a column called ELIG_FLAG, that is currently defaulted to ‘0’ (zero). I want to mark as eligable those rows that have a= foo and b= bar. In other words, ELIG_FLAG needs to be set to 1 where a and b match the given criteria. In the bad old days(tm), I would do something like this:

UPDATE tbl_1
set ELIG_FLAG = '1'
where a='foo'
and b='bar'
;

Add more complicated criteria across multiple tables on several 10’s of millions of rows and the run time becomes catastrophic.

A better method is to combine the technique of temp tables with DECODE:

create table my_tbl
as
select a, b, c,
decode(ELIG_FLAG, '1', '0'),
from tbl_1
where a=foo
and b= foo
;

This kills two birds with one stone:
It creates a new temp table called my_tbl that contains only those rows that pass the eligibility criteria and updates the ELIG_FLAG attribute to ‘1’ for those rows. So, in one fell swoop, I reduced the size of the my dataset and updated the value of ELIG_FLAG. And the runtime performance improvement is quite substantial. In the very bad old days(tm), I might have used UPDATE to set the ELIG_FLAG and then used DELETE to remove the rows that didn’t pass muster. Very poor coding practice indeed!

DECODE works like this (in this instance):
decode ( name of attribute, new value, old value)

DECODE is capable of more things, but that is for another day.

Update: 02/01/2005
In this decode example, I am taking the STATE attribute values and transforming them into one of three values, and placing those values in a new attribute called RPT_STATE. It roughly breaks up into this psuedo-code if-else statement:

if STATE = 'UT', then RPT_STATE = 'UT'
else if STATE = 'WA', then RPT_STATE = 'WA'
else RPT_STATE = 'OTHER STATES'

This is a relatively simple example that lets me assign a default for all the values I want to lump together. Here is the table create code that does it:
create table &RPT_4_TBL
nologging
parallel (degree 12)
as
select
decode(STATE, 'UT','UT',
'WA','WA',
'OTHER STATES') as RPT_STATE,
SRVC_CARD_IND,
MKT_OPTIN_IND
from &REPORTING_TBL
;
commit;

This entry was posted in oracle/sqlserver/database. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *