Refresh materialize View fast on commit multiple table

You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3.

You can create a plain VIEW:

CREATE VIEW graph_avg_view AS 
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

And materialize the result once or whenever you need to start over:

CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;

(Or use the SELECT statement directly, without creating a VIEW.)
Then, depending on undisclosed details of your use case, you can DELETE / UPDATE / INSERT changes manually.

A basic DML statement with data-modifying CTEs for your table as is:

Assuming nobody else tries to write to graph_avg concurrently (reading is no problem):

WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
   )
, upd AS (
   UPDATE graph_avg t
   SET    avg_val = v.avg_val
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
-- AND    t.avg_val IS DISTINCT FROM v.avg_val  -- alt if avg_val can be NULL
   )
INSERT INTO graph_avg t  -- no target list, whole row
SELECT v.*
FROM   graph_avg_view v
WHERE  NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);

Basic recipe

• Add a timestamp column with default now() to your base table. Let's call it ts.
  • If you have updates, add a trigger to set the current timestamp with every update that changes either xaxis or value.

• Create a tiny table to remember the timestamp of your latest snapshot. Let's call it mv:

  CREATE TABLE mv (
     tbl text PRIMARY KEY
   , ts timestamp NOT NULL DEFAULT '-infinity'
  ); -- possibly more details
  

• Create this partial, multicolumn index:

  CREATE INDEX graph_mv_latest ON graph (xaxis, value)
  WHERE  ts >= '-infinity';
  

• Use the timestamp of the last snapshot as predicate in your queries to refresh the snapshot with perfect index usage.

• At the end of the transaction, drop the index and recreate it with the transaction timestamp replacing the timestamp in the index predicate (initially '-infinity'), which you also save to your table. Everything in one transaction.

• Note that the partial index is great to cover INSERT and UPDATE operations, but not DELETE. To cover that, you need to consider the entire table. It all depends on exact requirements.

The BUILD DEFERRED clause in the CREATE MATERIALIZED VIEW DDL statement causes the actual population to be deferred until the first refresh.

While Oracle parses the actual SQL used to populate the view, it does not execute it & will therefore not pick up "runtime" problems.

This is easily demonstrated.

Parsing error, due to NONSENSE not being a datatype:

SQL> CREATE MATERIALIZED VIEW PARSINGERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NONSENSE) as parsing_error
     from dual; 


    CAST ('null' AS NONSENSE) as parsing_error
                    *
ERROR at line 6:
ORA-00902: invalid datatype

SQL>

Runtime error, that won't get picked up as it doesn't analyse the actual values involved, and is unaware that the included string isn't a number:

SQL> CREATE MATERIALIZED VIEW RUNTIMEERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('notanumber' AS number) as runtime_error
     from dual;  

Materialized view created.

SQL>

SQL> begin
  2  DBMS_MVIEW.refresh('RUNTIMEERROR');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 2


SQL>