Re: inline views

From: Daniel Fink <daniel.fink_at_sun.com>
Date: Wed, 16 Jul 2003 07:45:12 -0600
Message-Id: <25937.338011@fatcity.com>

This is a multi-part message in MIME format.

--------------AF28E9DA0FF4120CCDEA2930
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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: VIVEK_SHARMA_at_infosys.com
>
> 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: ListGuru_at_fatcity.com (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).
--------------AF28E9DA0FF4120CCDEA2930
Content-Type: text/x-vcard; charset=us-ascii;  name="daniel.fink.vcf"

Content-Transfer-Encoding: 7bit
Content-Description: Card for Daniel Fink
Content-Disposition: attachment;

 filename="daniel.fink.vcf"

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:daniel.fink_at_sun.com
title:DB Services Lead
x-mozilla-cpt:;-4832 Received on Wed Jul 16 2003 - 08:45:12 CDT