In the SQL*Loader control file, use the FILE parameter of the OPTIONS clause to specify the filename of any valid datafile in the tablespace of the object (table or partition) being loaded. SQL*Loader performs character set conversion and datatype conversion for you. 'filename withspace.mat'. For example, to invoke three SQL*Loader direct path load sessions on the same table, you would execute the following commands at the operating system prompt: The previous commands must be executed in separate sessions, or if permitted on your operating system, as separate background jobs.
(See SQL*Loader Case Studies for information on how to access case studies.). This method of data loading is enabled by setting both the DIRECT and the PARALLEL option to TRUE, and is often referred to as a "parallel direct path load.". Use function syntax to load all variables with names not The following topics are covered: For an example of loading with using the direct path load method, see Case 6: Loading Using the Direct Path Load Method. For example, the following OPTIONS clause could be used to specify STORAGE parameters: You can use the STORAGE parameter only in the SQL*Loader control file, and not on the command line. Be aware of the effect on performance when you have large values for both the CONCATENATE clause and the COLUMNARRAYROWS clause. Pre-sorting also allows you to take advantage of high-performance sorting routines that are optimized for your operating system or application. string. Therefore, media recovery is disabled for the loaded table, although database changes by other users may continue to be logged. To start SQL*Loader in direct path load mode, set the DIRECT parameter to true on the command line or in the parameter file, if used, in the format: Optimizing Performance of Direct Path Loads for information about parameters you can use to optimize performance of direct path loads, Optimizing Direct Path Loads on Multiple-CPU Systems if you are doing a direct path load on a multiple-CPU system or across systems. In such cases, it is usually better to use the conventional path load method, or to use the SINGLEROW parameter of SQL*Loader. But when you load data with the direct path, SQL*Loader disables some integrity constraints and all database triggers. If filename has All rows in the table are verified then. load(filename,'-mat') treats filename as This allows distribution based on content such as the value of a parameter. If the same number of rows are to be loaded into each table, then again pick the index with the largest overall width. If redo log file archiving is enabled (you are operating in ARCHIVELOG mode), SQL*Loader logs loaded data when using the direct path, making media recovery possible. If the existing index is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load. But if relatively few rows are added to a large table, then the time required to resort the indexes may be excessive.
If increasing the cache size does not improve performance, revert to the default behavior or set the cache size to 0. the previous syntax group. variables can be in one of the following forms. This section describes two methods for duplicating the effects of such a trigger.
Recover from a system failure in which in-memory data was changed but lost due to the failure before it was written to disk. (See SQL*Loader Case Studies for information on how to access case studies.). The Oracle database looks for partially filled blocks and attempts to fill them on each insert. If your directory is listed, check for ConflictingLibraries. Whenever the insert trigger would raise an exception, the update trigger can mark the row as invalid by setting a flag in the additional column.
All indexes listed in the SORTED INDEXES clause must be created before you start the direct path load.
This effect is simply termed “Load Path.” Load paths need to be continuous. The default value is four buffers. Only the variables X and caption are loaded into the structure array, S. Create an ASCII file from several 4-column matrices, and load the data back into a double-precision array. All indexes listed in the SORTED INDEXES statement must be created before you start the direct path load. To override this behavior, you can specify a format mask in the SQL*Loader control file that is equivalent to the setting of the NLS parameter in the initialization parameter file, or set the appropriate environment variable. Performance is optimized if all character sets are the same. This can slow the load significantly. restriction. See ENABLE in Oracle8i SQL Reference. You can improve performance of direct path loads by using temporary storage.
Use of the NOLOG attribute allows a finer degree of control over the objects which are not logged.
The following topics are covered: Optimizing Performance of Direct Path Loads, Optimizing Direct Path Loads on Multiple-CPU Systems, Direct Loads, Integrity Constraints, and Triggers, For an example of using the direct path load method, see case study 6, Loading Data Using the Direct Path Load Method. Depending on the behavior of the trigger, it may be necessary to have exclusive update access to the table during this operation, so that other users do not inadvertently apply the trigger to rows they modify. When you perform parallel direct path loads, there are options available for specifying attributes of the temporary segment to be allocated by the loader.
Avoid character set conversions if you can. REPLACE, TRUNCATE, and INSERT cannot be used (this is due to the individual loads not being coordinated). Note that a direct path load of a partitioned or subpartitioned table can be quite resource-intensive for tables with many partitions or subpartitions.
Generally, you can use a database update trigger to duplicate the effects of an insert trigger. It can be particularly useful to specify the number of column array rows and size of the steam buffer when you perform direct path loads on multiple-CPU systems.