11.16.11 Tuning synonym search function
To tune synonym search function, consider and check the descriptions here.
- Organization of this subsection
(1) Considering defining text indexes
If no text index is defined for columns in which document data is stored, consider defining text indexes. By defining text indexes, you can reduce the number of pages to be accessed, so improvement of retrieval performance is expected.
(2) Checking whether text indexes are effectively used
Check the value for Data_tidx_all_search_cnt output to the access path statistical information. When this value is no less than 1, as this value is larger, the text indexes are less effectively used. In Data_tidx_all_search_cnt, the number of all data searches is output when data in a chunk is retrieved by using text indexes.
If an SQL statement that satisfies either of the following conditions is specified, modifying the SQL statement might improve the performance of synonym search.
-
The scalar function CONTAINS for which synonym-search specification is specified, and the condition that uses the LIKE or LIKE_REGEX predicate are specified multiple times by using logical operator OR.
-
The scalar function CONTAINS for which synonym-search specification is specified, is specified multiple times by using logical operator OR.
In this case, consider unifying individual retrieval results by specifying UNION, or performing retrieval without using a text index.
The following shows an example of modifying an SQL statement.
<Before modification>
SELECT "TITLE" FROM "REPORTS"
WHERE CONTAINS("DOCUMENTS",'SYNONYM("Dictionary1","database")')>0
OR CONTAINS("DOCUMENTS",'SYNONYM("Dictionary1","application server")')>0
OR ...
<After modification>
- When UNION is used:
SELECT "TITLE" FROM "REPORTS"
WHERE CONTAINS("DOCUMENTS",'SYNONYM("Dictionary1","database")')>0
UNION DISTINCT
SELECT "TITLE" FROM "REPORTS"
WHERE CONTAINS("DOCUMENTS",'SYNONYM("Dictionary1","application server")')>0
UNION DISTINCT ...
- When no text index is used:
SELECT "TITLE" FROM "REPORTS" /*>> WITHOUT INDEX <<*/
WHERE CONTAINS("DOCUMENTS",'SYNONYM("Dictionary1","database")')>0
OR CONTAINS("DOCUMENTS",'SYNONYM("Dictionary1","application server")')>0
OR ...
(3) Checking the access time to synonym dictionary files
When synonym search is performed, the HADB server accesses synonym dictionary files. Therefore, performance improvement for synonym search is expected by creating only the directory for storing synonym dictionary files in the dedicated file system.
Check the value for Syndict_file_access_time output to the SQL statement statistical information. In Syndict_file_access_time, the time during which the HADB server is accessing the synonym dictionary files is output. If Syndict_file_access_time occupies a large portion of the time for executing SQL statements, consider creating the directory for storing synonym dictionary files in the dedicated file system.
For details about how to change the directory for storing synonym dictionary files, see 11.16.13 Changing the directory for storing synonym dictionary files.