If you specify LIKE predicates using the % wildcard character in any position other than at the end of the pattern, you should collect basic information about the sub-element structure.
As well as the wildcard LIKE predicate (for example,
DOCUMENTS WHERE KEYWORDS LIKE '%simulation%'), the columns
and the query must fit certain criteria to benefit from sub-element
In this example, each column value consists of five sub-elements, each of which is a word (the keyword), separated from the others by one blank.
'database simulation analytical business intelligence' 'simulation model fruit fly reproduction temperature' 'forestry spruce soil erosion rainfall' 'forest temperature soil precipitation fire'
The query should reference these columns in WHERE clauses.
The optimizer always estimates how many rows match each predicate. For these wildcard LIKE predicates, the optimizer assumes that the column being matched contains a series of elements concatenated together, and it estimates the length of each element based on the length of the string, excluding leading and trailing % characters. If you collect sub-element statistics, the optimizer will have information about the length of each sub-element and the delimiter. It can use this additional information to more accurately estimate how many rows will match the predicate.
To collect sub-element statistics, execute the RUNSTATS command with the LIKE STATISTICS parameter.