Drop materialized view (created on prebuilt table)

[radino]

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.

[radino]

The database version is 9.2.0.5.0