Re: delete without transaction?

In article <8ri1ej$2rc$1_at_nnrp1.deja.com>,   yadiata_at_my-deja.com wrote:
> Hi,
>
> is there a way to delete rows in a table without using any
> rollbacksegment?
>
> If you have a BLOB column with huge data in it, the duration of the
> delete statement for a row is very long due to the generation of
> rollback information.
>
> To reduce the generated rollback information during the regular
> processing you could make an update on the row to mark it as
> deleteable. Later another process could go through the table and
 delete
> all the rows marked for delete.
>
> At this point a transaction is not more needed, because you want to
> remove all the rows which are no more in use. Is it possible to delete
> these rows without a transaction generating rollback information?
>
> I know that deleting a row is a DML statement and that it has to be
> within an transaction. However is there any way to remove the rows
 with
> huge BLOB data in a faster way when a savety transaction is not
 needed?
>
> Thank you.
>
> kind regards
> Yadi Ata
>

1) There is no way to disable Oracle writing change data to the rbs segments
2) LOB datatype changes are not written to the rbs segments, but rather changes to the LOB (original version) are stored in the LOB column itself.

Look at the empty_blob/clob function. Perhaps it you empty the lob column before deleting the row that contains it, the delete will go faster. I have not tried this, but it seems worth a try.

Otehrwise, I would want to make sure the delete really needs to be done. If it is to be followed by another insert then maybe an update to the lob can be devised that will be less expensive.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.