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.


|