Getting (large) data via db link
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Getting (large) data via db link

  1. #1
    Join Date
    Jan 2002
    Posts
    16
    I need to query 3.5 million rows from remote database on a regular basis. Query will take subset of columns with or without condition.
    I am still not sure the way Oracle will behave when data are queried across db link: will all rows with all columns be moved, or will it be transfer all rows with requested subset of columns, or ...?
    I did notice speed up if querying a view on remote database comparing with executing full statement locally.
    Any advice on what could be the most efficient way of transferring large amount of data via db link.

    Or maybe instead of using db link, I do spool and load data !?
    Share on Google+

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Oracle locates all queried data in the buffer of remote database and returns to you only those fields which you specified in 'SELECT' clause.
    You may use COPY command to transfer data.
    Best wishes!
    Dmitri
    Share on Google+

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    If you're doing a simple select statement with where conditions, the query will be processed at the remote site and just the rows that satisfy the where clause will be returned. However, if you are joining a local and remote table, the query will be performed locally.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."
    Share on Google+

  4. #4
    Join Date
    Jan 2002
    Posts
    16
    Originally posted by kgb

    You may use COPY command to transfer data.
    What COPY command?

    Share on Google+

  5. #5
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    Consider setting up a snapshot to be built during the off hours. Your query can then run locally against the snapshot, urh, materialized view.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'
    Share on Google+

  6. #6
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    If you do need a join,
    you could consider using the 'DRIVING_SITE' hint

    Hope this helps
    Gert
    Share on Google+

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by pmiletic
    Originally posted by kgb

    You may use COPY command to transfer data.
    What COPY command?

    Copy - this is sqlplus command, that use syntax:

    COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table[(column, column, column, ...)] USING query

    where database has the following syntax: username[/password]@connect_identifier

    Copies data from one Oracle database to a table in another. APPEND, CREATE, INSERT or REPLACE specifies how COPY treats the existing copy of the destination table (if it exists). USING query identifies the source table and determines which rows and columns COPY copies from it.
    Share on Google+

  8. #8
    Join Date
    Jan 2002
    Posts
    16
    Thanks for your assistance guys.
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width