Bulk insert

Bulk insert can improve the performance of insert operations on remote tables via inserting data to remote data source by batches instead of inserting row one by one.

Bulk insert of federation is based on the support of remote databases, drivers and relevant wrappers. Currently, bulk insert of federation is only available for the following wrappers and remote databases:
  • ODBC (Netezza®, Oracle, MS SQL Server, and Snowflake)
  • Oracle Native Wrapper (NET8)
  • Db2® Wrapper (DRDA)
    Note: The Db2 Wrapper (DRDA) is available starting from version 11.5.6.
Note: Bulk insert is not applicable for LOB data types.

To enable bulk insert, set the server option ENABLE_BULK_INSERT to Y as shown below: 

create server "SERVER1" type oracle version 12.1 authorization "USER1" password "PASSWD1" options (node 'node1', password 'Y', pushdown 'Y', enable_bulk_insert 'Y');
Federation automatically performs bulk inserts when the insert operation includes a source table or includes multiple values for the INSERT operator in the access plan. A bulk insert is typically chosen if you insert data to a nickname of remote table by using the following insert operations:
  • Insert data from a result set of sub-query as shown in the following example: 
    INSERT INTO n1 SELECT c1, c2 FROM local_t1;
  • Insert more than one value into remote table as shown in the following example: 
    INSERT INTO n1 VALUES (1, 'a'), (2, 'b');