The Multi-Entity History database update for SAS Fraud Management 4.4M1 Hot Fix 3 (Version 5, Release 9) might be slow in Oracle. If your system is currently at 4.4M1 Hot Fix 2 (Version 5, Release 8) or earlier and you install either 4.4M1 Hot Fix 3 (Version 5, Release 9) or 4.4M1 Hot Fix 4 (Version 5, Release 10), you might encounter this performance issue.

The code that adds a new column to the FMH_V_DATA table and the FMH_Z_DATA table might not be efficient for your database configuration.  The code first adds the column. Then, it updates the column to a value. Finally, the code modifies the column to add a NOT NULL constraint.  Provided that you do not have partition compression enabled for the tables, these three steps can be handled in a single SQL statement. Such a statement is more efficient and less likely to cause contention within the database. If you have partition compression enabled, you cannot combine these steps into a single SQL. However, you still might want to manually add the columns in advance of running the hot fix installation. 

The steps to add the new columns prior to running the hot-fix installation are as follows:

1. Run this SQL statement to determine if partition compression is enabled. Substitute your schema-owner value in the statement before executing.
   select table_owner, table_name, partition_name from all_tab_partitions
          where table_owner='schema-owner' and table_name in ('FMH_Z_DATA','FMH_V_DATA')
          and compression='ENABLED';
2. If no rows are returned for FMH_Z_DATA and FMH_V_DATA, then submit these statements:
   alter table schema.fmh_z_data add (z_conv_id char(1 byte) default ' ' not null);
   
   alter table schema.fmh_v_data add (v_conv_id char(1 byte) default ' ' not null);
3. If rows are returned for both FMH_Z_DATA and FMH_V_DATA, follow either option A or option B for adding the new columns. Note: Before you run the statements in either of these options, substitute your schema name for schema.
   • Option A: Turn off compression on the tables and partitions that are compressed. Then, submit these statements:
     alter table schema.fmh_z_data add (z_conv_id  char(1 byte) default ' ' not null);
     
     alter table schema.fmh_v_data add (v_conv_id  char(1 byte) default ' ' not null);
   • Option B: Leave compression enabled.  Submit the following statements to add the new columns:
     alter table schema.fmh_z_data add (z_conv_id  char(1 byte));
     
     update schema.fmh_z_data set z_conv_id=' ';
     
     alter table schema.fmh_z_data modify (z_conv_id default ' ' not null);
     
     alter table schema.fmh_v_data add (v_conv_id  char(1 byte));
     
     update schema.fmh_v_data set v_conv_id=' ';
     
     alter table schema.fmh_v_data modify (v_conv_id default ' ' not null);
4. After the columns are added, proceed with the 4.4M1 hot-fix installation.

Operating System and Release Information