Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28

    Unanswered: Remote Query Performance

    I am doing an insert from a remote database that is quite large, into a local database on my side. Remote data base is quite large and has several million records. My_local_table_1 is truncated prior to insertion. My local table_2 has about 10-15 thousand records and the query will return about 200,000 rows.

    Problem is that the query works fine but takes almost 3 hours to complete.

    I have tried a driving site hint, but still, no change. The remote dba tried it on his side against the 10,000 cat_numbers I sent him, it took only a few mins to complete from his side.

    The amount of data being returned is not particularly large, maybe 70 char total to a row, but there are about 200,000 rows. Again, this is what I expect. It's the time it takes to do it that I have a problem with.


    Any suggestions on how to improve this time?

    Here is a sample of the sql...




    Code:
    INSERT INTO MY_LOCAL_TABLE_1 (
      SELECT CAT_NUMBER, COLUMN_B, COLUMN_C , COLUMN_D,
      FROM
      REMOTE_TABLE@REMOTE.XYZ.COM
      WHERE
      CAT_NUMBER IN ( SELECT CAT_NUMBER  FROM MY_LOCAL_TABLE_2)
      /
    Same as above with Driving site hint. No noticeable improvement.


    Code:
    INSERT INTO MY_LOCAL_TABLE_1 (
      SELECT  /*DRIVING_SITE  (R)*/ 
    
    CAT_NUMBER, COLUMN_B, COLUMN_C , COLUMN_D,
      FROM
      REMOTE_TABLE@REMOTE.XYZ.COM  R
      WHERE
      CAT_NUMBER IN ( SELECT CAT_NUMBER FROM MY_LOCAL_TABLE_2)
      /

  2. #2
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28
    Ok, this is one of those "Boy do I feel dumb" moments.

    Here is the reason after some testing:

    Hint must read:
    Code:
    /*+ DRIVING_SITE  (R)*/
    Note: the plus sign was missing. Otherwise Oracle treats it as just a comment.

Posting Permissions

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