Re: Drop materialized view (created on prebuilt table)

On May 23, 10:33 am, radino <rgol..._at_gmail.com> wrote:
> 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.

The database version is 9.2.0.5.0 Received on Fri May 23 2008 - 03:51:43 CDT