ORA-32321 MV FAST REFRESH after TRUNCATE - example

When you have (fast refreshable on commit) Materialized Views in your schema and want to truncate one or more of the underlying tables, you have a great chance of running into trouble after the truncate. This is when the ORA-32321 error usually appears:

  ORA-32321: REFRESH FAST of "..." unsupported after detail table TRUNCATE

This error occurs because the truncate operation does not record any changes in the MV log and thus a fast refresh will be out of the question for this MV. To solve this, you will have to issue a manual complete refresh on that MV. After that, fast refresh will be possible again.

So far, there is not much news. It gets interesting if you want to truncate and/or delete multiple (e.g. master-detail) tables that are the basis for an MV. When truncating a master-detail pair of tables, you basically have two choices:

1. disable the foreign key between the tables, truncate them both and re-enable the foreign key
2. truncate the detail table and after that, delete from the master table

As I have something against disabling constraints (because you must be really confident that re-enabling them again will succeed), I slightly prefer the second option.

There is a small difference in timing when the materialized views should be refreshed. In the first scenario, The MV should be completely refreshed after both tables are truncated and the constraint has been enabled again. In the second scenario, You should refresh the MV right after the truncate and again after the delete and commit of the master table. If you forget the first refresh, you will get the ORA-32321 error on commit of the delete on the master table. Should you forget the second refresh, the MV will not be refreshing at all, although you will not get an error!

In order to see this in action, I devised a simple example (see below).

1. we create a master-detail pair of tables, and give them an initial rowset. After that, an MV is created (and at the same time populated).
2. the detail table is truncated and rows from the master table are deleted. Note the timing of the complete refreshes of the MV
3. the tables are populated again in two steps, proving that the MV is populated on commit, as expected

If you want to see this in action, run the statements as a script, or use them statement-by-statement in order to follow every step along the way. In order to see what happens when you omit one of the refreshes, just try it. Leave either the first or the second (or both) of the refreshes out and spot the differences. Leaving out the first returns on obvious error. Leaving out the second can cause some trouble, as it isn't really obvious that something is wrong. Right up to the point that users start complaining, should this happen in a production environment...

Example - using manual refresh on MV's to re-enable fast refresh on commit


  --
  -- Cleanup
  --

  DROP MATERIALIZED VIEW train_min_max_times_mv;
  DROP TABLE train_times;
  DROP TABLE trains;

  --
  -- Setup for the tables
  --

  CREATE TABLE trains
  ( train_number    number(10)
  , train_date      date
  , description     varchar2(100)
  , CONSTRAINT trains_pk PRIMARY KEY (train_number) USING INDEX
  );

  CREATE TABLE train_times
  ( train_number    number(10)
  , train_time      timestamp(0)
  , description     varchar2(100)
  , CONSTRAINT train_times_pk PRIMARY KEY (train_number, train_time) USING INDEX
  , CONSTRAINT train_times_trains_fk FOREIGN KEY (train_number)
                REFERENCES trains (train_number)
  );

  INSERT INTO trains VALUES (1, sysdate, 'first train');
  INSERT INTO trains VALUES (2, sysdate, 'second train');

  INSERT INTO train_times VALUES (1, systimestamp       , 'station A');
  INSERT INTO train_times VALUES (1, systimestamp+1/1440, 'station B');
  INSERT INTO train_times VALUES (1, systimestamp+2/1440, 'station C');
  INSERT INTO train_times VALUES (1, systimestamp+3/1440, 'station D');

  INSERT INTO train_times VALUES (2, systimestamp+4/1440, 'station X');
  INSERT INTO train_times VALUES (2, systimestamp+5/1440, 'station Y');
  INSERT INTO train_times VALUES (2, systimestamp+6/1440, 'station Z');

  COMMIT;

  --
  -- Create the materialized view and view logs
  --

  CREATE MATERIALIZED VIEW LOG ON trains
    WITH SEQUENCE, ROWID (train_number, train_date)
    INCLUDING NEW VALUES;

  CREATE MATERIALIZED VIEW LOG ON train_times
    WITH SEQUENCE, ROWID (train_number, train_time)
    INCLUDING NEW VALUES;

  CREATE MATERIALIZED VIEW train_min_max_times_mv
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT tr.train_number
            , tr.train_date
            , min(tt.train_time)    min_train_time
            , max(tt.train_time)    max_train_time
            , count(*)              total_times
         FROM trains       tr
            , train_times  tt
        WHERE tt.train_number = tr.train_number
        GROUP BY tr.train_number
            , tr.train_date;

  --
  -- Truncate detail table, delete master table
  -- and make MV fast refreshable again
  --

  TRUNCATE TABLE train_times;

  exec dbms_mview.refresh('TRAIN_MIN_MAX_TIMES_MV','C');

  DELETE FROM trains;

  COMMIT;

  exec dbms_mview.refresh('TRAIN_MIN_MAX_TIMES_MV','C');

  SELECT * FROM train_min_max_times_mv;

  INSERT INTO trains VALUES (1, sysdate, 'first train');

  INSERT INTO train_times VALUES (1, systimestamp       , 'station A');
  INSERT INTO train_times VALUES (1, systimestamp+1/1440, 'station B');
  INSERT INTO train_times VALUES (1, systimestamp+2/1440, 'station C');
  INSERT INTO train_times VALUES (1, systimestamp+3/1440, 'station D');

  COMMIT;

  SELECT * FROM train_min_max_times_mv;

  INSERT INTO trains VALUES (2, sysdate, 'second train');

  INSERT INTO train_times VALUES (2, systimestamp+4/1440, 'station X');
  INSERT INTO train_times VALUES (2, systimestamp+5/1440, 'station Y');
  INSERT INTO train_times VALUES (2, systimestamp+6/1440, 'station Z');

  COMMIT;

  SELECT * FROM train_min_max_times_mv;

Example - disabling constraints when truncating

The first example can be modified to using disabling of constraints, by replacing the "truncate-refresh-delete-commit-refresh" portion in the example by the lines below.

  ALTER TABLE train_times MODIFY CONSTRAINT train_times_trains_fk DISABLE;

  TRUNCATE TABLE train_times;
  TRUNCATE TABLE trains;

  ALTER TABLE train_times MODIFY CONSTRAINT train_times_trains_fk ENABLE;

  exec dbms_mview.refresh('TRAIN_MIN_MAX_TIMES_MV','C');