Hi Tom,
The SET ROW clause has been available since Oracle 9i and after years of wring endless and database design dependent assignment list, I just love it.
The clause enables us to update a record using a PL/SQL record and very efficient syntax like:
UPDATE table_name
SET ROW = plsql_record
WHERE ...
Unfortunately the target table's
primary key is always updated whenever a record is updated, but most of the time the primary key has not changed.
This sometimes dramatically decreases the performance of an update statement and can also cause some significant locking issues when updating a table that is referenced by many other tables.
1) Did you find a way to get around this important disadvantage?
2) Would you rather suggest not to use this PL/SQL "feature?
3) Do you know of something in the Oracle pipeline addressing this problem?
Thank you for all your help and keep up the good work,
Dieter
... Unfortunately the target table's primary key is always updated ...
that is false, ALL of the columns in the record are updated (have to be, think about it - there is nothing 'special' about a primary key that says 'do not update me' besides common sense).
It is only a looking issue if you have UNINDEXED foreign keys and update the parent records primary key (that I believe would be the cause for most any and all noticeable performance issues - since we skip the index maintenance if you do not modify the indexed column).
I'm not a fan of this set row - it is an idea that sounds good but when you peel back the layers - it is not.
It updates by position - not by name - so I've seen some tricky to debug situations whereby a record is selected from table T1 and applied to table T2 - and the columns were just a bit out of order, eg:
ops$tkyte%ORA9IR2> create table t1 ( x int primary key, a int, b int );
Table created.
ops$tkyte%ORA9IR2> create table t2 ( x int primary key, b int, a int );
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t1 values ( 1, 2, 3 );
1 row created.
ops$tkyte%ORA9IR2> insert into t2 values ( 1, null, null );
1 row created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
2 l_rec t1%rowtype;
3 begin
4 select * into l_rec from t1;
5 update t2 set row = l_rec where x = l_rec.x;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select 't1', x, a, b from t1
2 union all
3 select 't2', x, a, b from t2;
'T X A B
-- ---------- ---------- ----------
t1 1 2 3
t2 1 3 2
I would much rather "pay the price" of listing, in order, the columns I intend and need to update - rather than this syntactic sugar that has been the cause of no little confusion.
I don't mind typing a bit today - in order to forgo really bad accidental side effects (eg: bugs) tomorrow.
I'm not a huge fan of this feature anymore - I wrote about it when it first came out, but experience has caused me to conclude we'd be better off without it.
Just like autonomous transactions - they seemed like such a good idea at the time...