Re: Move ALL Data from 1 Database into Another

On Wed, 13 Nov 2002, Dale_at_DataBee.com wrote:

> Are you sure about the "one round trip" thing.

Turns out you are right about not doing a single round-trip per row. My information was either outdated or apocryphal:

SQL> select value from v$sesstat ss, v$statname sn where sid = 31 and ss.statistic# = sn.statistic# and sn.name = 'SQL*Net roundtrips to/from dblink';

     VALUE

         0

SQL> insert into foobar (select * from foobar_at_baz);

1660073 rows created.

SQL> select value from v$sesstat ss, v$statname sn where sid = 31 and ss.statistic# = sn.statistic# and sn.name = 'SQL*Net roundtrips to/from dblink';

     VALUE

      1925

(Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production)

However, using DB links can never be as efficient as a parallel datafile copy followed by a near-zero downtime switchover.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton




> Hi Jeremiah

> 

> Are you sure about the "one round trip" thing. I may be mistaken but I must

> admit that has not been my experience. Are you thinking of occasions when

> you use a remote table (accessible via a DBLink) in a local join? In such

> cases the optimizer might set up the plan to get the remote rows

> individually rather than doing unproductive remote full or range scans. One

> would anticipate that a query like INSERT into <localtable> (select * from

> remotetable_at_link) would pull things over in a stream. OCI certainly streams

> query results like that - I'm not saying that DBLinks are based on OCI -

> just that there is precedent for this in at least one Oracle networking

> layer.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

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).