The rule test query on Oracle systems might not be efficient for large transaction tables


In the Coding folder in SAS® Rules Studio, you click the Test button to test the rule logic on existing transactions in the Transaction Date Repository (TDR). On Oracle systems, the query that retrieves transactions might not perform well for transaction tables that contain millions of rows.

The Oracle SQL query first retrieves all the qualifying transactions and orders them by RQO_PROC_UTC_DATETIME. Then, the first 10,000 rows of the full set of sorted transactions are returned for display in the transaction grid on the Related Rows tab. If the number of qualifying transactions is large, this query structure is database resource-intensive and might perform poorly. (For example, this issue occurs if the qualifying transactions is in the millions.)

The query structure is as follows:

   select * from

   (  

    select …

    from FCM_XXXX

    where …

    order by RQO_PROC_UTC_DATETIME

    )

    where ROWNUM <= 10000

If your rule test does not require contiguous transactions, then you can design a more efficient query. The more efficient query structure retrieves and sorts only the first 10,000 qualifying rows.

The more efficient query structure is as follows: 

   select …

   from FCM_XXXX

   where …

   and ROWNUM <=10000

   order by RQO_PROC_UTC_DATETIME

Starting in SAS® Fraud Management 6.2 Hot Fix 4, the new rule_test_apply_rownum_before_order_by property allows the system administrator to choose the query structure for all users. You can update this property on the Preferences tab, under System Properties ► Rule Testing.

The default value for the property is TRUE. Note that this setting uses the new, more efficient query structure. Use this setting when contiguous transactions are not required to test rules.

If this property is FALSE, then the transactions in the sample are contiguous by RQO_PROC_UTC_DATETIME. Having contiguous transactions in the sample might be beneficial for accurately testing time-based rules. (For example, it might be beneficial for rules that use the %SHIFTHISTORYARRAY macro). However, this option might be costly in terms of performance. This setting matches the behavior of SAS® Fraud Management 6.2 Hot Fix 3 and earlier.