Issue

With Oracle databases, queries against the transaction tables in the Transaction Data Repository (TDR) in SAS Fraud Management might perform slowly. The slow performance can be a caused by full-table scans on the tables when the queries do not use the existing indexes. Your database administrator can determine whether table scans are occurring on large transaction tables. If table scans are found, advise your database administrator to follow the steps described below.

Important Notes:

• Test all changes in a lower-level environment before making changes on your production system.
• Updating indexes in Oracle requires table locks. The updates should be scheduled during a maintenance window, not during peak business activity.

Workaround

By default, the indexes on the transaction tables in the TDR are created with the REVERSE option in Oracle. The REVERSE option prevents the indexes from being used by some queries. To allow the indexes to be used, update the indexes to remove this option.

1. Remove the REVERSE option from all indexes in the TDR by using one of the two available methods listed below. Depending on the size of the tables and other factors, one method might complete substantially faster than the other. Your database administrator should determine the best method for each transaction table in the TDR:

• Rebuild the index with the NOREVERSE option. Here is a sample SQL statement to rebuild an index:
  
       ALTER INDEX "TDRCORE"."CQF_X_CCCA" REBUILD NOREVERSE;
  
  
• Drop the index and then create the index with the NOREVERSE option.

2. After the indexes are rebuilt, the database administrator should monitor the time spent on the database file sequential reads. The time should be lower than before the changes were made.

The above workaround can also be used for SAS^® Fraud Management 4.4M1.