An
index join looks up each row of the smaller table by querying an index
of the large table. When chosen by the optimizer, an index join usually
outperforms a sort-merge join on the same data. To get the best join
performance, you should ensure that both tables have indexes created
on any columns that you want to participate in the join relationship.
The SAS SQL optimizer considers an index join when:
-
The join is an equijoin in which
tables are related by equivalence conditions on key columns.
-
Joins with multiple conditions
are connected by the AND operator.
-
The larger table has an index that
includes all the join keys.
Encourage
an index nested loop with IDXWHERE=YES as a data set option, as follows:
proc sql _method; select ... from smalltable, largetable(idxwhere=yes)
. You can also turn on the
Suggest Index Join property on the properties panes for the tables in the query.