Re: optimizing in delete-statement

Try using an exists clause in your statement. i.e.

delete from tRoute t
 where exist
  ( select 1

      from tUserXorganizer tu
     where t.RouteId = tu.RouteId
       and tu.type = :1
       and tu.fkuserid = :2);

You may also want to check to see if RouteId is part of an index, preferably the lead column. If it's not, your options are to find different criteria to delete so that an index is used, or, build an index on that column. Lastly, if you are deleting most of the rows and using the CBO, Oracle will probably pick a full table scan, in which case you may have to hint the statement with an index.

HTH,
Pete's

Ralf Beutler <muelleimer_at_brain4.de> wrote in message news:<b206rt$1883dc$1_at_ID-19598.news.dfncis.de>...
> 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
Received on Fri Feb 07 2003 - 16:38:38 CST