copy command

[vignesh_man]

Can we enforce commit intervals using copy command in vertica database?

[JimKnicely]

Hi vignesh_man,

Welcome to vertica-forums.com!

Unfortunately (as of Vertica 6.0.0-3) there is not an option of the COPY command that will commit portions of the records copied... There is one commit at the end. Keep in mind, this is probably a good idea because you want the data to load ASAP and I've found having a lot of transactions is bad for performance.

Note: The autocommit variable can only be turned on or off... I was hoping, like in Oracle, we could set the variable equal to a commit interval:

Code: Select all

dbadmin=> set autocommit = 100;
ERROR 5415:  Value must be either ON or OFF

Is there any way you can break up the data you want to load into multiple files where each file contains the set number of records that you want the commit interval to be?

Thanks.

[jfur2133]

Are there any plans to add a COMMIT to the COPY command? The problem we are running into is when we load a large table we run out of temp space.

[JimKnicely]

Please check out this post:

viewtopic.php?f=60&t=314&p=1280#p1280

The forums' user jpcavanaugh has generously offered to send "New Feature" requests to Vertica for us!