Data Set Options for Relational Databases |
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
|
BL_INDEX_OPTIONS=any valid SQL*Loader Index
optionsegment-name
|
- 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. |
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.
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;
BULKLOAD= Data Set Option
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.