Use conventional path data load of SQL*Loader utility - Load data into Oracle database - Oracle Clusters

Q.  You are using flat files as the data source for one of your data warehousing applications. You plan to move the data from the flat file structures to an Oracle database to optimize the application performance. In your database you have clustered tables. While migrating the data, you want to have minimal impact on the database performance and optimize the data load operation. Which method would you use to load data into Oracle database?
- Published on 29 Jul 15

a. Use the external table population
b. Use the Oracle Data Pump export and import utility
c. Use the conventional path data load of SQL*Loader utility
d. Use the direct path data load of Oracle export and import utility

ANSWER: Use the conventional path data load of SQL*Loader utility

Related Content

 

    Discussion

  • Nirja Shah   -Posted on 02 Sep 15
    - Use the external table population - The external tables feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database.

    - Use the Oracle Data Pump export and import utility - The new Data Pump Export and Import utilities (invoked with the expdp and impdp commands, respectively) have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate.
    - Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility.
    - Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.

    - Use the conventional path data load of SQL*Loader utility - Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.
    - When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources.
    - This can slow the load significantly.
    - Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.
    - The Oracle database looks for partially filled blocks and attempts to fill them on each insert.
    - Although appropriate during normal use, this can slow bulk loads dramatically.

    - Use the direct path data load of Oracle export and import utility - Data Pump technology enhances direct path technology in the following ways:

    1. Support of a direct path, proprietary format unload.
    2. Improved performance through elimination of unnecessary conversions. This is possible because the direct path internal stream format is used as the format stored in the Data Pump dump files.
    3. Support of additional datatypes and transformations.
    - The default method that Data Pump uses for loading and unloading data is direct path, when the structure of a table allows it.
    - If the table has any columns of datatype LONG, then direct path must be used.

➨ Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)
▲