1.12 COPY : Using NO COMMIT Option.

COPY : Using NO COMMIT Option.

• Use the NO COMMIT option with COPY (unless the tables are temp tables) to perform a bulk load transaction without automatically committing the results.

• This option is useful for executing multiple COPY commands in a single transaction.

Say we have to load multiple files, but they are in different formats Ex: UNCOMPRESED,BZIP .GZIP

As we cannot combine multiple compression techniques in same load, we can perform multiple copy loads with no commit option and the after the last command we can COMMIT.

For example, the following set of COPY ... NO COMMIT statements performs several copy statements sequentially, and then commits them all.

In this way, all of the copied data is either committed or rolled back as a single transaction.

COPY... NO COMMIT;   
COPY... NO COMMIT;
COPY... NO COMMIT;
COPY X FROM LOCAL NO COMMIT;
COMMIT;

• You can combine NO COMMIT with most other existing COPY options, but not the REJECTED DATA AS TABLE option. 

If a transaction is in progress that was initiated by a statement other thanCOPY (such as INSERT), using COPY with NO COMMIT adds rows to the existing transaction, rather than starting a new one. The previous statements are NOT committed.

• Following are advantages of NO COMMIT:

1. Using a single transaction for multiple COPY statements also allows HP Vertica to load the data more efficiently since it can combine the larger amounts of data from multiple COPY statements into fewer ROS containers.

2. You can use the NO COMMIT option to detect constraint violations as part of the load process.

HP Vertica checks for constraint violations when running a query, but not when loading data. To detect constraint violations, load data with the NO COMMIT keyword and then test the load using ANALYZE_CONSTRAINTS.
If you find any constraint violations, you can roll back the load because you have not committed it.