Re: Query with dblink runs slow

"TurkBear" <jgreco1_at_mn.rr.com> wrote in message news:9ge3kugdh5oadub3c7g7hdbed5g698g0qb_at_4ax.com...
>
> Sorry NetComrade, Remote Database links do NOT use the indexes on the
remote server..
> Check the docs..
> ( at least thru 8.1.x)
>
> andreyNSPAM_at_bookexchange.net (NetComrade) wrote:
>
> >Queries DO use indexes, but require a lot of tuning.
> >Somtimes you have to do a lot of nested queries, but that doesn't
> >always produce the needed results. In most cases we try the nested
> >query to return what's needed from the remote db, and than join it to
> >local tables.
> >Additionally, queries that execute entirely on the remote site, should
> >have the same explain plan as they're on local.
> >
> >explain plan set statement_id='my'
> >for
> ><your query>
> >--the OTHER column shows what gets executed remotedly
> >
> >
> >set linesize 160
> >set pagesize 80
> >set feedback 100
> >--column explain format a40
> >column explain format a80
> >column other format a70
> >set long 600
> >set longchunksize 40
> >column other word_wrapped
> >select id||lpad(' ',2*level) || operation
> >||decode(id,0,'Cost='||position)
> >||' '||object_name as explain, other
> >from plan_table
> >where statement_id='&1'
> >connect by prior id=parent_id
> >start with id=0
> >;
> >delete from plan_table where statement_id='&1';
> >commit
> >/
> >
> >On Thu, 25 Jul 2002 22:27:17 GMT, Daniel Morgan
> ><dmorgan_at_exesolutions.com> wrote:
> >
> >>Bill Wong wrote:
> >>
> >>> I have a query that retrieves data from a secondary database (TEST2)
> >>> via a dblink from the primary database (TEST1) at which I logged in.
> >>> It took 2+ minutes to run.
> >>>
> >>> I copied the query, removing the dblinks and ran it in TEST2. It took
> >>> only 9 seconds.
> >>>
> >>> Any suggestions?
> >>
> >>Slow network connection
> >>DB Link queries don't use indexes
> >>
> >>It is the nature of the beast.
> >>
> >>Daniel Morgan
> >>
> >
> >.......
> >We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> >remove NSPAM to email
>
>
>
> -----------== Posted via Newsfeed.Com - Uncensored Usenet News
==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers
=-----

Sorry again, Remote Database links CAN use the indexes on the remote server, it totally depends on how you structure the WHERE clause.  Check the docs again.

George Barbour. Received on Thu Aug 01 2002 - 03:09:14 CDT