Re: Remote Joins cause poor response times

In article <Iy1F6.716878$JT5.19090605_at_news20.bellglobal.com>, Scott Watson <swatson_at_datachest.com> writes:
>
>
>Is there a more efficient way to get oracle to work with remote joins. My
>query is selecting data form the local table using the primary key (good)
>and using the returned values to join on the remote table using colums of a
>selective index over on the remote side. My problem is I have no idea how
>Oracle is handling the join the card and byte values are extremely high as
>opposed to when I issue the select alone.
>
>Here is the little monster (the remote table is OL_SUBST_NAME)
>order of the tables does not make a difference so driving table is not an
>issue
>the function call has been ruled out ast the culprit as well.
>
> Select DISTINCT DE.SFID, DE.VERSION_TOKEN,
> N.NAME_DESCRIPTION,
> Get_Translated_Text(1, N.SYN_TOKEN,'SYNONYM_TYPES.SYN_TYPE')
> from OL_SUBST_NAMES N,
> QUERY_OL_DATA_ELEMENTS DE
> where DE.QUERY_SUMMARY_NO = 8476 and N.USID = DE.USID AND N.LANG_TOKEN =
>1
>
>If I were to execute
>select a,b,c
> from OL_SUBST_NAMES
>where usid in (x,y,z)
> and lang_token = 1
>
>the results come back very fast but when used in the query above with a join
>as I said it is very slow.
>
>Thanks for providing any insight.
>Scott.
>
>

The odds are that the remote table is being read via a full table scan within a nested loop. You can see this by running an explain plan on the SQL, which should give you a pretty good idea what Oracle is doing and you can see the SQL passed to the remote system in the plan_table.other column.

Where you normally rewrite SQL into a join to tune it, when dealing with a distributed query you often have to convert the join into a sub-query to get it to run well. In your case since you have a driving index, I think you probably should try to perform a coordinated subquery against the remote table for each row returned by the driving query. The join columns on the remote table need to be indexed. This will allow an indexed look-up for each row returned in the driver and if the remote table is of any size will prove superior to a full table scan.

• Mark D. Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net