Re: COPY vs. INSERT

--0__=YqBe6kKSfFdDVAO5IDlRN7hjDUticYPLlwTCq6xe0kQzBv4q48PLEMMn
Content-type: text/plain; charset=us-ascii Content-Disposition: inline

COPY is a SQLPLUS command only while INSERT is a SQL command that can run anywhere (not restrict only to SQL PLUS) you can set SQLPLUS in a way that they will commit after certain number of rows are COPied to the table, while without doing any PL/SQL script, you can only commit once after the whole insert goes through.

That means, COPY will not eat up rollback segment the way INSERT is (if rollback space is a concern to you)

And COPY had a lot of options like REPLACE, APPEND, INSERT, TRUNCATE(I believe!) etc. but INSERT will only add to the end of the table.

That's what I can think of so far!

Winnie

"Walter K." <alden14004_at_yahoo.com> on 05/23/2000 05:46:37 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Winnie Liu/HQ/ISC)

Subject: COPY vs. INSERT

Hi,

Which is more efficient, to populate a table via a standard INSERT command or using the SQL*Plus COPY command? Has anyone tried to benchmark the two? What are the pros/cons of each? The table to be populated will always need to be appended to, never truncated.

Thanks in advance for the advice.
-wk

--0__=YqBe6kKSfFdDVAO5IDlRN7hjDUticYPLlwTCq6xe0kQzBv4q48PLEMMn
Content-type: text/html;

        name="att1.htm"

Content-Disposition: attachment; filename="att1.htm"
Content-transfer-encoding: base64
Content-Description: Internet HTML

PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDQuMCBUcmFuc2l0aW9uYWwv L0VOIj4NCjxIVE1MPjxIRUFEPg0KPE1FVEEgY29udGVudD0idGV4dC9odG1sOyBjaGFyc2V0PWlz by04ODU5LTEiIGh0dHAtZXF1aXY9Q29udGVudC1UeXBlPg0KPE1FVEEgY29udGVudD0iTVNIVE1M IDUuMDAuMjkyMC4wIiBuYW1lPUdFTkVSQVRPUj4NCjxTVFlMRT48L1NUWUxFPg0KPC9IRUFEPg0K PEJPRFkgYmdDb2xvcj0jZmZmZmZmPg0KPERJVj48Rk9OVCBmYWNlPUFyaWFsIHNpemU9Mj5IaSw8 L0ZPTlQ+PC9ESVY+DQo8RElWPjxGT05UIGZhY2U9QXJpYWwgc2l6ZT0yPiZuYnNwOzwvRElWPjwv Rk9OVD4NCjxESVY+PEZPTlQgZmFjZT1BcmlhbCBzaXplPTI+V2hpY2ggaXMgbW9yZSBlZmZpY2ll bnQsIHRvIHBvcHVsYXRlIGEgdGFibGUgdmlhIGEgDQpzdGFuZGFyZCBJTlNFUlQgY29tbWFuZCBv ciB1c2luZyB0aGUgU1FMKlBsdXMgQ09QWSBjb21tYW5kPyBIYXMgYW55b25lIHRyaWVkIHRvIA0K YmVuY2htYXJrIHRoZSB0d28/IFdoYXQgYXJlIHRoZSBwcm9zL2NvbnMgb2YgZWFjaD8gVGhlIHRh YmxlIHRvIGJlIHBvcHVsYXRlZCANCndpbGwgYWx3YXlzIG5lZWQgdG8gYmUgYXBwZW5kZWQgdG8s IG5ldmVyIHRydW5jYXRlZC48L0ZPTlQ+PC9ESVY+DQo8RElWPiZuYnNwOzwvRElWPg0KPERJVj48 Rk9OVCBmYWNlPUFyaWFsIHNpemU9Mj5UaGFua3MgaW4gYWR2YW5jZSBmb3IgdGhlIGFkdmljZS48 L0ZPTlQ+PC9ESVY+DQo8RElWPjxGT05UIGZhY2U9QXJpYWwgc2l6ZT0yPi13azwvRk9OVD48L0RJ Vj48L0JPRFk+PC9IVE1MPg0KDQo= Received on Tue May 23 2000 - 19:06:13 CDT