Visual Importer ETL supports four methods of loading data into Oracle using Direct path load, Conventional path load, Ole DB and ODBC Direct-path load in Oracle is used when a session is reading buffers from disk directly into the PGA(opposed to the buffer cache in SGA). During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. You may prefer to use a direct path load when you have a large amount of data to load quickly and you want to load data in parallel for maximum performance, but there are alternative costs to be aware of.
With the conventional path load method, arrays of rows are inserted with standard SQL INSERT statements; integrity constraints and insert triggers are automatically applied. But when you load data with the direct path, Visual Importer ETL disables some integrity constraints and all database triggers.
Option ‘Commit every Array’ works only for Oracle conventional path loading
The constraints that remain in force are:
For conventional path loading when Commit every Array is checked import works as follows:
When Commit every Array is not checked import is executed inside one big transaction:
Note: Loading Unicode data via direct path is not supported. If your database was not created with Unicode support but you have some NCHAR or NVARCHAR2 fields you may need to set array size to 1.