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”.
