Join Planner Reset Option Examples

Using JOINTECH_PREF Reset Switch to Alter an Index Join to a Hash Join

Join Planner DETAILS= Reset Switch

The following example shows use of the DETAILS reset switch on a join between two tables. In this case, table A contains an index on the join column.

proc sql;          
connect to sasspds(
   dbq='mydomain' 
   host="myhost" 
   serv="14500" 
   user='anonymous');          

execute(reset 
   details="why_join$what_join$") 
by sasspds;          

execute (create table 
  tblout as select * 
  from tablea, tableb 
where 
   a1 = a2) 
by sasspds;  

 **WHY_JOIN( 1)?: Plan an Inner Join  
**WHY_JOIN( 1)?: INDEX available on 1 tables  
**WHY_JOIN( 1)?: Index Join pass 1  
**WHY_JOIN( 1)?: Inner table [X0000001].TABLEA Index a1  
**WHY_JOIN( 1)?: Idx dup_ratio(1.00) >= indexselectivity(0.70)  
**WHY_JOIN( 1)?: Est inner rows to read via idx(100.0)
**WHY_INDX( 1)?: Good dup_ratio and inner table index is beneficial  
SPDS_NOTE: PROC SQL planner chooses indexed join.  
SPDS_NOTE: Table X0000001.TBLOUT created, with 100 rows and 4 columns.

The WHAT_JOIN$ details produce the server note that reads

PROC SQL planner chooses
                     indexed join.

. This note indicates that the index join was selected. The WHY_JOIN$ details provide information that shows that the join performed is an inner join. Table A has an index on column A1. The duplicate variable ratio on the index is favorable (as compared to the index selectivity). As a result, the index join is selected.

Using JOINTECH_PREF Reset Switch to Alter an Index Join to a Hash Join

The following example uses the reset switch JOINTECH_PREF to persuade the server to choose a hash join over an index join.

execute(reset 
  details="why_join$what_join$" 
  jtech_pref=hash) 
by sasspds;         

execute (create 
  table tblout 
  as select * 
  from tablea, tableb 
  where a1 = a2) 
by sasspds;  

**WHY_JOIN( 1)?: Plan a Inner Join  
**WHY_NIDX( 1)?: Magic=103 (jtech_pref=hash) prohibits index 
**WHY_MERG( 1)?: Index join not selected, do merge join  
**WHY_JOIN( 1)?: Magic=103 (jtech_pref=hash) skips JM table order check  
**WHY_HASH( 1)?: merge xformed to hash join, num_hashjoins=1  
SPDS_NOTE: PROC SQL planner chooses hash join.  
**WHY_HASH( 1)?: Inset optimization, hashkeys(100) le hashinsetsize(1024)  
SPDS_NOTE: Table X0000007.TBLOUT created, with 100 rows and 4 columns.

The WHAT_JOIN$ details produce the server note

PROC SQL planner chooses hash
                     join

, which indicates that the index join was selected.

The WHY_JOIN$ details indicate that the join is an inner join, and that an index join is not selected because the JTECH_PREF is set to hash. The join was successfully transformed to a hash join (implying that there was sufficient buffer size to do the hash). The hash join inset optimization was used because the number of hash keys in the smaller table (100) is less than or equal to the hash inset size limit (1024).

N-Way Join Example

When you use an n-way join, the server returns WHAT_JOIN$ and WHY_JOIN$ information for each pairwise join of the n-way join.

execute(reset
  details="why_join$what_join$" 
  _method jointech_pref=none) 
by sasspds;         

execute (create table tblout 
  as select * 
  from tablea, tableb, tablec 
  where a1 = a2 
  and a2 = a3) 
by sasspds;

**WHY_JOIN( 1)?: Plan a Inner Join  
**WHY_NIDX( 1)?: No INDEX on join column  
**WHY_MERG( 1)?: Index join not selected, do merge join  
**WHY_JOIN( 2)?: Plan a Inner Join  
**WHY_JOIN( 2)?: INDEX available on 1 tables  
**WHY_JOIN( 2)?: Index Join pass 1  
**WHY_JOIN( 2)?: Inner table [X0000010].TABLEA Index a1  
**WHY_JOIN( 2)?: Idx dup_ratio(1.00) > indexselectivity(0.70)   
**WHY_INDX( 2)?: Favorable inner table index dup_ratio 

SPDS_NOTE: PROC SQL planner chooses indexed join.  

**WHY_HASH( 1)?: merge xformed to hash join, num_hashjoins=1  

SPDS_NOTE: PROC SQL planner chooses hash join. 

**WHY_HASH( 1)?: Inset optimization, hashkeys(100) le hashinsetsize(1024)

The WHAT_JOIN$ details produce two server notes. The first note in the SAS log above reads

PROC
                     SQL planner chooses indexed join

. The second note reads

PROC
                     SQL planner chooses hash join

. These notes indicate that two pairwise joins were required for the query: an index join and a hash join.

The WHY_JOIN$ details show how each pairwise join was planned. The order of the join is indicated by the additional numeric values in the log.

WHY_JOIN(
                     1)

is the first pairwise join plan, and

WHY_JOIN(
                     2)

is the second pairwise join plan. It is a good idea to include the DETAILS="WHY_JOIN$_WHAT_JOIN$" switch in your reset command when you create an n-way join. It adds helpful information to the SAS log that enables you to easily determine which tables are involved in each pairwise join of the n-way join.

The _method information for the above join is as follows:

SPDS_NOTE: SQL execution methods chosen are:     
   sqxcrta         
     sqxjndx(2)                 
       sqxjhsh(1)                         
          sqxsrc ( [X0000010].TABLEB )                         
             sqxsrc ( [X0000010].TABLEC )                 
          sqxsrc ( [X0000010].TABLEA )

The _method information shows that TABLEB and TABLEC will be used by the sqxjhsh (hash join) method. The results of the join will be used with TABLEA for the sqxjndx (index join) method. The numeric in the join method chosen matches up with the numeric in the WHY_JOIN$ information. In other words, the sqxjhsh(1) hash join method was selected as the result of the WHY_JOIN(1) plan, and the sqxjndx(2) index join method was selected as a result of the WHY_JOIN(2) plan.

Last updated: February 8, 2017