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;