When loading data through EIM process, we normally use Pl/SQL procedures to fetch and process huge volume of records and then load the data to EIM Interface tables. There are scenarios when the normal way of data loading into EIM tables take long time due to the bulk volume of records. Typical usage of SQL queries result in performance bottleneck issues due to the frequent context switches between the SQL and Pl/SQL engine
Implementation :
We have an OOB solution for this by using the BULK COLLECT function provided by Oracle which provide a significant performance boost when dealing with large sets of data. This feature enables to reduce the Turn Around time involved in loading and processing records in EIM table.
Syntax :
FETCH<cursor_name> BULK COLLECT INTO <collection_name> LIMIT
LIMIT: Specify the # of records to be collected during single fetch.
PS : This function is available in Oracle and works only for Oracle 10g version.