In one case, the statement's execution plan used a nested loop, where it read the
local table then probed the index on the remote table. This caused several million
trips across the network to retrieve less than 10,000 records (IIRC).
select local.cola, local.colb, remote.colc, remote.cold
from local,
remote
where local.cola = remote.cola;
We rewrote the query to retrieve all of the data from the remote table then the nested
loops was much more efficient.
select local.cola, local.colb, remote_i.colc, remote_i.cold
from local
(select remote.cola,
remote.colc,
remote.cold
from remote) remote_i
where local.cola = remote_i.cola;
VIVEK_SHARMA wrote:
>
> Daniel, List
>
> Can you give more detail with an EXAMPLE please ?
>
> Thanks
>
> -----Original Message-----
> Sent: Friday, June 06, 2003 7:05 PM
> To: Multiple recipients of list ORACLE-L
>
> I have used an inline view to reduce network traffic when retrieiving
> data from a remote db. Instead of using a nested loop and making
> multiple trips, it made 1 trip and brought over all of the data. The
> query time was reduced from 30 minutes to 5 minutes.
>
> --
> Daniel W. Fink
> http://www.optimaldba.com
>
> VIVEK_SHARMA wrote:
>
> >Where are they advantageous to use & where not ?
> >
> >Thanks
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: VIVEK_SHARMA
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
