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: | NO [Greenplum, Impala, Microsoft SQL Server, SAP HANA] |
| LIBNAME option value [Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, HAWQ, Informix, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP IQ, Teradata, Vertica, Yellowbrick] | |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | DBINDEX= LIBNAME option, DBKEY= data set option, MULTI_DATASRC_OPT= LIBNAME option |
| CAUTION |
Improper use of this option can impair performance. See MULTI_DATASRC_OPT= for detailed information about using this option. |
Table of Contents
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.
indicates that no automated index search is performed.
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.
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. Performance is often improved for queries that involve a large DBMS table and a relatively small SAS data set that is passed to the DBMS.
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:
Specifying DBKEY= takes precedence over DBINDEX=.
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;data a;
set s1;
set mydblib.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.
proc sql;
select * from s1 aa, mydblib.dbtab(dbindex=yes) bb where aa.a=bb.a;
select * from s1 aa, mydblib.dbtab(dbindex=yes) bb where aa.a=bb.a;
/*or*/
select * from s1 aa, mydblib.dbtab(dbindex=a) bb where aa.a=bb.a;
select * from s1 aa, mydblib.dbtab(dbindex=a) bb where aa.a=bb.a;