Restrictions

Data load optimization is not followed when certain scenarios are met.

Data-Load Optimization – Restrictions

Traditional row-by-row mode data load is used when:

ins_by_bulk is not enabled in the plan clause or set statement.
The estimated page count to be inserted is fewer than eight pages (applies only to insert...select statements).
The target table is a system table.
The target table is a temporary table.
The target table is a remote table.
The target table is an all pages-locked (APL) table.
The target table contains foreign keys for referential integrity.
The target table is an in-memory database object.
Referential integrity or deferred referential integrity is defined on the target table.
full logging is not properly specified at the database, session, or query statement level.
The target table contains large object (LOB) columns.
The insert trigger is on the target table.
insert...values is used without parameters.
Source and target tables are the same.
The ignore_dup_key option is used with a unique index.
The batch insert query is not dynamically prepared.
The insert operator is issued from the alter table command.
Bulk insert is disabled while the following commands are underway concurrently when the data load is attempted: .
- reorg rebuild ... with online
- create index ... with online
The insert instead performs in non-bulk mode, with serial index updates

Data loaded using bulk inserts in a multi-statement transaction is visible only to the inserting task while the transaction is active. While the transaction that loaded this data using bulk inserts is still active, concurrent access to this data by other tasks skips the uncommitted data.
Concurrent data loads to the same table (or partition) from different tasks is supported. All data inserted during these concurrent data loads remains invisible to all tasks other than the inserting task until the inserting transaction commits.
A single transaction can perform multiple bulk inserts to the same table multiple times. However, a maximum number of four tables per database can be inserted using this optimization in one transaction.
For transactions that span multiple databases, each database has a maximum of four tables which is inserted using this optimization.

Bulk inserts are not applied to the fifth and subsequent tables in one transaction, and data load is performed in non-bulk mode, with serial index updates.
If the ddl in tran database option is enabled, an attempt to execute create index ...with online on a table following an insert bulk to that table in the same transaction is restricted. The create index operation is aborted, but the transaction remains active.