Oracle Data Load Options

Astera 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 a single record insert does not terminate the entire batch; it only skips that particular record. You can attach a Record Level Log to the database object to log record-level errors.

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

SQL-Loader

SQL-Loader is a reliable and fast way to load large data sets to an Oracle table.

SQL-Loader 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 a ‘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 SQL-Loader options have several 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: SQL-Loader is meant as a mass insert tool. Astera does not currently support SQL-Loader for mass updating, deleting, or upgrading the records.

To use SQL-Loader, the user is required to download and install OracleClientTools files into a local folder on the machine that runs the Astera service.

Bulk Insert

Bulk Insert is a fast way to insert large record sets into an Oracle table. If the Bulk Insert is unable to get a lock on the table, you could get a "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.

Astera 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 privileges 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 been shown to support concurrent writes more reliably 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 SQL-Loader as an alternative option should you encounter an ORA-12592 error during Array Insert.

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

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