Controlling the Use of Materialized Views
TMS can use materialized views to populate the lists of values for external system columns in the Filter window of Reclassify Verbatim Terms and Approve VTAs. Using materialized views accelerates populating many of these LOVs, but in databases with a high data volume, refreshing materialized views during Synchronization can be too slow.
If the materialized views refresh during Synchronization is slowing Synchronization at your database, you have two options:
-
You can reduce the number of external system columns that rely on materialized views, switching some or all of them to regular views. To perform this switch, follow the steps in Choosing Materialized or Regular Views for Each External System Value, then create the new views by Creating Source Term Views.
-
You can remove the materialized views refresh from the Synchronization process. To de-couple this refresh from Synchronization, perform the steps in Including the Materialized Views Refresh in the Synchronization Process. If you use materialized views for any external system values, and you have made this refresh process independent of Synchronization, you must still perform the refresh periodically. See Refreshing Source Term Materialized Views.
For more information, see:
- Choosing Materialized or Regular Views for Each External System Value
- Creating Source Term Views
- Including the Materialized Views Refresh in the Synchronization Process
- Refreshing Source Term Materialized Views
Parent topic: Administration
Choosing Materialized or Regular Views for Each External System Value
The installation reference codelist TMS_SOURCE_MAT_VIEWS controls whether regular views or materialized views are used to populate the list of values for external system values in the Filter window. Each value in the codelist controls the view behavior of one external system field: for example, setting the long value of the codelist value MV1 to Y will make TMS use a materialized view to populate the list of values for the external value 1 in these Filter windows.
By default, TMS_SOURCE_MAT_VIEWS specifies that materialized views be created for external values 1 to 5, and regular views for external values 6 to 8.
Whenever any of the values in TMS_SOURCE_MAT_VIEWS are changed, you must run the Create Source Terms Views batch job. See Creating Source Term Views.
To change the view settings for an external system value:
- Open the Installation Reference Codelists window. (From the Definition menu, select Installation Reference Codelists.)
- Query for the TMS_SOURCE_MAT_VIEWS codelist.
- Update the long value for each external system value that you want to change.
- Save.
Parent topic: Controlling the Use of Materialized Views
Creating Source Term Views
Whenever you change any of the values in the TMS_SOURCE_MAT_VIEWS reference codelist, TMS must create a new view for each external system value you changed. Run the Create Source Terms Views batch job to create the materialized or regular views that your change requires.
To run this job from the user interface:
From the Definition menu, select Jobs, then choose Create Source Terms Views.
To run this job from a SQL*Plus prompt, execute the following API call:
tms_user_data_admin.configSTMatViews
Parent topic: Controlling the Use of Materialized Views
Including the Materialized Views Refresh in the Synchronization Process
You can control whether TMS refreshes materialized views during the Synchronization process by changing the reference codelist value SYNCREFRESHMV in the TMS_CONFIGURATION reference codelist. If this setting is Y, the materialized views refresh will be included in Synchronization.
If you remove the materialized views refresh from Synchronization, you must periodically run the Refresh Source Term Materialized Views batch job to refresh them manually. See Refreshing Source Term Materialized Views.
To change the inclusion status of materialized views in Synchronization:
- Open the Maintain Installation Codelists window. (From the Definition menu, select Installation Reference Codelists.)
- Query for the TMS_CONFIGURATION reference codelist.
- Scroll down to the SYNCREFRESHMV row, and change its long value setting. A long value of Y includes the materialized views refresh in Synchronization; N removes this refresh from Synchronization.
- Save. TMS will either couple or de-couple the materialized views refresh and the Synchronization process.
Parent topic: Controlling the Use of Materialized Views
Refreshing Source Term Materialized Views
If you do not refresh the materialized views as part of Synchronization, you must refresh them periodically by running the Refresh Source Term Materialized Views batch job.
To run this job from the user interface:
From the Definition menu, select Jobs, then select Refresh Source Term Mat. Views.
To run this job from a SQL*Plus prompt, execute the following API call:
tms_user_synchronization.RefreshMviews
Parent topic: Controlling the Use of Materialized Views