Re: optimizing in delete-statement

Ralf Beutler wrote:

> 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

I'm sticking my neck out here but Any SQL on 58K rows with the SQL you provided taking even 30 seconds is outrageous. I would be unhappy if the delete took 1 second.

My guess is that you have multiple things wrong. Start with an EXPLAIN PLAN. tRoute needs an index on RouteID and it better show up in the EXPLAIN PLAN. Same goes for type and fkUserID in tUserXOrganizer. If the indexes do not show up in the plan then make sure they exist. And if they do, and it is still taking more than one second, post the plans.

Daniel Morgan Received on Mon Feb 10 2003 - 10:54:09 CST