5.2.6 Notes on searching using a text index
If you use the logical operator OR to specify multiple LIKE predicates, LIKE_REGEX predicates, or CONTAINS scalar functions that specify text indexed columns, the search is conducted using the search strings from all the conditions.
- Example: Suppose that C1 is a text indexed column.
-
SELECT * FROM "T1" WHERE "C1" LIKE 'ABCDEFG%' OR "C1" LIKE_REGEX 'XYZ[0-9]+'When this SELECT statement is executed, the search strings (ABCDEFG% and XYZ[0-9]+) specified in the conditions of the LIKE predicate and the LIKE_REGEX predicate are used in the search processing.
If the total number of characters in the pattern character strings specified in the LIKE predicates, the regular expressions in LIKE_REGEX predicates, and the search conditions in CONTAINS scalar functions exceeds 1,000 characters, search processing might slow down significantly. If the total number of characters is 1,001 or more, consider using one of the following approaches:
-
Change the SQL statement, for example by using a UNION to join the search results.
-
Use index specification or other means to conduct a search that avoids the use of a text index.
For details about the UNION statement, see Query expression in the manual HADB SQL Reference. For details about index specification, see Index specification in the manual HADB SQL Reference.