Default TEXT Index Creation Script

This section introduces the default TEXT index creation script.

Location of Default TEXT Index Creation Script

The default TEXT index creation script can be found at the following path on an instance of PPM 9.13 or later: 

<PPM_Home>/utilities/database_dms/DatabaseDMS_FullTextSearch_Scripts.zip

The zip package contains two files:

  • The script to run as SYS user to give PPM DB User the required grants to create TEXT indexes.

  • The default index creation script. You may want to customize this script to meet your requirements.

Back to top

TEXT Index Creation Parameters

The first parameter that you may want to update is the lexer used when indexing documents. By default, WORLD_LEXER is used as it supports a wide range of languages (including all languages supported by PPM). For more choices of lexer, see the Oracle documentation.

The other parameter that you may want to modify when creating indexes is the delay between TEXT indexes refresh. This is set in the "SYNC (every sysdate+XXX)" of the index creation SQL, where XXX is the average duration between two index refreshes expressed in days. So, using "T/1440" allows you to easily express duration T in minutes.

The default script creates indexes that are refreshed every 10 minutes [SYNC (every sysdate+1/144)]. In other words, when users add a document to PPM, they may have to wait up to 10 minutes before the document can appear in the search results.

Note: Setting this value to a short delay allows users to see documents shortly after adding the documents in PPM, but can result in index fragmentation on the long run. Fragmented indexes result in slower searches. This can be solved by optimizing the index or dropping and re-creating the index. For more information, see the Maintaining TEXT Indexes section below.

If you do not want to wait for a document to be indexed after adding it to PPM, you can use the SYNC (ON COMMIT)option. However, note that when doing so, there is a noticeable performance impact as the document indexing is done as part of the transaction. Moreover, this results in a very fragmented index on the long run, requiring frequent optimizations, and possibly indexes full rebuilds.

Note: If you choose the SYNC (ON COMMIT) option for all your TEXT indexes, the PPM DB User does not need the "CREATE JOB" access grant. Only CTXAPP is required.

Back to top