Re: Interesting problem

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 26 May 2011 12:18:45 +0000 (UTC)
Message-ID: <pan.2011.05.26.12.18.45_at_gmail.com>



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
Received on Thu May 26 2011 - 07:18:45 CDT

Original text of this message