Re: Local and Remote queries

David Marcus wrote:
>
> Has anyone had any experience with remote tables joined to a local
> table(s).
> eg: tablea local
> tableb remote
>
> select ...
> from tablea a,
> tableb b
> where a=b;
>
> This gives us very poor performance. I've logged TARs in the past, and was
> told by Oracle that the entire remote table is brought back into local memory.
> Can anyone verify this and/or suggest a method around it??? We are
> running 7.1.3.2 on a HP with oracle financials and peoplesoft along with
> some home grown apps. Any help would be appreciated.
> --

I have had similar problems with 7.0.15/16. The solution I found was to use cost based optimisation. When using rule based only, some queries would work while others would not. Using cost based seemed to make a HUGE difference to the performance of some queries. Unfortunately there are still some cases where it still has bad performance.

As an example -

I have a remote table - INVENTORY ~ 10000 rows

When I execute this it takes minutes -

select * from inventory where stock_code in (select '1234' from dual)

If I have a table (DUMMY 1 row) with cost based optimisation in the sub-query it runs instantly -

select * from inventory where stock_code in (select '1234' from dummy)

Hope this helps.

Richard Eichhorn
rme_at_qld.mim.com.au Received on Tue Feb 20 1996 - 00:00:00 CET