Like Optimization Enhancements

ASE 12.0 changes the costing for like clauses that are not migrated into SARGs, using a technique of generating more accurate cost estimates for queries that include a leading wildcard in a like clause.

This provides better selectivity estimates, resulting in better query plans. Previously a like clause with a leading wild card was estimated to qualify all rows in the column (a selectivity of 1.0) since there was no way to search for a match. This was not the most accurate way to cost such a clause.

Example:

select ... from part, partsupp, lineitemwhere l_partkey = p_partkeyand l_partkey = ps_partkeyand p_title = ’%Topographic%’

The like string is compared with histogram cell boundaries.

A match occurs when the like term is found in a cell boundary by means of a pattern match search.

If no pattern match is found the selectivity is assumed to be 1/number of steps in the histogram. If the default of 20 cells is used, selectivity will be 0.05 if there is now pattern match in the histogram boundary values.

If a pattern match is found in the cell boundaries then the selectivity is estimated to be the sum of the weights of all cells with a pattern match.

In either case the resulting selectivity estimate will be more accurate than in previous versions.

This also applies to queries with like clauses of the type like “_abc”, or like “[ ]abc”.