SAS/ACCESS Software for Relational Databases: Reference

Passing Joins to the DBMS

Prior to Version 7 of the SAS System, an SQL query involving one or more DBMS tables or view descriptors was processed by the SQL procedure as if the DBMS tables were individual SAS files. For view descriptors, the SQL procedure fetched all the rows from each DBMS table and then performed the join processing within SAS.

Although the SQL Procedure Pass-Through Facility has always passed joins to the DBMS, it is now possible to pass joins to the DBMS without using Pass-Through. Beginning in Version 7, the LIBNAME engine allows you to pass joins to the DBMS without using Pass-Through but with the same performance benefits. The DBMS server will perform the join and return only the results of the join to the SAS software. This will provide a major performance enhancement for many of your programs that perform joins across tables in a single DBMS. Both inner and outer joins are supported in this new enhancement.

In this example, two large DBMS tables, TABLE1 and TABLE2, have a column named DEPTNO. An inner join of these tables is performed where the DEPTNO value in TABLE1 is equal to the DEPTNO value in TABLE2. This join will be detected by the SQL Procedure and passed by the SAS/ACCESS engine directly to the DBMS server. The resulting rows will be passed back to the SAS System.

proc sql;
select tab1.deptno, dname from
   mydblib.table1 tab1,
   mydblib.table2 tab2
where tab1.deptno=tab2.deptno
   using libname mydblib oracle user=testuser
     password=testpass path=myserver;

If you want to perform a join between a large DBMS table and a relatively small SAS data file, you may want to specify the DBKEY= data set option. The DBKEY= data set option causes the SQL Procedure to pass a WHERE clause to the DBMS so that only the rows that match the WHERE condition are retrieved from the DBMS table. Also, if DEPTNO has an ORACLE index defined on it, using DBKEY= will greatly enhance the join's performance. In this example, the DBKEY= option causes only the rows that match DEPTNO to be retrieved. Without this option, the SQL Procedure would retrieve all the rows from TABLE1.

libname mydblib oracle user=testuser 
password=testpass;
proc sql;
select tab1.deptno, loc from
   mydblib.table1 (dbkey=deptno) tab1,
   sasuser.sasds tab2
   where tab1.deptno=tab2.deptno;

For more information on this data set option, see SAS/ACCESS Data Set Options.

Chapter Contents
Previous
Next
Top of Page