Kbase 21352: What is Bulk Inserts and how does it differ from a Data Dictionary load for Oracle DataServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/22/2010 |
|
Status: Verified
GOAL:
What is Bulk Inserts and how does it differ from a Data Dictionary load for Oracle DataServer?
GOAL:
How does Bulk inserts differ from a Data Dictionary load for Oracle DataServer?
FACT(s) (Environment):
Oracle DataServer
Progress 9.1B
Progress 9.1C
Progress 9.1D
OpenEdge 10.x
All Supported Operating Systems
FIX:
Bulk Insert is a specific load feature under the DataServer > ORACLE Utilities Options Menu. Specific to ORACLE DataServer Version 9.1B and higher, the utility is not present within ORACLE DataServer Version 8 and is not an implemented feature for other DataServer licenses, such as MS SQL DataServer, ODBC DataServer, DB2/400 DataServer. The utility's purpose is to provide an alternative method (other than the conventional Data Dictionary option) for loading .d (data) files into ORACLE.
-- Data Dictionary Load
a) The Data Dictionary > Data Administration > Admin > Load .d option does an individual insert for each row.
b) Each row typically will issue an oexfet (to get a value for the progress_recid) and then an oexec (insert the row).
c) If the table has a progress_recid column, the DataServer must first retrieve the next value from the sequence generator.
In this method, the application creates a new record, assigns value and at a later time, the DataServer will execute a SQL INSERT statement.
d) Upon reviewing the SQL statements; the SELECT, and get next value is done before the INSERT.
-- Bulk Inserts
a) Bulk Insert will insert several rows at once with a single
execute.
b) Several hundred records are inserted into ORACLE as a single
array insert operation, drastically reducing OCI/Net 8
overhead and improving Oracle?s internal record management.
c) Here the client will collect several new rows and send them to
the Server in one large message. This Server will collect
several rows and perform an oexn call, to insert several rows
at one time.
d) Instead of an oexfet and an oexec for each row inserted
(which is seen via a Data Dictionary load), only one oexn will
be executed for many (potentially hundreds) of rows.
e) If the table has a progress_recid column, the DataServer will
NOT fetch the next value and ORACLE is told to use the
sequence generator in the INSERT statement.
Bulk Insert Advantages
-- Performance difference between the two load utilities.
There is a 3-fold improvement with using Bulk Insert. The
biggest improvement is with tables that have a progress_recid,
hence the DataServer tells ORACLE to get the value directly
from the sequence generator.
-- How many records can Bulk Insert load?
It depends on the maximum length of the widest character column. If there is a LONG & LONG RAW column then we check if there is any column wider than 256 characters for processing the record; however, this does not affect how much data can be inserted into those columns. There is no limitation on the .d file size.
Bulk Insert Disadvantages -- utility restrictions
a) Error reported to the client is delayed
b) During the load, client will not be able to identify exactly
which row had a problem
c) Rows may be inserted out of sequence if they contain a LONG
column
d) RECID/ROWID will be unknown. The utility never requests the RECID/ROWID of records being inserted because it does not know when the insert will be done.
Bulk Insert Performance
-- What method can be used to increase Bulk Insert Performance?
A very useful thing to do is to drop all of the indexes (in ORACLE, leave the schema holder alone), do the Bulk Insert and then re-create the indexes within ORACLE.