Re: optimizing in delete-statement

From: Ralf Beutler <muelleimer_at_brain4.de>
Date: Tue, 11 Feb 2003 18:31:17 +0100
Message-ID: <b2bc16$1a7d9l$1@ID-19598.news.dfncis.de>

DA Morgan schrieb unter anderem:
> Ralf Beutler wrote:
>

>> DA Morgan wrote:
>>
>> > My guess is that you have multiple things wrong. Start with an EXPLAIN PLAN.
>> > .. post the plans.
>>
>> DELETE STATEMENT ()
>>  DELETE ()                     TROUTE
>>   NESTED LOOPS ()
>>    VIEW ()                     VW_NSO_1
>>     SORT (UNIQUE)
>>      TABLE ACCESS (BY INDEX RO TUSERXORGANIZER
>>       INDEX (RANGE SCAN)       I_TUSERXORG_FKUSERID
>>    INDEX (UNIQUE SCAN)         SYS_C001926
>>
>> I can't see something wrong.
>>

>
> This is taking 30 seconds on 58,000 rows? Hmmm.
>
> Is it 58,000 rows in the table or 58,000 rows that are being deleted? What is the
> breakdown between total rows and deleted rows?

I think there are 3 to 5 rows to be deleted in average. 58,000 rows is the total number of rows.

Remark: I installed a system for tests on a WinNT System and I can't reproduce that problem. The datafiles on the unix-System are placed in RAID 5. I thought about rollback segments: there are 12 RBS and file size is 4MB for each. Redo-Log-Groups: 24 and 20MB in size for each.

> Please don't snip as it is hard to remember specific posts when I
> can't see them.

Hmmm. This causes on postings in german newsgroups. I can't post there generating fullquotes. But here's the statement again:

explain plan for
DELETE FROM tRoute
WHERE RouteId IN
(
  SELECT ItemId
  FROM tUserXorganizer
  WHERE Type = :1 AND FKUserId = :2
)
;

br | rb Received on Tue Feb 11 2003 - 11:31:17 CST