Printable View
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
I like it because it is easy and lightweight, requires no db links, allows periodic commits.
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?Quote:
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.
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
Copy works fine for the LONG columns while your CTAS would not
Badrinath
Thank you for your replies, they were very informative.
-Jason
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.Quote:
Originally posted by badrinathn
Copy works fine for the LONG columns while your CTAS would not
Badrinath
Abhay.
I am not aware that COPY has any issues regarding 7-bit charactersets.Quote:
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.
Can you explain this?
Lemme giv an example where we found dificulty in using COPYQuote:
Originally posted by TomazZ
I am not aware that COPY has any issues regarding 7-bit charactersets.
Can you explain this?
This sounds strange, but when I looked into the record/s which was causing COPY to fail, I found that value was ASCII(¹)=185.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.
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
Abhay.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
I don't get it - how can you have a chr(185) in us7ascii database anyway :confused:
Quote:
Originally posted by TomazZ
I don't get it - how can you have a chr(185) in us7ascii database anyway :confused:
7-Bit Characterset will have range of binary values from -127 to +127.Code:
WW15_DBA> select * from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
WW15_DBA> select kurst,FCURR,TCURR,GDATU, ascii(substr(GDATU,3,3)) "Ascii Value of GDATU" from temp_ytcurr
2 where kurst='M' and FCURR='GRD' and TCURR='USD' and GDATU like '19%90320';
KURS FCURR TCURR GDATU Ascii Value of GDATU
---- ----- ----- -------- --------------------
M GRD USD 19990320 185
M GRD USD 19990320 185
M GRD USD 19990320 57
M GRD USD 19990320 57
the range of values from -127 to -1 will be mirror image of 1 to 127..i mean same charactes/numbers repeated.
Abhay.
Actually, 7-bit characterset should have only 0-127 character values.Quote:
Originally posted by abhaysk
7-Bit Characterset will have range of binary values from -127 to +127.
2^7=128
I look that up on metalink:
Ther is no problem in copy command with 7-bit charactersets except if the 7-bit character has another bit in the pocket :DQuote:
Data in us7ascii is 8-bit, but the point is that the value of the 8th bit is undefined. All our software can do is assume that data is in the encoding scheme you say it is. If it is not, then you can not expect things to work correctly. If you have the database character set specified as US7ASCII and NLS_LANG as (for example) American_America.US7ASCII then since both are the same, no character set conversion will take place. If you now 'cheat' and insert a character with a byte value 164 (which is for example an 'n tilde lowercase' in WE8PC850), then that is the byte value that is stored, and that is the byte value that is returned if you query it. However, any string processing done on the data (for example UPPER, LOWER, INITCAP SQL functions, or character validation done by Developer in a CHAR field) will process the byte as if it were byte value 36 (being 164-128) since you have told the software that the data is in US7ASCII.
...
the point is that the database encoding scheme (specified with the CREATE DATABASE command) must be one that defines all the characters you need to handle in the specific application.
This is what I said in my previous post ( Ofcourse I should admit I had thought wrongly the range of values, however with 8th bit undefined we can think of it as SIGN bit as well ;) )Quote:
Originally posted by TomazZ
Ther is no problem in copy command with 7-bit charactersets except if the 7-bit character has another bit in the pocket :D
"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."
Yep, that's what you said. Sorry and thanks for interesting information.
Still, I would say the fault is that database lets you insert 7-bit character with value over 127 in the first place.
And the copy command is probably not the only victim of this fault.
DB lets you insert -> agreed thats not the one it should as its a 7-bit charset.Quote:
Originally posted by TomazZ
Still, I would say the fault is that database lets you insert 7-bit character with value over 127 in the first place.
And the copy command is probably not the only victim of this fault.
Ok, now try "select chr(340) from dual;" in any of the 8-Bit characterset you will know the reason why i said -127 to +127 or rather you can assume 127+ as starting from 0+ values ( but this however differs in actual storage of Binary Values )....
Did I say COPY is only victim of this fualt?
Abhay.
It's doing mod 256 (wraps around the byte discarding overflow). And what's the point?Quote:
Originally posted by abhaysk
Ok, now try "select chr(340) from dual;" in any of the 8-Bit characterset you will know the reason why i said -127 to +127 or rather you can assume 127+ as starting from 0+ values ( but this however differs in actual storage of Binary Values )....
I don't get you :confused:
I don't believe you did.Quote:
Did I say COPY is only victim of this fualt?
Have I said you said something like that?
:p