Redwood: Improve the Performance of Filters on Tables and Graphs

Oracle continuously looks at opportunities to improve planner productivity by taking advantage of technologies to speed up data retrieval from the database. This update improves the performance of tables and graphs with filters by using incremental fetch of data based on user selections.

This feature uses a new approach for handling tables or graphs with many members in the drop-down filters on the filter bar. The previous method involved creating all possible filter combinations using a Cartesian join.

For example,

  • 1,000 members in the first filter
  • 12 members in the second filter
  • 50 members in the third filter

A full Cartesian join would generate 600,000 combinations (1,000 × 12 × 50 = 600K). These combinations were stored in the server cache to avoid repeated database queries. However, constructing and caching such a large dataset was time-consuming and inefficient.

Distinct Query Optimization Approach:

In 25C, we introduce a new approach for distinct query optimization. The optimized method eliminates the need to generate and store all possible filter combinations. Instead, a distinct query is performed on the Cartesian join for each filter, considering only the relevant members from the previously selected filters. This ensures that:

  • 1,000 members are retrieved for the first filter.
  • Up to 12 members are retrieved for the second filter (based on the selected member from the first filter).
  • Up to 50 members are retrieved for the third filter (based on selections in the first and second filters).

This targeted approach significantly reduces the number of unnecessary calculations and stored results.

Benefits

  1. Reduced Data Retrieval – Eliminates the need to fetch and store all 600K combinations in the server cache.
  2. Faster Processing – Only relevant filter members are retrieved dynamically, minimizing query execution time.
  3. Improved System Efficiency – Lower memory usage and reduced server load, leading to faster response times.

By implementing these optimizations, the filters load more efficiently, significantly enhancing the user experience and reducing system overhead.

Steps to Enable

You don't need to do anything to enable this feature.

Tips And Considerations

The new approach of distinct query optimization is enabled by default.

To revert to the previous logic using a full Cartesian join, you can disable this feature by creating and setting a profile option named MSC_OPTIMIZE_TABLE_FILTER_PERFORMANCE as follows:

  1. In the Setup and Maintenance work area, go to the Manage Profile Options task in the Supply Chain Planning functional area.
  2. On the Manage Profile Options page, in the Profile Options section, click the New icon.
  3. On the Create Profile Option page, create the profile option as follows:
  • Profile Option Code: MSC_OPTIMIZE_TABLE_FILTER_PERFORMANCE
  • Profile Display Name: MSC_OPTIMIZE_TABLE_FILTER_PERFORMANCE
  • Application: Planning Common
  • Module: Planning Common
  • Start Date: current date

Leave the remaining fields blank.

  1. Click Save and Close.
  2. On the Manage Profile Options page, for the MSC_OPTIMIZE_TABLE_FILTER_PERFORMANCE profile option, in the Profile Option Levels section, select the checkboxes in the Enabled and Updatable columns for the Site and User levels.
  3. Click Save and Close.
  4. In your work area, on the Manage Planning Profile Options page, search for the MSC_OPTIMIZE_TABLE_FILTER_PERFORMANCE profile option, and set the profile value as FALSE.

You may need to work with someone who has administration privileges to make these profile-related changes.

Access Requirements

Users who are assigned a configured job role that contains these privileges can access this feature:

  • Maintain Planning Tables (MSC_MAINTAIN_PLANNING_TABLES_PRIV)

These privileges were available prior to this update.