well look at that.

testing is showing that the index update is ignored -- its not doing it (it optimized it right out of there). another case of conventional wisdom out the window....

So, that (the index) is not a concern -- but the row data in the table, that is, it'll generate gobs of extra redo.

scott@ORA817DEV.US.ORACLE.COM> update emp set empno = empno;

14 rows updated.


Statistics
----------------------------------------------------------
2 recursive calls
20 db block gets
2 consistent gets
0 physical reads
3648 redo size
830 bytes sent via SQL*Net to client
554 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

scott@ORA817DEV.US.ORACLE.COM> update emp set empno = empno, ename = ename, job = job, mgr = mgr, hiredate = hiredate, sal = sal, comm = comm, deptno = deptno;

14 rows updated.


Statistics
----------------------------------------------------------
0 recursive calls
18 db block gets
1 consistent gets
0 physical reads
5104 redo size
845 bytes sent via SQL*Net to client
653 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed

scott@ORA817DEV.US.ORACLE.COM>

So -- nix the index stuff (thanks for making me really look at it) but remember the extra redo generated.

Yes, if you want to selectively update the columns -- tradeoff -- DISK vs CPU+RAM.

Me, I would probably opt for the disk hit personally.

One other comment on this. Will your end users really generate 5.4E11 STATEMENTS (i think not). there will be some small working set that you will generate, it'll be small, it'll be finite. As long as you make the sql in a predicable fashion -- using bind variables -- you'll be just fine.

that will have the effect of updating every column -- greatly increasing the REDO. Yes, you can do it -- but be aware of the consequences.

Well, you don't have to get it from the data dictionary -- you don't have to PRE-GENERATE these, just generate them in a predicable fashion.

You say you pass a map record and change record and build a sql statement based on that. That is fine, you should do something like this (psuedo-ish code, won't necessarily compile)


create package body dyn_update_table_t
as
g_cursor number := dbms_sql.open_cursor;

procedure do_udpate( map_rec in ..., changes in ... )
is
l_stmt long default 'update t set ';

procedure add_set( map_field in number, cname in varchar2 )
is
begin
if ( map_field = 1 ) then
l_stmt := l_stmt || field_name || '= :' || field_name ||',';
end if;
end;
procedure do_bind( map_field in number, cname in varchar2, val in number )
is
begin
if ( map_field = 1 ) then
dbms_sql.bind_variable( ... );
end if;
end;

procedure do_bind( map_field in number, cname in varchar2, val in date )
is
begin
if ( map_field = 1 ) then
dbms_sql.bind_variable( ... );
end if;
end;
procedure do_bind( map_field in number, cname in varchar2, val in varchar2 )
is
begin
if ( map_field = 1 ) then
dbms_sql.bind_variable( ... );
end if;
end;
begin
add_set( map_rec.c1, 'c1' );
add_set( map_rec.c2, 'c2' );
...
add_set( map_rec.cN, 'cN' );

l_stmt := rtrim(l_stmt,',') || ' where pk = :id';
dbms_sql.parse( g_cursor, l_stmt );
add_bind( map_rec.c1, 'c1', changes.c1 );
add_bind( map_rec.c2, 'c2', changes.c2 );
...
add_bind( map_rec.cN, 'cN', changes.cN );

dbms_sql.execute( g_cursor );
end;

end;
/

You won't avoid the soft parse, but you'll use binds, the sqlstmts are generated in a predicable fashion (they will be reused if two people update the same set of columns) and by using session_cached_cursors, you'll avoid much of the soft parse overhead.

Now, if I had the CHOICE, and the amount of redo generated was acceptable -- I would use the static sql suggested by someone else:

update t
set
c1 = decode(map_rec.c1, 1, changes.c1, c1),
c2 = decode(map_rec.c2, 1, changes.c2, C2)
...;



only you can answer 'is the amount of extra redo acceptable'.

It is not any different from what i said:

You will have a finite number of statements -- say a table has an ID and columns
C1, C2, C3. the only updates you should EVER generate are:

update t set c1 = :c1 where id = :id;
update t set c1 = :c2 where id = :id;
update t set c1 = :c3 where id = :id;
update t set c1 = :c1, c2 = :c2 where id = :id;
update t set c1 = :c1, c3 = :c3 where id = :id;
update t set c2 = :c2, c3 = :c3 where id = :id;
update t set c1 = :c1, c2 = :c2, c3 = :c3 where id = :id;


In fact, it is just an IMPLEMENTATION of what I said. It is not an alternative, its the same thing!

It is "different" -- it's a different implementation of the same thing. Another way of doing it -- there must be hundreds of ways to do the same thing.

There is a certain elagance to their approach, a certain readability to the one I laid out. Cursory testing shows a very slight performance edge in the "elegant" approach.

except for the "commit", it would be fine for a small set of columns (quickly explodes into lots of elseif)

CASE would be used in 9i...

(the commit comment is because I firmly believe commit and rollback should have been left out of the plsql language -- it would be a limitation about 0.1% of the times but prevent thousands of bugs in the meantime...)

Ok, imagine the future where the application takes your nice routine to update this row AND an update to some *other row* - but you made your update_row "atomic" all by itself.

Oh no, i cannot reuse your code, because YOUR CODE had the audacity to say "i'm it, i'm the king, you call me, I commit -- anything you do after me is someone elses problem, don't even THINK about reusing me cause I'll mess your transaction up big time"


To me - the CLIENT application (that stateless thing in the middle tier) is the ONLY thing on this planet that *knows*

a) when the transaction is complete
b) what is ok to fail and what is ok to succeed

you, the coder of this little tiny subroutine, have no idea what the requirements will be in 3 years from now.

Maybe I need to:

a) update this address row
b) and modify that audit trail
c) and update this queue to propagate the change back to the mainframe

but if you commit in (a), well, forget about using your code, you are the most unsafe thing possible.


the only thing -- repeat ONLY THING -- that knows "commit should happen here" is the client invoking you. They might be stateless - but they do most certainly have the ability to execute as many of you as they want at a time - in a transaction.

my thought is - 

Oracle already does this, it is the way procedures work.


ops$tkyte@ORA10GR2> create table t ( x int check (x>0) );
 
Table created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          insert into t values ( 1 );
  5          insert into t values ( 2 );
  6          insert into t values ( 3 );
  7          dbms_output.put_line( 'I have inserted 3 rows...' );
  8          insert into t values ( 0 );
  9  end;
 10  /
 
Procedure created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> insert into t values ( 100 );
 
1 row created.
 
ops$tkyte@ORA10GR2> select * from t;
 
         X
----------
       100
 
ops$tkyte@ORA10GR2> exec p;
I have inserted 3 rows...
BEGIN p; END;
 
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C0020965) violated
ORA-06512: at "OPS$TKYTE.P", line 8
ORA-06512: at line 1
 
 
ops$tkyte@ORA10GR2> select * from t;
 
         X
----------
       100
 
ops$tkyte@ORA10GR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA10GR2> select * from t;
 
no rows selected