Data Set Options for Relational Databases

DBINDEX= Data Set Option

Detects and verifies that indexes exist on a DBMS table.

Default value:	DBMS-specific
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

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

Syntax Description

YES: triggers the SAS/ACCESS engine to search for all indexes on a table and return them to SAS for evaluation. If SAS/ACCESS finds a usable index, it passes the join WHERE clause to the DBMS for processing. A usable index should 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. It requires the same type of call as when DBINDEX=YES is used.

Details

If indexes exist on a DBMS table and are of the correct type, you can use this option to potentially improve performance when you are processing a join query that involves a large DBMS table and a relatively small SAS data set that is passed to the DBMS.

CAUTION:: Improper use of this option can impair performance. See Using the DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= Options 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.

You can enter the DBINDEX= option as a LIBNAME option, SAS data set option, or an option with PROC SQL. Here is the order in which the engine processes it:

DATA step or PROC SQL specification.
LIBNAME statement specification

Specifying the DBKEY= data set option takes precedence over DBINDEX=.

Examples

Here is the SAS data set that is used in these examples:

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

This 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 next 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.

This example shows how to use 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;