Re: Performance issue while loading large amount of data

"Ban Spam" <ban-spam_at_operamail.com> wrote in message [big snip]
>
> WRT to ROLLBACK in increasing amount of undo generated (generalized)
> SELECT - none because nothing needs to be "undone"
> INSERT - stores the ROWID so the row could be DROPPED upon a ROLLBACK

Er, call me pedantic, but that's a 'DELETED upon a rollback'.

And the above fails to take account of *anything* that Nuno and I have been discussing for days, or which Richard's magisterial post resolved. Namely that 'it's not as simple as that, because there are different sorts of inserts'. Generalizing is fine, but not if it misses the entire point of the (very extensive) thread.

> UPDATE - Stores the changed fields (before the change is applied)
> DELETE - Stores every field (so an INSERT could be done upon a ROLLBACK)
>
> WRT to REDO in increasing amount of redo generated (generalized)
> SELECT - none because nothing needs to be "undone"
> DELETE - stores the ROWID

Totally incorrect. Redo stores both the before AND after images of the data. So a delete stores both the rowid AND the entire row. As does an insert.

> UPDATE - Stores the changed fields (after the change is applied)

Totally incorrect. Redo stores both the before AND after images of the data. So an update causes both the data as it was before the change was applied and the data after the change was applied to be stored.

> INSERT - Stores every field

Totally incorrect. Redo stores both the before AND after images of the data. So an insert stores both the rowid AND the entire row. As does a delete.

> So INSERT, UPDATE, & DELETE inclusive ALL generate both REDO & UNDO;
> each in varying amounts.

Er, yes you could very well say that. Except that that's wrong too. Discrete transactions don't generate any undo. And nologging transactions (such as create index, or insert /* +APPEND */ don't generate any redo.

So, apart from the fact that nearly everything you said was mostly incorrect, not a bad effort.

HJR Received on Wed Jan 15 2003 - 19:29:29 CST