Re: online redefinition used too much undo segment?

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:gbm202t4btphm9hca9sdnbfion7dn08jp0_at_4ax.com...
> On 25 Feb 2006 22:05:21 -0800, "Zhu Chao" <zhuchao_at_gmail.com> wrote:
>
>>no, I believe to rollback the insert, it just need to log the rowid,
>>and delete that rowid from database.
>>
>>To rollback a delete, it does need to save all the old value.
>>
>>Right?
>
> Nonsense. A rowid is a symbolic representation of the location in the
> database. It is not stored at all, except in indexes. Also blocks are
> rearranged when a row is removed.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Zhu Chao is correct.

Apart form the normal overhead of an undo record, the undo operation for "insert row piece" is "clear the rowindex entry at fileX, blockY, entry Z" i.e.
"here's a rowid, clear the pointer to the row"

FYI - blocks are not re-arranged when a row is removed, the row is marked as deleted, but may be reduced to a stub so that it takes very little space but still holds a rowid entry. Blocks are only re-arranged when it is necessary to coalesce the free space within the block - which may be long after the delete, and long after the next delayed block cleanout.

Zhu Chao - the /*+ append */ allows Oracle to avoid (almost all) undo on the table rows - but if there are any indexes on the table, the updates to the index have to generate undo. However, there are situations where the /*+ append */ hint is not legal and is therefore ignored: are there any triggers on the target table, or has it been defined as the child table in a foreign key relationship ? Neither should be true for the bulk of the online redefinition.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html