DBINDEX= Data Set Option

Detects and verifies that indexes exist on a DBMS table. If they do exist and are of the correct type, a join query that is passed to the DBMS might improve performance.

Valid in:	DATA and PROC steps
Default:	DBMS specific
Supports:	All

Syntax

Details

Examples

Example 1: Use DBINDEX= in a LIBNAME Statement

Example 2: Use DBINDEX= in a SAS DATA Step

Example 3: Use DBINDEX= in PROC SQL

Syntax

DBINDEX=YES | NO | <'>index-name<'>

Syntax Description

YES: triggers the interface 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 might be able to use DBINDEX= to improve performance.

CAUTION:

Improper use of this option can degrade performance.

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.

Examples

Example 1: Use DBINDEX= in a LIBNAME Statement

The following SAS data set is used in these examples:

data s1;
   a=1; y='aaaaa'; output;
   a=2; y='bbbbb'; output;
   a=5; y='ccccc'; output;
run;

The following example demonstrates the use of DBINDEX= in the LIBNAME statement:

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd
   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. Therefore, 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.

Example 2: Use DBINDEX= in a SAS DATA Step

The following example demonstrates the use of DBINDEX= in the SAS DATA step:

data a;
   set s1;
   set x.dbtab(dbindex=yes) key=a;
   set mydblib.dbtab(dbindex=yes) key=a;
run;

The key is validated against the list from the DBMS. If a is an index, then a pass down occurs. Otherwise, the join takes place in SAS.

Example 3: Use DBINDEX= in PROC SQL

The following example demonstrates the use of DBINDEX= in PROC SQL:

proc sql;
   select * from s1 aa, x.dbtab(dbindex=yes) bb where aa.a=bb.a;
   select * from s1 aa, mylib.dbtab(dbindex=yes) bb where aa.a=bb.a;
   /*or*/
   select * from s1 aa, x.dbtab(dbindex=a) bb where aa.a=bb.a;
   select * from s1 aa, mylib.dbtab(dbindex=a) bb where aa.a=bb.a;