CODEWITHSUNDEEP
sqloracle-database
code990
code990

Reputation: 621

Modify materialized view query

I need to modify Materialized view query . Is is possible to do the same without droping and recreating it.

Upvotes: 36

Views: 106889

Answers (5)

StewS2
StewS2

Reputation: 421

This may be a new feature in a later Oracle version, but I've found this works nicely:

DROP MATERIALIZED VIEW my_mview PRESERVE TABLE;

CREATE MATERIALIZED VIEW my_mview
ON PREBUILT TABLE ...

Upvotes: 3

mcalmeida
mcalmeida

Reputation: 562

No, you cannot alter the query of a materialized view without dropping it.

The CREATE MATERIALIZED VIEW syntax does not support that feature.

The ALTER MATERIALIZED VIEW is used to modify an existing materialized view in one or more of the following ways:

  • To change its storage characteristics
  • To change its refresh method, mode, or time
  • To alter its structure so that it is a different type of materialized view
  • To enable or disable query rewrite

See Oracle 12c Release 1 Manual for:

Upvotes: 31

MaxH
MaxH

Reputation: 859

For conditions where you're not sure whether the MVIEW exists or not (which is what CREATE OR REPLACE is really good for), I use;

BEGIN
    EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW name_of_mview';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE = -12003 THEN
                dbms_output.put_line('MVIEW does not exist, which was somewhat expected');
            ELSE
                RAISE;
            END IF;
END;
/

CREATE MATERIALIZED VIEW name_of_mview ... AS SELECT ...

Upvotes: 8

erbsock
erbsock

Reputation: 1217

You can leave the mview in place. If your concern is to minimize downtime while the new mview instantiates (because you didn't use a prebuilt table), you can do the following.

  1. Create a new table called mview_1 on prebuilt table
  2. Once it has been created you can drop the old one
  3. create or replace view mview as select * from mview_1

Now, whenever you need to rebuild you will be able to do so with little to no downtime since you can simple point the view to the new table/mview going forward.

Upvotes: 5

Gary Myers
Gary Myers

Reputation: 35401

You MIGHT be able to make use of the existing MV as the source of a prebuilt table for the new MV

A lot depends on what you are doing to the query of course. If you are adding a column, for example, you'll need refresh everything to get its new value.

PS. The quick way of turning the existing MV into a table would be partition exchange but watch out for gotchas. Then you manipulate the table to match the new result set and create the new MV based on the manipulated table.

Upvotes: 0

Related Questions