Re: Interesting problem

From: onedbguru <>
Date: Fri, 27 May 2011 11:25:38 -0700 (PDT)
Message-ID: <>

On May 26, 8:18 am, Mladen Gogala <> wrote:
> On Thu, 26 May 2011 00:26:52 -0700, mhoys wrote:
> > That's strange. My initial thought was also "couldn't this be more
> > performant with 2 simple delete statements?". I did a quick test with a
> > table with 1 million records (agreed, nothing big) and a single delete
> > statement was 50% faster than a cursor with bulk operation (database is
> >
> Of course it is. You don't need to execute query twice. There are
> actually two operations involved:
> 1) Implicit query to identify the rows for deletion
> 2) Doing the actual delete.
> The speed-up comes from the fact that the procedure, now written
> correctly as a single loop, will only execute the query once and will
> just keep fetching and deleting pieces. You shouldn't compare against a
> single delete operation, you should compare against a solution based on
> rownum. For that, you will need to execute the same number of queries and
> delete operations. The "bulk delete" procedure will only execute a single
> query, thus the increase in performance.
> > You wrote "it must be done in a piecewise fashion". What is
> > the reason for this?
> I thought that this is obvious? It's because of the UNDO segments. I
> don't want to delete 250,000 records at once, unless I really have to.
> --

One thing that might be of interest IF you are running is DBMS_PARALLEL_EXECUTE. Breaks up these sort of bulk DML type operations. Received on Fri May 27 2011 - 13:25:38 CDT

Original text of this message