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 encountered the same performance problems and have decided to use
snapshots of the remote tables and create indexes on the snapshot tables

Reference your Oracle7 Server Application Developers Guide Chapter 12
i.e.
create snapshot tableb
pctfree 5 pctused 60
tablespace xyz
storage (initial 50k next 50k octincrease 50)
refresh fast
   start with sysdate
   next sysdate + 1
as select * from tableb_at_dblinkname;

Then create necessary indexes on "SNAP$_TABLEB".
Change the refresh next frequency depending on how static your data is.

Also, do an explain plan on your current query across the db link.  I
have found oracle will not use all the indexes on the remote tables.
Could have been unique to my query.  Worth looking at.

Hope this helps.
=======================================================================
   CCCCC     Todd A. Wood                tawood_at_cobcs1.cummins.com     
 CC          Senior Systems Analyst      Cummins Engine Company, Inc.  
 CC ummins   Corporate CIM Team          500 Jackson Street, MC 60202  
 CC          Manufacturing Engineering   Columbus, IN  47201           
   CCCCC                                 812-377-4637                  
=======================================================================