Configure Spilling-Avoidance Rewrite

The spilling-avoidance rewrite splits large hash joins into smaller joins to avoid memory overflow.

The spilling-avoidance rewrite is configured by specifying:

• The cardinality threshold at which the rewrite will be triggered.

• The size of the smaller joins (groups) into which the larger hash join should be split.

The two engine variables used to configure these two values are:

To enable the spilling-avoidance rewrite, set the cardinality_threshold_for_join_split engine variable to a value other than UINT_MAX.

To disable the spilling-avoidance rewrite, set the cardinality_threshold_for_join_split engine variable to UINT_MAX.

Select the Engine Variable Values

SET SESSION cardinality_threshold_for_join_split_rewrite = <value>

SET SESSION target_group_size_for_join_split_rewrite = <value>

Remarks

The following apply to the spilling-avoidance rewrite:

• The tables must be columnstore tables.

• The tables must have column statistics available.

• A histogram on the high cardinality column must exist.

• The join condition on the high cardinality column must be an equality condition on two columns, for example: t1.a = t2.b.

• The join condition on the high cardinality column cannot include an inequality. For example, if the join condition is t1.a < t2.b , the join will not be rewritten.

• The join condition on the high cardinality column cannot include a constant value or expressions. For example, if the join condition is t1.a = 1,  the join will not be rewritten.

• The joined columns must be of numeric types (integer or double).

Example

Consider two tables, t1 and t2, both with an ID column of high cardinality.

Assume the cardinalities of the ID columns are as follows:

• t1.ID has distinct values (1-20M) thus has cardinality 20,000,000

• t2.ID has distinct values (1-40M) thus has cardinality 40,000,000

The following query joins tables t1 and t2 on the high-cardinality ID columns. This join is a candidate for the spilling-avoidance rewrite.

SELECT * 
FROM t2
JOIN t1
on t2.ID = t1.ID;

To enable the spilling-avoidance rewrite for this query, use the following commands.

SET SESSION cardinality_threshold_for_join_split_rewrite = 12000000;
SET SESSION target_group_size_for_join_split_rewrite = 10000000;

Setting cardinality_threshold_for_join_split_rewrite = 12000000 indicates that if a join column has a cardinality greater than 12,000,000, the original query should be rewritten into smaller joins. Both t1.ID and t2.ID have a cardinality greater than 12,000,000, so the engine will apply the spilling-avoidance rewrite to this query. Note that both columns have cardinality greater than 12,000,000, so the threshold is met.

Setting target_group_size_for_join_split_rewrite = 10000000 enables the spilling-avoidance rewrite and specifies the size of the inputs to the smaller joins (also called the groups).

With these settings, the previous query will be rewritten as follows:

(SELECT * 
 FROM t2 
 JOIN t1 
 ON t2.ID = t1.ID AND t1.ID<10000000
)

UNION ALL

(SELECT * 
 FROM t2 
 JOIN t1 
 ON t2.ID  = t1.ID AND t1.ID >= 10000001
);

The single join is rewritten into two smaller joins which are combined with a UNION ALL.

The smaller joins are created by adding a filter on the t1.ID column. The t1.ID column has 20,000,000 distinct values (a cardinality of 20,000,000). This column is used to break the query into two groups of size target_group_size_for_join_split_rewrite using the filters t1.ID < 10000000 and t1.ID >= 10000001.

The column with the smaller cardinality, t1.ID in this example, is used to create the groups.