Re: Index not being used

Devinder <devinder_pal.singh_at_pharma.novartis.com> wrote in message news: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.

If you use Rule-based Optimization,
the optimizer does not have information about indexes for the remote tables, ie, Oracle can't use the remote indexes.

There are three ways to improve:

1. Use Cost-based Optimization,
2. Use a View on remote database to represent the local table.
3. As in Oracle 8, you can use DRIVING_SITE hint to force the join to be done

    at a remote site.

You can refer the "Tunning Distributes Queries" section in Oracle8 Server Tunning manual. Received on Mon Aug 02 1999 - 12:58:55 CDT