Remote Joins cause poor response times

From: Scott Watson <swatson_at_datachest.com>
Date: Mon, 23 Apr 2001 21:56:56 GMT
Message-ID: <Iy1F6.716878$JT5.19090605@news20.bellglobal.com>

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. Received on Mon Apr 23 2001 - 16:56:56 CDT