Lets you specify SQL*Loader Index options with bulk loading.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Alias: | SQLLDR_INDEX_OPTION= |
| Default: | none |
| Requirement: | To specify this option, you must first specify BULKLOAD=YES. |
| Data source: | Oracle |
| See: | BULKLOAD= data set option |
Table of Contents
The value that you specify for this option must be a valid SQL*Loader index option, such as one of those below. Otherwise, an error occurs.
Use this option when loading either a direct path with APPEND on systems with limited memory or a small number of records into a large table. It inserts each index entry directly into the index, one record at a time. By default, DQL*Loader does not use this option to append records to a table.
This clause applies when you are loading a direct path. It tells the SQL*Loader that the incoming data has already been sorted on the specified indexes, allowing SQL*Loader to optimize performance. It lets the SQL*Loader optimize index creation by eliminating the sort phase for this data when using the direct-path load method.
You can now pass in SQL*Loader index options when bulk loading. For details about these options, see the Oracle utilities documentation.
This example shows how you can use this option.
proc sql;
connect to oracle ( user=myusr1 pw=mypwd1 path=mypath);
execute ( drop table blidxopts) by oracle;
execute ( create table blidxopts ( empno number, empname varchar2(20))) by
oracle;
execute ( drop index blidxopts_idx) by oracle;
execute ( create index blidxopts_idx on blidxopts ( empno ) ) by oracle;
quit;
libname x oracle user=myusr1 pw=mypwd1 path=mypath;
data new;
empno=1; empname='one';
output;
empno=2; empname='two';
output;
run;
proc append base= x.blidxopts( bulkload=yes bl_index_options='sorted indexes
( blidxopts_idx)' ) data= new;
run;