Re: optimizing in delete-statement

From: Ralf Beutler <muelleimer_at_brain4.de>
Date: Wed, 12 Feb 2003 11:25:04 +0100
Message-ID: <b2d7e0$1bi61q$1@ID-19598.news.dfncis.de>

DA Morgan schrieb unter anderem:

> Ralf Beutler wrote:
> 

>> 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
>> )
>> ;

>>
> You definitely have a problem but it isn't your SQL statement.

OK. So I'm not so stupid.

> Also I notice that your EXPLAIN PLAN listing didn't contain the cost. What is > it?

O                              OBJECT_NAME                          COST
------------------------------ ------------------------------ ----------
DELETE STATEMENT ()                                                    8
 DELETE ()                     TROUTE
  NESTED LOOPS ()                                                      8
   VIEW ()                     VW_NSO_1                                8
    SORT (UNIQUE)                                                      8
     TABLE ACCESS (BY INDEX RO TUSERXORGANIZER                         4
      INDEX (RANGE SCAN)       I_TUSERXORG_FKUSERID                    3
   INDEX (UNIQUE SCAN)         SYS_C001926

br | rb Received on Wed Feb 12 2003 - 04:25:04 CST