My Oracle Support Banner

Drop/Recreate Index Procedure for Duplicate Check Impacts Performance (Doc ID 1924902.1)

Last updated on AUGUST 05, 2020

Applies to:

Oracle Communications Billing and Revenue Management - Version and later
Information in this document applies to any platform.



Dropping and recreating Index procedures for duplicate check is impacting performance.


It is an issue because right now in IFW_DUPLICATECHECK% tables, some of them have more than 1000 millions (1 billion or 1 milliard) records. Therefore dropping and recreating the indexes takes about 1 hour and 10-15 minutes.

Every day during index creation these are the issues faced by customer:

- Huge archive log activity (about 1 switch every 3 seconds).
- Data Guard (standby database) is way out of sync for a few hours until it applies all archive logs.
- Some AWR snapshots are not created because DB is working so heavily that will not have time to create them (AWR frequency is 15 minutes for BRM Production DB).

Need to have the possibility to avoid drop/recreate index. For e.g. it may be one parameter inside FCT_DuplicateCheck module that would allow customers to decide which approach they choose. Or some other improvement that will reduce impact of flushing old data into DB when StoreLimit/BufferLimit are changed.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.