13.1.3 Re-evaluating the defined text indexes
This subsection describes how to check whether text indexes are efficiently used.
- Method of checking
-
Check the following output item in the access path statistical information output to the SQL trace file:
-
Data_tidx_all_search_cnt
In the preceding item, the number of all data searches is output when data in a chunk is retrieved by using text indexes. If a value no less than 1 is output to the preceding item, consider taking a corrective action.
- Note
-
For details about access path statistical information, see 10.11.3 Examples of output of and output items for access path statistical information.
-
- Action to take
-
If you take either of the following actions, performance might be improved:
-
Modifying the SQL statement so that individual retrieval results are unified by the use of UNION
-
Modifying the SQL statement so that text indexes are not used for retrieval (by specifying the index specification)
For an example of modifying the SQL statement, see (2) Checking whether text indexes are effectively used in 11.16.11 Tuning synonym search function.
If an SQL statement you specified satisfies all the following conditions, the SQL statement might be the cause of performance deterioration:
-
The LIKE predicate, LIKE_REGEX predicate, or scalar function CONTAINS is specified for a search condition.
-
Multiple search conditions in each of which the preceding predicate or scalar function is specified are specified by using logical operator OR.
-
In a search condition in which the preceding predicate or scalar function is specified, a text indexed column is specified.
An SQL statement that satisfies all the preceding conditions performs retrieval by using the search-target character strings specified in all search conditions. If there are too many search conditions, narrowing down of data by using text indexes is not performed. Instead, all data search by using text indexes is performed, so the search time might be considerably long.
In addition, when a synonym search operation is performed, internal processing unifies the search conditions (each of which has a synonym specified) by using logical operator OR such that multiple synonyms are specified. Therefore, if too many synonyms are registered in a synonym group, all data search by using text indexes is performed, instead of narrowing down data by using text indexes.
-