176 DBINDEX= Data Set Option Chapter 10
triggers the libname engine to search for all indexes on a table and return them to
SAS for evaluation. If a usable index is found, then the join WHERE clause is passed
to the DBMS for processing. A usable index is expected to have at least the same
attributes as the join column.
NO
no automated index search is performed.
index-name
verifies the index name that is specified for the index columns on the DBMS table.
This requires the same type of call as when DBINDEX=YES is used.
Details
When processing a join that involves a large DBMS table and a relatively small SAS
data set, you may be able to use DBINDEX= to improve performance.
CAUTION:
Improper use of this option can impair performance. See “Using the DBINDEX=,
DBKEY=, and MULTI_DATASRC_OPT= Options” on page 38 for detailed
information about using this option.
Queries must be issued to the necessary DBMS control or system tables to extract
index information about a specific table or validate the index that you specified.
The DBINDEX= option can be entered as a libname option, SAS data set option, or
as an option with PROC SQL. The order that the libname engine will process this
option is as follows:
1 data step or PROC SQL specification.
2 LIBNAME statement specification.
Note: If “DBKEY= Data Set Option” on page 177 is specified, it will take
precedence over DBINDEX=.
Example
The following SAS data set is used in these examples:
data s1;
a=1; y=’aaaaa’; output;
a=2; y=’bbbbb’; ouput;
a=5; y=’ccccc’; output;
run;
The following example demonstrates the use of DBINDEX= in the libname statement:
libname mydblib oracle user=myuser password=userpwd dbindex=yes;
proc sql;
select * from s1 aa, x.dbtab bb where aa.a=bb.a;
select * from s1 aa, mydblib.dbtab bb where aa.a=bb.a;
The DBINDEX= values for table dbtab are retrieved from the DBMS and compared
with the join values. In this case, a match was found so the join is passed down to the
DBMS using the index. If the index
a was not found, the join would take place in SAS.
The following example demonstrates the use of DBINDEX= in the SAS data step:
data a;
sets1;
