Re: optimizing in delete-statement

From: Ralf Beutler <muelleimer_at_brain4.de>
Date: Mon, 10 Feb 2003 09:34:25 +0100
Message-ID: <b27o6i$19r8jn$1@ID-19598.news.dfncis.de>

Hi,

DA Morgan schrieb unter anderem:
> Ralf Beutler wrote:

>> following statement works very slow:
>>
>> DELETE FROM tRoute
>>         WHERE RouteId IN
>>         (
>>           SELECT ItemId
>>           FROM tUserXorganizer
>>           WHERE Type = :1 AND FKUserId = :2
>>         )
>> ;
>>

> First off "very slow" is about as meaningless a phrase as can be imagined?
>
> How long does it take?

7-8 min

> What would be acceptable?

30sec

> What hardware and operating system?

Sun Solaris, SunOS 5.8, datafiles are on a RAID-System (Don't ask me)

> How many rows in the table?

nearly 58000

> How many simultaneous users accessing the table, the datafile, the hard
> disk?

I'm the only one at this time.

> What version of Oracle?

8.1.7

> Are statistics kept current with DBMS_STATS?

yes, every night.

> Is there an index on ROUTEID in the TROUTE table?

RouteId is primary key, using index

> I wouldn't start looking for a better way unless you've fully dealt with
> making what you have work.

but it could be I'm wrong.

I forgot: tRoute contains a column of type blob (LONG RAW), but I'm not searching on this column.
I use the structure of the statement above in some other ways, tables in this cases have nearly 300000 rows: without any problem.

br | rb Received on Mon Feb 10 2003 - 02:34:25 CST