Which statement is true about loading data by using the conventional path of SQL*Loader?

Question

Options

• ARedo is not generated while performing conventional path loads.
• BOnly PRIMARY KEY, UNIQUE KEY, and NOT NULL constraints are checked,
• CNo exclusive locks are acquired when the conventional path loads are performed.
• DInstead of performing transactions, SQL*Loader directly writes data blocks to the data files.
• EINSERT triggers are disabled before the conventional path load and re-enabled at the end of the

• A
  3% (1)
• B
  83% (25)
• C
  3% (1)
• E
  10% (3)

Explanation

(see direct loads, integrity constraints, and triggers) Data Loading Methods SQLLoader provides two methods for loading data: - conventional path load - direct path load Direct path loads can be significantly faster than conventional path loads. Direct path loads achieve this performance gain by eliminating much of the Oracle database overhead by writing directly to the database files. The direct load, therefore, does not compete with other users for database resources so it can usually load data at nearly disk speed. Certain considerations, inherent to this method of access to database files, such as security and backup implications, are discussed in this chapter. Conventional Path Loads Conventional path loads (the default) use the SQL command INSERT and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications. When SQLLoader 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 commands are generated, passed to Oracle, and processed. Oracle looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically. Direct Path Loads Direct path loads are optimized for maximum data loading capability. Like the conventional path method, SQLLoader's direct path method provides full support for media recovery. Instead of filling a bind array buffer and passing it to Oracle with a SQL INSERT command, the direct path option creates data blocks that are already in Oracle database block format. These database blocks are then written directly to the database. Internally, multiple buffers are used for the formatted data. While one buffer is being filled, multiple buffers are being written if asynchronous I/O is available on the host platform. This parallelism increases load performance. Although direct path loads minimize the necessity of database processing, a few, fast calls to Oracle are made at the beginning and end of the load. Tables are locked and the locks are released at the end. Also, during the load, space management routines are used to get new extents when needed and to adjust the high-water mark. The high-water mark is described in Oracle calls are also used to sort the data and build the index. SQL calls are not performed anytime during the load. Advantages of Direct Path Loads The direct path method is faster than the conventional path for the following reasons: - Partial blocks are not used, so no reads are needed to find them and fewer writes are - SQL INSERT commands are not generated by SQLLoader, and therefore, processing load on the Oracle database is reduced. - The bind-array buffer is not used -- formatted database blocks are written directly. - The direct path method calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. The conventional path calls Oracle once for each array of rows to process a SQL INSERT statement. - Unlike conventional path loads, direct path loads use asynchronous I/O, if available, to perform these operations in parallel: - reading from input files - writing to database files - Processes using the direct path perform their own write I/O, instead of using Oracle's buffer cache in contention with other Oracle users. Therefore, the direct path does not contend for free buffers in the buffer cache. - The direct path's pre-sorting option allows you to use high-performance sort routines that are native to your system or installation. - When the table to be loaded is empty, the pre-sorting option eliminates the sort and merge phases of index-building -- the index is simply filled in as data arrives. - Protection against instance failure does not require redo log file entries during direct path loads. Therefore, if Oracle is operating in NOARCHIVELOG mode, no time is required to log the load. See "Instance Recovery with the Direct Path". Integrity Constraints All integrity constraints are enforced during direct path loads, although not necessarily at the same time. All constraints that can be checked without referring to other rows or tables, such as the NOT NULL constraint, are enforced during the load. Records that fail these constraints are Integrity constraints that depend on other rows or tables, such as referential constraints, are disabled before the direct path load and must be re-enabled afterwards. Direct Loads, Integrity Constraints, and Triggers With the conventional path, arrays of data are inserted with standard SQL statements -- integrity constraints and insert triggers are automatically applied. But when loading data on the direct path, some integrity constraints and all database triggers are disabled. This section discusses the implications of using direct path loads with respect to these features. Integrity Constraints During a direct path load, some integrity constraints are automatically disabled. Others are not. Enabled Constraints The constraints that remain in force are: Not Null constraints are checked at insertion time. Any row that violates this constraint is rejected. Unique constraints are verified when indexes are rebuilt at the end of the load. The index will be left in direct load state if a violation is detected. (Direct load state is explained.) A primary key constraint is merely a unique-constraint on a not-null column. Disabled Constraints The following constraints are disabled: - check constraints - referential constraints (foreign keys)

No community discussion yet for this question.