Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Oracle Server > Drop materializ...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 2 Topic 16605 of 17418
Post > Topic >>

Drop materialized view (created on prebuilt table)

by radino <rgolian@[EMAIL PROTECTED] > May 23, 2008 at 01:33 AM

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.
 




 2 Posts in Topic:
Drop materialized view (created on prebuilt table)
radino <rgolian@[EMAIL  2008-05-23 01:33:59 
Re: Drop materialized view (created on prebuilt table)
radino <rgolian@[EMAIL  2008-05-23 01:51:43 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan12V112 Sat Nov 22 14:55:50 CST 2008.