Data Set Options for Relational Databases

BL_INDEX_OPTIONS= Data Set Option

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

Alias:	SQLLDR_INDEX_OPTION=
Default value:	none
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Oracle

Syntax
Syntax Description
Details
Example
See Also

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 the following. 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 allows the SQL*Loader to optimize index creation by eliminating the sort phase for this data when using the direct-path load method.

Details

To specify this option, you must first set BULKLOAD=YES.

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

Example

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;