DBINDEX= Data Set Option

Detects and verifies that indexes exist on a DBMS table.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default:	DBMS-specific
Data source:	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
See:	DBINDEX= LIBNAME option, DBKEY= data set option, MULTI_DATASRC_OPT= LIBNAME option

Syntax

Details

Examples

Example 1

Example 2: Use DBINDEX= in a LIBNAME Statement

Example 3: Use DBINDEX= in a SAS DATA Step

Example 4: Use DBINDEX= in PROC SQL

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: indicates that 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

Example 1

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;

Example 2: Use DBINDEX= in a 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 that 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 3: Use DBINDEX= in a 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, a pass-down occurs. Otherwise, the join takes place in SAS.

Example 4: 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;