Re: Rollback extents

"Joe Bayer" <u705413818_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1025109462.1245666503@
> That is why I prefer using PL/SQL for inserting.
> Even though it is about 3 times slower than direct insert ... as select,
> it makes rollback segment much easier to handle, and best of all, even
> if it fail, it only roll back a small amount, not the whole process.

If you mean that you prefer to code a commit every n rows eg<pseudo code>

begin
commit_limit number=50;
for x in (select rownum,col_list from tab) update tab set x.col_list = f(col_list); if mod (x.rownum,commit_limit) = 0 then
commit;
end if;
end;

then you are trying to achieve rollback segment too old errors. Moreover in many cases the fact that you only rollback part of the transaction means in the event of failure you are utterly stuffed since you don't know what is and isn't changed.

>
> One other thing i want to point is, if another schema is selecting against
the same table you are inserting, it will be extremely slow,
> because it will look through the huge rollback segment.

I'm entirely lost as to what you mean here. the changing row has a pointer to the original value in the rollback segment. no 'scan' of the rollback segment should ever be done.

--
Niall Litchfield
Oracle DBA
Audit Commission UK