Drop materialized view (created on prebuilt table)

Hello,

I have to change a definition of a materialized view.

For example, I have mvw like this:

CREATE MATERIALIZED VIEW my_mvw
ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT
  some_column1,
  sum(some_column2),
FROM some_table
GROUP BY some_column1;

There is materialized view log on some_table:

CREATE MATERIALIZED VIEW LOG ON some_table WITH ROWID, SEQUENCE,
(some_column1, some_column2)
INCLUDING NEW VALUES; Note: Some other materialized views depend on this log (my_mvw is not the only one), i cannot truncate the log.

And I want to change my_mvw like this (for example):

CREATE MATERIALIZED VIEW my_mvw
ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
SELECT
  some_column1,
  count(some_column2),
FROM some_table
GROUP BY some_column1;

Note: there are some indexes on my_mvw

My current approach is:

1. set indexes unusable
2. alter session set skip_unusable_indexes = true;
3. drop my_mvw (here: preexisting table reverts to its identity as a table.)
4. create my_mvw with new definition
5. complete refresh of my_mvw
6. rebuild indexes

The problem: I would like to speed up the 3rd step or find better approach to solve this problem.

I don't have much experience on materialized views, so I would like to ask for your hints and ideas.

Thank you. Received on Fri May 23 2008 - 03:33:59 CDT