DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Why use the 'copy' command?

  1. #1

    Why use the 'copy' command?

    Do you know of any functional difference between the 'copy' statement and a 'insert into emp2 (select * from emp)' or a 'create table emp2 as (select * from emp)'? A developer suprised me with the copy command and I'm trying to figure out what are the advantages/disadvantages of it vs the alternatives.

    Thanks,
    -Jason

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I like it because it is easy and lightweight, requires no db links, allows periodic commits.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    COPY command works faster when all the columns are selected. However, it will run slowly when a sub set of columns is selected.

    CTAS ( create table as select) is much desirable options by developers because it can be done in parallel.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by tamilselvan
    COPY command works faster when all the columns are selected. However, it will run slowly when a sub set of columns is selected.

    CTAS ( create table as select) is much desirable options by developers because it can be done in parallel.
    Why does it run more slowly for a subset of columns?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    We use it because our developers are not allowed to use import/export. And as Dave pointed out you can set up commits within using ARRAYSIZE and COPYCOMMIT.


    Here is some info on copy that I prepared (with the help of "Oracle SQL*Plus, the definitive guide, O'Reilly) a while ago for some developers.


    SET ARRAY[SIZE] May be abbreviated to array.
    This parameter sets the number of rows fetched at one time. The default is 15. The allowed range is from 1 to 5000.

    SET COPYC[OMMIT] May be abbreviated to copyc.
    The copycommit setting works in conjunction with the arraysize setting. The arraysize setting controls the number of rows in a batch. The copycommit setting controls how many batches are copied before committing. The number or rows copied before each commit is equal to ARRAYSIZE * COPYCOMMIT.

    Example
    SET ARRAYSIZE 15
    SET COPYCOMMIT 10
    COPY TO my_id@my_big_arse_database.com -
    INSERT my_id.table_name -
    USING SELECT * from table_name;

    Since the arraysize setting is 15 and the copycommit setting is 10, the COPY statement shown here will commit changes after every 150 rows (15 * 10).

    COPY
    The COPY command allows you to use SQL* Plus as a conduit for transferring data between two Oracle databases:

    COPY {FROM connection | TO connection}
    {APPEND | CREATE | INSERT | REPLACE}
    destination_table [(column_list)]
    USING select_statement

    Note; When using the COPY command In SQL*Plus you must code a hyphen (-) after each line to continue to the next line. The last line will end with a semi-colon (
    COPY is the command.

    FROM/TO
    If you are connected to the source database , use the TO option to specify the destination database. If you are connected to the target database, use the FROM option to specify the source of the data.

    Connection
    Is the login information to use when connection to the other database.
    Your_id@your_big_arse_database.com

    APP[END]
    Causes SQL*Plus to insert the copied rows into the destination table, creating it first if necessary.

    CRE[ATE]
    Causes SQL*Plus to copy the data only if the destination table is a new table.
    If the destination table already exists, the COPY command will abort.

    INSERT
    Causes SQL*Plus to insert the copied rows into the destination table only if it already exists. If the destination table is a new table, the COPY command will abort.

    REP[LACE]
    Causes SQL*Plus to drop the destination table if it currently exists. A new table is then created, and the data is copied.

    Destination_table
    Is the name of the table to which you want to copy.

    Column_list
    Specifies column names to use when the COPY command creates a new destination table. This is a comma delimited list, and the number of column names must match the number of columns in the SELECT statement.

    Select_statement
    Is a SELECT statement that returns the data you want to COPY.


    Can you feel the love in here?

    MH
    Last edited by Mr.Hanky; 07-15-2003 at 02:16 PM.
    I remember when this place was cool.

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Copy works fine for the LONG columns while your CTAS would not

    Badrinath
    There is always a better way to do the things.

  7. #7

    Talking Thanks!

    Thank you for your replies, they were very informative.

    -Jason

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by badrinathn
    Copy works fine for the LONG columns while your CTAS would not

    Badrinath
    COPY work fine with what so ever....only exception is for Characterset US7ASCII or any 7-bit characterset when ascii values are above 127.For this case it sucks.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by abhaysk
    COPY work fine with what so ever....only exception is for Characterset US7ASCII or any 7-bit characterset when ascii values are above 127.
    I am not aware that COPY has any issues regarding 7-bit charactersets.
    Can you explain this?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    I am not aware that COPY has any issues regarding 7-bit charactersets.
    Can you explain this?
    Lemme giv an example where we found dificulty in using COPY

    Code:
    WWCIW_DBA@WW15> truncate table wwciw_sap.ytcurr;
    
    Table truncated.
    
    WWCIW_DBA@WW15> copy from wwciw_dba@ww04 to wwciw_dba@ww15-
    > insert ytcurr-
    > using-
    > select * from ytcurr
    FROM
    TO
    
    Array fetch/bind size is 5000. (arraysize is 5000)
    Will commit after every 5 array binds. (copycommit is 5)
    Maximum long size is 80. (long is 80)
    SQLRCN in cpytbl failed: -1075
    SQLRCN in cpyyerr failed: -1075
    
    ERROR:
    ORA-00001: unique constraint (WWCIW_SAP.PK_YTCURR) violated
    
    
    WWCIW_DBA@WW15> select name from v$database;
    
    NAME
    ---------
    WW15
    
    WWCIW_DBA@WW15> truncate table wwciw_sap.ytcurr;
    
    Table truncated.
    
    WWCIW_DBA@WW15> insert into ytcurr select * from ytcurr@ww04;
    
    594951 rows created.
    
    -- Now it dosent error, Strange
    
    WWCIW_DBA@WW15> commit;
    
    Commit complete.
    This sounds strange, but when I looked into the record/s which was causing COPY to fail, I found that value was ASCII(¹)=185.

    But when we used, DB Link all records were inserted without any errors..

    Reason for COPY to fail was, it wasnt sensing the difference between '9' and '9' see below

    Code:
    WW04_DBA> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_CHARACTERSET               US7ASCII
    
    WW04_DBA> select chr(185) "ASCII(¹)", chr(57) "ASCII(9)" from dual;
    
    A A
    - -
    9 9
    
    But See in other Charactersets ( 8-Bit )
    
    TEST_ABHAY> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_CHARACTERSET               WE8MSWIN1252
    
    TEST_ABHAY> select chr(185) "ASCII(¹)", chr(57) "ASCII(9)" from dual;
    
    A A
    - -
    ¹ 9
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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