How do I rebuild the indexes on the sf_event_alert and sf_event_alert_property_value tables?
In certain cases where the manager has been down for an extended period of time the sf_event_alert table may have an abnormally large number of records. Once the issue is resolved and the number of records in the table returns to a normal number again, the table and associated index will still be spread out over many data blocks.
In order to rebuild the indexes, thus reducing the number of data blocks used by the two tables the following steps can be taken. These steps should be done on a quiet MarkView system (Stop the OC4J processes and any DBMS_JOBS that are running etc.)
Rebuild indexes on sf_event_alert table:
alter table SF_EVENT_ALERT move; alter index SF_EA_EVENT_ALERT_PK rebuild; alter index SF_EA_EVENT_TYPE_ID_FK rebuild; alter index SF_EA_ALERTING_USER_ID_FK rebuild; alter index SF_EA_OPTIMAL_QUERY rebuild; exec dbms_stats.gather_table_stats('MARKVIEW','SF_EVENT_ALERT');
Rebuild indexes on sf_event_alert_property_value table:
alter table SF_EVENT_ALERT_PROPERTY_VALUE move; alter index SF_EAPV_EVENT_PROP_VALUE_PK rebuild; alter index SF_EAPV_EVENT_PROPERTY_UNQ rebuild; alter index SF_EAPV_EVENT_PROPERTY_ID_FK rebuild; exec dbms_stats.gather_table_stats('MARKVIEW','SF_EVENT_ALERT_PROPERTY_VALUE');
As always you will want to follow Industry Standard Best practices and apply these changes in a Non-Production environment before making any changes to your Production environment.
Note: The attached Validate_and_rebuild_index.sql can be run against any schema as the APPS user and may provide information on indexes that may also need to rebuilt.
Keywords: rebuild event alert how indexes