Batch and Bulk Updates

Loading and updating data in Vector tables can be done as either a bulk (Append operator in terms of x100 algebra) or a batch operation. SQL statements and the command line data loading tools map to bulk and batch DML operations as follows:

• INSERT is a batch operation that quickly adds a small number of records.

• DELETE is a batch operation that quickly deletes a small number of records.

• UPDATE is a batch operation that quickly modifies a small number of records.

• CREATE TABLE AS SELECT is a bulk operation (Append) that adds a large number of records.

• INSERT...SELECT is a bulk operation (Append) that adds a large number of records.

• A batch INSERT through an ODBC, JDBC, or .Net based application is a bulk operation (Append) that adds a large number of records.

• COPY FROM is a bulk operation (Append) that adds a large number of records.

• vwload is a bulk operation (Append) that adds a large number of records.

• A data load through DataFlow Direct Load is a special type of bulk operation (Append) that adds a large number of records.

COPY FROM, INSERT ... SELECT, batch INSERT through JDBC, ODBC or .Net based applications and vwload are batch operations if executed on a non-empty table with a clustered index.

For these operations to be bulk (i.e. Append), the target table with the clustered index must meet all of the following conditions:

• The table is empty.

• There have been no batch updates performed on this table since the last COMBINE call for the table.

• If the index is based on a foreign key, there can be no batch updates on the referenced table.

Note: The table can be seen as empty, but there can still be batch updates stored for it in memory. This can happen if a user issues, for example, an INSERT or COPY command followed by a DELETE.