Re: delete and rollback segs

In article <1992Apr26.213118.22025_at_cnw01.storesys.coles.oz.au> nigel_at_cnw01.storesys.coles.oz.au (Nigel Harwood) writes:
>We are developing an application which does some large deletes on
>tables and are finding that our roll back segments are overflowing.
>
>The simple answer is that we should increase the size of these
>segments to ensure they are large enough for our largest delete.
>
>Unfortunately we don't have enough disk space for that.
>
>Another suggestion was that we have just one big RBS rather than
>five smaller ones. The DBA says this is not a good idea as this
>causes contention.
>
>Anyone have any other ways that they have gotten around this?

Well, there are a two things right off the bat that you could look into.

First of all, how about breaking up the deletes into chunks. For example, if your table contains a date field, perform deletes based on a time window instead of using one command to cover all dates needing to be deleted.

Instead of a single delete statement such as:

	delete from baba where jdate < (select max(jdate)-10 from baba);
	commit;

try something like:

	delete from baba 
	where	jdate < (select max(jdate)-50 from baba);
	commit;
	delete from baba 
	where	jdate < (select max(jdate)-40 from baba);
	commit;
	delete from baba
	where	jdate < (select max(jdate)-30 from baba);
	commit;
	.
	.
	.
	delete from baba
	where	jdate < (select max(jdate)-10 from baba);
	commit;

This sequence of delete statements does (nearly) the same thing yet does not put nearly the same demands on the rollback segs. (PL/SQL is good for things like this). The first query requires substantially more rollback resources to finish than the seond method. By biting off smaller chunks at a time, you place less demands on the rollbacks. The commits here are essential. If you leave them out, you are doing essentially the same as the first query. Why? Because it is considered the same transaction.

Secondly, how about creating and using a single rollback segment designed especially for large transactions? With 6.0.33 (at least for SUN and DEC ULTRIX) you may designate which rollback segment is to be used as opposed to relying on the default "round robin" method. You can create a single large rollback for large processes and several other smaller ones for other processing. Within SQL*Plus (and embedded - I believe) issue the command:

         set transaction use rollback segment r1;

This will force your transaction to use the designated rollback segment "R1". The catch here (isn't there always one?) is that this reserves the rollback segment R1 until the transaction is commited. At that point, the command must be reissued.

This should do the trick.

Regards,

Dale Cooper, DBA				"Oh, Ohhhhhhhhhhhhhhhhhhh"
Center for Seismic Studies				- S. Kinison