ORACLE Data Load Options in Centerprise 7.4

Centerprise 7.4 offers several data load options for writing to an Oracle table. The article below summarizes their recommended uses as well as best practices for each of those options.

Single Record Insert

Single Record Insert is the slowest of all the data load options by some margin, but is also very reliable. This data load option allows you to log record level errors. An error during single record insert does not terminate the entire batch; it only skips that particular record. You can attach Record Level Log to the database object to log record level errors.

Due to obvious speed limitations, single record insert is mostly suitable for smaller data loads.

SQLLoader

SQLLoader is a reliable and fast way to load large data sets to an Oracle table.

SQLLoader has two data load options:

  1. Conventional Load is the default option and it is the slower option of the two. Its write performance is comparable to Array Insert. This is the more flexible option as it supports better concurrency (including several SQLLoader dataflows writing to the same table at once) due to locking. We recommend using this option by default.
  2. Direct Load is the faster option and it mimics bulk insert in terms of performance and locking requirements. We do not recommend this option if you anticipate heavy concurrency, such as processes inserting/updating a single target table at the same time. We have seen ‘resource busy error’ from Oracle database in those scenarios.

A record level error (aka ‘data dependent error’ in Oracle terms) does not terminate the entire batch; the error record is skipped and optionally written to the SQLLoader log file.

Both SQLLoader options have a number of different restrictions as to indexes, concurrency, triggers and sequence objects (for instance, Conventional Load supports Sequence Objects, while Direct Load will insert NULLs instead). The link below describes those differences in more detail:

https://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1007501

Note 1: SQLLoader is meant as a mass insert tool. Centerprise does not currently support SQLLoader for mass updating, deleting or upserting the records.

Note 2: To use SQLLoader, the user is required to download and install OracleClientTools files into a local folder on the machine that runs Centerprise service.

Bulk Insert

Bulk Insert is a fast way to insert large record sets into an Oracle table. If Bulk insert is unable to get a lock on the table, you could get ‘resource is busy’ error (ORA-00600), which we have seen happen during concurrent inserts. Because of that, we recommend scheduling bulk insert loads in a way that will make the target table available/not used by other competing processes.

Bulk Insert supports identity column insert, but does not support Sequence Objects. A Null value will be written in the Sequence Object column. There are also some trigger and index considerations when using this data load option.

Centerprise supports bulk insert load for Insert, Update, Delete and Upsert. Update, Delete and Upsert actions are done using a temp table in the target database. This requires the user account to have create/drop privilege for global temp tables in the Oracle database.

Array Insert

Array insert is a fast way to insert large record sets into an Oracle table. Performance-wise it is comparable to Bulk Insert. Array insert has shown to support concurrent writes in a more reliable manner compared to Bulk Insert. However, we have noticed the occasional ORA-12592 error, which appears to affect Array Insert due to some connectivity/network issue between the Astera service and the Oracle database. Because of that, we recommend using SQLLoader as an alternative option should you encounter ORA-12592 error during Array Insert.

Array Insert supports identity column insert, and it also supports Sequence Objects.

Centerprise also supports array insert mode for updating, deleting and upserting (update + insert) into the target table.