Re: Interesting problem
From: onedbguru <onedbguru_at_yahoo.com>
Date: Fri, 27 May 2011 11:25:38 -0700 (PDT)
Message-ID: <eb90831e-410f-44cd-81c2-0e25fd6d142c_at_dn9g2000vbb.googlegroups.com>
On May 26, 8:18 am, Mladen Gogala <gogala.mla..._at_gmail.com> 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
> > 10.2.0.5).
>
> 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.
>
> --http://mgogala.byethost5.com
Date: Fri, 27 May 2011 11:25:38 -0700 (PDT)
Message-ID: <eb90831e-410f-44cd-81c2-0e25fd6d142c_at_dn9g2000vbb.googlegroups.com>
On May 26, 8:18 am, Mladen Gogala <gogala.mla..._at_gmail.com> 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
> > 10.2.0.5).
>
> 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.
>
> --http://mgogala.byethost5.com
One thing that might be of interest IF you are running 11.2.0.1+ is DBMS_PARALLEL_EXECUTE. Breaks up these sort of bulk DML type operations. Received on Fri May 27 2011 - 13:25:38 CDT