Oracle Materialized Views for APEX Dashboard Performance

Dashboards Need Precomputed Data

APEX dashboards that aggregate millions of rows for charts and summary cards can be painfully slow if they query base tables directly. Materialized views (MVs) precompute and store the results of complex aggregation queries, turning multi-second dashboard queries into sub-second lookups.

Creating a Dashboard Materialized View

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT d.department_name,
       p.category,
       TRUNC(o.order_date, 'MONTH') AS order_month,
       COUNT(*) AS order_count,
       SUM(oi.quantity * oi.unit_price) AS total_revenue,
       COUNT(DISTINCT o.customer_id) AS unique_customers
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  JOIN departments d ON o.department_id = d.department_id
 GROUP BY d.department_name, p.category, TRUNC(o.order_date, 'MONTH');

Your APEX dashboard cards and charts can now query mv_sales_summary directly, which returns results instantly regardless of how many underlying orders exist.

Refresh Strategies

FAST refresh applies only incremental changes since the last refresh, but requires materialized view logs on the base tables. COMPLETE refresh recomputes the entire view from scratch. For dashboards, schedule a FAST refresh every 15 minutes during business hours using DBMS_SCHEDULER:

-- Create MV logs on base tables (required for FAST refresh)
CREATE MATERIALIZED VIEW LOG ON orders WITH ROWID, SEQUENCE
    (order_id, order_date, department_id, customer_id)
    INCLUDING NEW VALUES;

-- Schedule refresh
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'REFRESH_SALES_SUMMARY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''MV_SALES_SUMMARY'',''F''); END;',
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
        enabled         => TRUE
    );
END;

Query Rewrite

With ENABLE QUERY REWRITE, the optimizer can automatically redirect queries against the base tables to the materialized view when it detects that the MV can answer the query. This means existing APEX reports may benefit from the MV without changing their source queries. However, for dashboard pages, querying the MV directly is more predictable and avoids depending on optimizer decisions.

Freshness Indicator

Dashboard users should know how fresh the data is. Query USER_MVIEWS to get the last refresh time and display it on the dashboard:

SELECT last_refresh_date
  FROM user_mviews
 WHERE mview_name = 'MV_SALES_SUMMARY';