Re: Index not being used

It is possibly a reasonable thing for Oracle to do in this case. There are basically two execution paths:

    Fetch each row of the remote table, and for each     row check back to the local table - which might     be very expensive on network traffic

    Convert the existence subquery to an IN subquery,     scan the local table, send all case_ids to the remote     site en masse, and delete all the rows there for     which an incoming case_id exists, and return only     the result set.

You don't say how the remote table is handled when the local table is scanned, though, so I could be way off track.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Devinder wrote in message <37A0C19D.C56C040_at_pharma.novartis.com>...
>Dear all,
>
>I am running this query
>
>delete from case_master_at_bs04 a where exist (select 'x' from case_product
>b where b.case_id = a.case_id);
>
>ie table a is remote and table b is local
>
>When I do an explian plan it shows a full tablescan for case_product ie
>my local table and if I create the case_master table locally then it
>starts using the index for table b. Can somebody explain that or some
>other way to format this quiery except creating snapshots or creating a
>local table.
Received on Thu Jul 29 1999 - 16:45:36 CDT