Posted by One of the pieces of advice that I often see on the ‘net is that undo space is somehow this incredibly precious thing, and as a consequence, one should always keep the amount of uncommitted changes in the database to a small size.
Personally I think that is baloney (Ed-in reality, as an Australian I have a slightly more powerful choice of term, but lets keep things PG-rated 🙂). But when I recently challenged someone about their assertion, they provided the following test case as proof that we should always perform large changes in small batches.
SQL> create table t as
2 select rownum x1,rownum x2,rownum x3,mod(rownum,2) x4
3 from
4 ( select 1 from dual connect by level <= 1000),
5 ( select 1 from dual connect by level <= 1000);
Table created.
SQL> set timing on
SQL> delete from t;
1000000 rows deleted.
Elapsed: 00:00:01.34
SQL> set timing off
SQL> commit;
Commit complete.
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t as
2 select rownum x1,rownum x2,rownum x3,mod(rownum,2) x4
3 from
4 ( select 1 from dual connect by level <= 1000),
5 ( select 1 from dual connect by level <= 1000);
Table created.
SQL> set timing on
SQL> begin
2 loop
3 delete from t where rownum <= 1000;
4 exit when sql%notfound;
5 commit;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.59
SQL> set timing off
So, whilst the looping construct is a little slower to run, we have still got the task done in just a few seconds, and think of all those great undo savings we must have got!
But here is the key thing. When you make a claim that something is “best practice” in all cases, then providing a single case does not meet the definition of proof. Quite the opposite in fact, in that, only a single counter-example if needed in order to disprove the claim. So let us continue exploring this example. Firstly, let us add some more realism to the demo, by making it larger (after all, why bother deleting in a loop unless you have a lot of data to delete), and adding some indexes to the table.
SQL> create table t as
2 select rownum x1,rownum x2,rownum x3,mod(rownum,2) x4
3 from
4 ( select 1 from dual connect by level <= 2000),
5 ( select 1 from dual connect by level <= 2000);
Table created.
SQL> create index t_ix1 on t ( x2 );
Index created.
SQL> create index t_ix2 on t ( x3 );
Index created.
SQL>
SQL> set timing on
SQL> delete from t where x1 <= 2000000;
2000000 rows deleted.
Elapsed: 00:00:20.06
SQL> set timing off
SQL> commit;
Commit complete.
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t as
2 select rownum x1,rownum x2,rownum x3,mod(rownum,2) x4
3 from
4 ( select 1 from dual connect by level <= 2000),
5 ( select 1 from dual connect by level <= 2000);
Table created.
SQL>
SQL> create index t_ix1 on t ( x2 );
Index created.
SQL> create index t_ix2 on t ( x3 );
Index created.
SQL>
SQL> set timing on
SQL> begin
2 loop
3 delete from t where x1 <= 2000000 and rownum <= 1000;
4 exit when sql%notfound;
5 commit;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:41.66
SQL> set timing off
SQL>
So now it is twice as slow, but the timings are still pretty good. If I can wait 20 seconds, I can probably wait 40 and not really notice the difference. But still we’re using a special case there because we are cleaning out blocks completely, and there are some optimizations the database can use to see that a block is completely empty, and hence not have to dig into the rows in the block. Let us add some more realism by deleting rows that match a particular criteria.
SQL> create table t as
2 select rownum x1,rownum x2,rownum x3,mod(rownum,2) x4
3 from
4 ( select 1 from dual connect by level <= 2000),
5 ( select 1 from dual connect by level <= 2000);
Table created.
SQL>
SQL> create index t_ix1 on t ( x2 );
Index created.
SQL> create index t_ix2 on t ( x3 );
Index created.
SQL>
SQL> set timing on
SQL> delete from t where x4=0;
2000000 rows deleted.
Elapsed: 00:00:22.71
SQL> set timing off
SQL> commit;
Commit complete.
SQL>
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t as
2 select rownum x1,rownum x2,rownum x3,mod(rownum,2) x4
3 from
4 ( select 1 from dual connect by level <= 2000),
5 ( select 1 from dual connect by level <= 2000);
Table created.
SQL>
SQL> create index t_ix1 on t ( x2 );
Index created.
SQL> create index t_ix2 on t ( x3 );
Index created.
SQL>
SQL> set timing on
SQL> begin
2 loop
3 delete from t where x4=0 and rownum <= 1000;
4 exit when sql%notfound;
5 commit;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:28.69
SQL> set timing off
SQL>
Now we are nearly 6 times slower. Maybe that is still fine, maybe it is not. 20 seconds to 120 seconds could be acceptable, but 20 hours to 120 hours might be a disaster. Remember that proof means “for all cases”, where counter-example simply means “find one case where it is NOT the case”
So by all means, use either single-delete or iterations of delete, for whatever best suits your needs…but don’t claim that one is always better than the other.
Learning is not a spectator sport 
It would be good to also actually measure the amount of undo generated in each case..
But….why? I’ve never really understood why the undo amount is a metric to be concerned about *unless* you are running out of it. In which case, the operation consuming lots of undo is either
a) a one-off, never to be repeated exercise – so you would probably not be making an alteration to your undo (perhaps temporarily add a new undo tablespace to be dropped later)
b) a regular operation, so you would increase your undo accordingly and leave it at that size.
In this case, to indicate that overall undo generation would actually be higher than would be the case with a single delete. Also, when doing this for real, one would want to plan for sufficient undo, since you definitely don’t want that delete to rollback. Also, one would want to cater for related impacts, e.g. additional redo related to this undo, which will result in e.g. additional traffic to a dataguard database, etc.
Hi Conner,
aside from the proof issue I think you actually be telling people that frequent commits are a bad idea as outlined by Tom way back in 2002:-
” frequent commits — there is NO resource to free up — undo is undo, big old circular buffer” (https://asktom.oracle.com/pls/apex/asktom.search?tag=issue-frequent-commit-statements).
Thanks
Andy