Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: Substitute for COPY in SQL*Plus in Oracle 10g

    Hello Experts,

    I'm using the following query to create a table,which has over 7 million records. I'm writing these query's on oracle 10g using Pl/sql developer IDE. It has been running over 12 hours and is still in execution. Can anyone suggest me some accelration techniques, as this query is fetching data over dblinks

    create table temp1 as (
    select
    table_1.cust_id, table_1.invoice_amt, table_2.payment_date
    from
    table_1@dblink, table_2@dblink
    where
    table_1.cust_id = table_2.cust_id
    )

    I came across some posts that use the COPY command in SQL*PLUS. Is there an equivalent command I can use for Oracle SQL ?

    Many thanks !

    novice

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I came across some posts that use the COPY command in SQL*PLUS.
    Good for you.

    >Is there an equivalent command I can use for Oracle SQL ?
    Why do you not want to use COPY from SQL*Plus?
    What exactly do you mean by "Oracle SQL"?
    Why do you think it will be faster?

    The way it is written EVERY records is brought across the Network in order to do the comparison on the now local system.

    How many rows in table_1@dblink
    How many rows in table_2@dblink
    How many rows meet WHERE table_1.cust_id = table_2.cust_id
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2009
    Posts
    6
    > Why do you not want to use COPY from SQL*Plus?
    Because I dont have rights to configure SQL* Plus on my workstation and I have to go around indentifying the person who will set it up for me!

    > What exactly do you mean by "Oracle SQL"?
    I meant a regular sql query which I can run from my setup without the need to go about configuring environments. Please excuse the terminology, as i'm still trying to figure out how DB's work!

    The method I used to workaround,which I could have thought of earlier is :

    Copy the table structure from the remote db into my local db using:

    CREATE TABLE temp1 AS SELECT cust_id, invoice_amt FROM table_1@dblink WHERE 1=2
    : This copies the table structure, where 1=2 ensures, no records are copied.

    INSERT records into my table using:

    INSERT INTO temp1 SELECT cust_id, invoice_amt FROM table_1@dblink

    Now, I will go about performing a Join, in my local db. The problem using this method is that this method does'nt copy the original tables attributes / indexes.

    Will be great if anyone can suggest how the attributes / indexes can be copied too!

    thanks!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Data manipulation over a database link can be very slow. If it is possible, try to bring all data (here I mean 'table_1' and 'table_2') into your database, either by using export / import utilities, or perhaps by creating a materialized view.

  5. #5
    Join Date
    Feb 2009
    Posts
    62
    Are both your remote tables across the same database link?

    If so, you could try
    Code:
    create table temp1 as (
    select /*+ driving_site (table_1) */
    table_1.cust_id, table_1.invoice_amt, table_2.payment_date
    from
    table_1@dblink, table_2@dblink
    where
    table_1.cust_id = table_2.cust_id
    )
    as this should perform the sql at the remote site and simply bring the results back across the network.

    If the results of the join will be more rows than are in either of the two tables, then your posted approach of making a local copy of the two tables will probably perform quicker.

Posting Permissions

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