Modify Materialized View Query

Change column type in a materialized view

It's not what you were hoping to get, but currently the only way to change the query on which a materialized view is based is to drop and recreate it. Still the case in Postgres 10.

ALTER MATERIALIZED VIEW can only change auxiliary properties. You can also change column names, but not data types.

If concurrent access is required and the MV takes a long time to recreate, you might create a new MV under a different name, populate it and use it instead of the old one to keep downtime to a minimum - if that's an option.

Related:

• Replace a materialized view in Postgres

Clickhouse altering materialized view's select

So it appears the way to update materialized view's select query is as follows:

1. Get path to views metadata
   
   

   SELECT metadata_path FROM system.tables WHERE name = 'request_income';

2. Use your favorite text editor to modify view's sql. In my case edited sql will look like

   ATTACH MATERIALIZED VIEW request_income (
   date Date,
   date_time DateTime,
   timestamp UInt64,
   microtime Float32,
   traceId Int64,
   host String,
   ip String,
   type String,
   service String,
   message String,
   caller String,
   context String ) ENGINE = MergeTree(date, microtime, 8192) AS SELECT
   toDate(toDateTime(timestamp)) AS date,
   toDateTime(timestamp) AS date_time,
   timestamp,
   timestamp_micro AS microtime,
   traceId,
   host,
   ip,
   type,
   service,
   message,
   caller,
   context FROM default.request_income_buffer

3. Attach modified view back

   ATTACH TABLE request_income;

Update materialized view when urderlying tables change

To take your questions in reverse order

A FAST refresh is also known as an incremental refresh. That should give you a clue as to the difference. A COMPLETE refresh rebuilds the entire MVIEW from scratch, whereas a FAST refresh applies just the changes from DML executed against the feeder table(s).

In order to do execute FAST refreshes you need the appropriate MVIEW LOG. This tracks changes to the data of the underlying tables, which allows Oracle to efficiently apply a delta to the materialized view, rather than querying the whole table.

As for the syntax, here are the basics:

SQL> create materialized view log on emp
  2  with rowid, primary key, sequence (deptno, job)
  3  including new values
  4  /

Materialized view log created.

SQL> create materialized view emp_mv
  2  refresh fast on commit
  3  as
  4  select deptno, job from emp
  5  group by deptno, job
  6  /

Materialized view created.

SQL>

The ON COMMIT clause means that the MVIEW is refreshed transactionally (as opposed to ON DEMAND which is regular refresh in bulk). The REFRESH clauses specifies whether to apply incremental or complete refreshes. There are some categories of query which force the use of COMPLETE refresh, although these seem to diminish with each new version of Oracle.

A quick test to see that it works ...

SQL> select * from emp_mv
  2  order by deptno, job
  3  /

    DEPTNO JOB
---------- ---------
        10 MANAGER
        10 PRESIDENT
        10 SALES
        20 ANALYST
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN
        40 CLERK
        40 DOGSBODY

11 rows selected.

SQL>

How about a new record?

SQL> insert into emp (empno, ename, deptno, job)
  2  values (6666, 'GADGET', 40, 'INSPECTOR')
  3  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * from emp_mv
  2  order by deptno, job
  3  /

    DEPTNO JOB
---------- ---------
        10 MANAGER
        10 PRESIDENT
        10 SALES
        20 ANALYST
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN
        40 CLERK
        40 DOGSBODY
        40 INSPECTOR

12 rows selected.

SQL>

You can find more details on the syntax in the SQL Reference. It's also worth reading the Materialized View chapter in the Data Warehousing Guide.

---

Despite the concerns of the commenters below this does work as advertised. Unfortunately the usual places for publishing demos (SQL Fiddle, db<>fiddle) do not allow materialized views. I have published something on Oracle SQL Live (free Oracle account required): I am awaiting Oracle approval for it and will update this question when it arrives.

---

Related Topics