Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: optimizing in delete-statement

Re: optimizing in delete-statement

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Fri, 07 Feb 2003 11:41:27 -0800
Message-ID: <3E440BE7.A3A9F7A2@exesolutions.com>


Ralf Beutler wrote:

> Hi,
>
> following statement works very slow:
>
> DELETE FROM tRoute
> WHERE RouteId IN
> (
> SELECT ItemId
> FROM tUserXorganizer
> WHERE Type = :1 AND FKUserId = :2
> )
> ;
>
> It performs a full table scan on tRoute.
> IMHO that's why it has to search all RouteId's and then have a look at
> tUserXOrganizer to find them there.
> Is there a better way for deleting on this table without full table
> scan? This statement is used in a procedure.
>
> thx
>
> br | rb

First off "very slow" is about as meaningless a phrase as can be imagined?

How long does it take?
What would be acceptable?
What hardware and operating system?
How many rows in the table?
How many simultaneous users accessing the table, the datafile, the hard disk?

That said ... answers to the following are required before going any further.
What version of Oracle?
Are statistics kept current with DBMS_STATS? Is there an index on ROUTEID in the TROUTE table?

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

Daniel Morgan Received on Fri Feb 07 2003 - 13:41:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US