Scenario Question 2

[qingbo]

Suppose your DB is OLTP and you have 50 users online. Your DB is in archive log mode and has enough space in the archive directory. You have a table named Table_A. It has 1 million rows.

You execute this:

delete from Table_A;

Can anyone tell me what exactly will happen next to the database? Exactly what will be written to online redo logs?


Now you modify your SQL and make it commit every 5000 rows. Will you have the same amount of redo?

How about commit every single row? Will that be good to the DB?

Qingbo

[marist89]

The pre- image of each and every row will be recorded in the online redo-logs. This will happen regardless if you commit once, once per row, or every 5000 rows. The same amout of redo will be generated.

Your rollback segments, however, are a different story. With one commit, you will need one large rollback segment. Committing every row you will still need a large rollback segment because Oracle needs a consistent view of the data from the start of the statement until the end. Otherwise, you will get a "snapshot too old" message.

[qingbo]

Committing every row you will still need a large rollback segment?
Wouldn't be true that the blocks in RBS be freed after each commit?

Every commit will signal a completion of the current transaction, correct?

[marist89]

I assumed you were opening a cursor on the table and deleting a row at a time by the PK. If you are submitting a delete statement for each row, then no, your rollback segments would not get very big.

[jdorlon]

I think committing every row is a bit overkill. It would be faster to delete every 1000 or so.

You could do it like this :

declare
procedure del_1000 is
begin
delete from mytable where rownum < 1000;
commit;
end;
Begin

del_1000;
while SQL%rowcount > 0 loop
del_1000;
end loop;

end;

[jdorlon]

of course the fastest way is :

TRUNCATE MYTABLE;