| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Remote Joins cause poor response times
Mark, thanks for the answer....
However attached is two statements with plans. I would like to know why when I don't select any columns from the remote table the other column (from explain plan) shows the query I would have hoped was sent to the remote server, but when I include columns it does not.
Thanks,
Scott.
explain plan
set statement_id = 'SCOTT1' for
Select /*+ ordered */ DE.SFID, DE.VERSION_TOKEN, N.NAME_DESCRIPTION,
Get_Translated_Text(1, N.SYN_TOKEN,'SYNONYM_TYPES.SYN_TYPE')
from QUERY_OL_DATA_ELEMENTS DE, OL_SUBST_NAMES N
where DE.QUERY_SUMMARY_NO = 8476 and N.USID = DE.USID AND N.LANG_TOKEN = 1;
Execution Plan
OTHER
INDEX (RANGE SCAN) DATACHEST.QRY_OL_DATA_ELEMENTS_PK ANALYZED Cost=3 Rows=522
PROD SERIAL_FROM_REMOTE Cost=206 Rows=2018033 Bytes=131172145
SELECT "USID","LANG_T
OKEN","SYN_TOKEN","NA
ME_DESCRIPTION" FROM
"OL_SUBST_NAMES" "N"
WHERE "LANG_TOKEN"=1
explain plan
set statement_id = 'SCOTT2' for
Select /*+ ordered */ DE.SFID, DE.VERSION_TOKEN
from QUERY_OL_DATA_ELEMENTS DE, OL_SUBST_NAMES N
where DE.QUERY_SUMMARY_NO = 8476 and N.USID = DE.USID AND N.LANG_TOKEN = 1;
Execution Plan
OTHER
INDEX (RANGE SCAN) DATACHEST.QRY_OL_DATA_ELEMENTS_PK ANALYZED Cost=3 Rows=522
PROD SERIAL_FROM_REMOTE Rows=2018033 Bytes=52468858 SELECT "USID","LANG_T OKEN" FROM "OL_SUBST_ NAMES" "N" WHERE "USI D"=:1 AND "LANG_TOKEN "=1
<xmark.powell_at_eds.com.x> wrote in message
news:9c40e1$sm$1_at_news.netmar.com...
> 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.
>
>
> >
 |
 |