what doubts??

what are you looking for exactly? You update a row, we'll fire the update triggers but internally we'll delete the row and insert it

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  enable row movement
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> insert into t values ( to_date( '01-jun-2007', 'dd-mon-yyyy' ), 1, 'hello' );

1 row created.

ops$tkyte%ORA10GR2> create or replace trigger update_trigger
  2  after update on t for each row
  3  begin
  4          dbms_output.put_line( 'old key = ' || to_char( :old.dt, 'dd-mon-yyyy' ) ||
  5                                ' new key = ' || to_char( :new.dt, 'dd-mon-yyyy' ) );
  6          dbms_output.put_line( 'old rowid = ' || rowidtochar( :old.rowid ) ||
  7                                ' new rowid = ' || rowidtochar( :new.rowid ) );
  8  end;
  9  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select 'part1', t.* from t partition(part1) union all select 'part2', t.* from t partition(part2);

'PART DT                 X Y
----- --------- ---------- ------------------------------
part1 01-JUN-07          1 hello

ops$tkyte%ORA10GR2> update t set dt = add_months(dt,12);
old key = 01-jun-2007 new key = 01-jun-2008
old rowid = AAA4GjAAEAAABNEAAA new rowid = AAA4GkAAEAAABNMAAA

1 row updated.

ops$tkyte%ORA10GR2> select 'part1', t.* from t partition(part1) union all select 'part2', t.* from t partition(part2);

'PART DT                 X Y
----- --------- ---------- ------------------------------
part2 01-JUN-08          1 hello

... am not able to see the delete and insert getting
executed, is it possible to see these using some other even tracing?

....

no, you won't- that is why I said "internally", we don't do sql at the lowest level of doing sql - we do calls to C subroutines way down there.


If you want to find out the "cost" of doing a 2 million row partition update - you should benchmark it - in a manner similar to what I did right above to show you the trigger firing. Set up your example, measure cpu, IO, response times, amount of redo generate, undo - etc...


I would probably NOT want to insert 2,000,000 records and then update them all causing them to be internally deleted and reinserted elsewhere. I'd like to insert them with the proper key in the first place if possible (much less work obviously)

define fragmentation for me.

As space is allocated/released in a block, the block will move off/on the freelist. When on the freelist - it will accept new inserts. When not on the freelist, it won't.

If the update that moves a row causes a block to have sufficient free space on it all of a sudden, the block will be on the freelist and will be available for the next insert into the table.

oracle will reuse that space, just like it reuses any space - yes.

There would be no need to shrink unless you never insert (or have rows moved) into that partition again.