Home >
Articles > What are the different types of data loading methods used by SQL*Loader?
What are the different types of data loading methods used by SQL*Loader?
Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to
ace your certification exam in first attempt.
SQL*Loader uses the following types of data loading methods for loading data into database tables:
- Conventional path load: In a conventional path load, SQL*Loader uses the INSERT statement and a bind array buffer to load data into database tables. The input data is parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full, the INSERT statement is automatically executed to insert the data stored in the bind array into a table. By default, SQL*Loader uses the conventional path load for data loading.
- Direct path load: In a direct path load, SQL*Loader parses the input data according to the field specifications, converts the input field data to the column (column of the table in which the data is to be inserted) datatype, and builds a column array. The column array is passed to a block formatter. The block formatter creates data blocks in Oracle database block format. The newly formatted data blocks are directly written to the database, bypassing most of the SQL statement processing. Therefore, a direct path load is much faster than a conventional path load.
- External table load: In an external table load, SQL*Loader creates an external table for data in a data file and uses the INSERT statement to insert the data from the external table into the target database table. When loading data from a large data file by using the external table load, SQL*Loader loads the data in parallel. In an external table load, the data being loaded can be modified byusing SQL and PL/SQL functions.
Other articles
