Improves performance when processing a join that involves a large DBMS table and a small SAS data set.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | NO |
| Restriction: | Oracle: Use this option only when the object is a TABLE, not a VIEW. Use DBKEY= when you do not know whether the object is a TABLE. |
| Data source: | 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= data set option, MULTI_DATASRC_OPT= |
Table of Contents
specifies that SAS uses columns that have specified DBMS indexes in the WHERE clause.
specifies that SAS does not use indexes that are specified on DBMS columns.
When you process 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.
libname mydblib oracle user=myuser password=userpwd dbindex=yes;
proc sql;
select * from s1 aa, mydblib.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.