BL_INDEX_OPTIONS= Data Set Option

Lets you specify SQL*Loader Index options with bulk loading.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Alias:	SQLLDR_INDEX_OPTION=
Default:	none
Requirement:	To specify this option, you must first set BULKLOAD=YES.
Data source:	Oracle
See:	BULKLOAD= data set option

Syntax

Details

Example

Syntax

BL_INDEX_OPTIONS=any valid SQL*Loader Index optionsegment-name

Syntax Description

any valid SQL*Loader Index option

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.

SINGLEROW: 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.

SORTED INDEXES: 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.

Details

You can now pass in SQL*Loader index options when bulk loading. For details about these options, see the Oracle utilities documentation.

Example: Specify SQL*Loader Index Options

This example shows how you can use this option.

proc sql;
connect to oracle  ( user=scott pw=tiger path=alien);
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=scott pw=tiger path=alien;
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;