Re: Interesting problem
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.comReceived on Thu May 26 2011 - 07:18:45 CDT