limitations of "on prebuilt table" for creating snapshots

[srinivast]

Hello all,

is there any limitation on using "on prebuilt table" option while creating the materialized view. If I create the materialized view with out this, it is fetching the data, if I use this it is not fetching the data. Can some body explain this.

Thanks in advance
--Srinivas

[srinivast]

Just posting, to keep it in top

[virajvk]

dear sir ,

Limitations I am not knowing them.

But prebuilt option saved me once when I renamed my source table and then drop that table itself and Materialized view was in hanging stage.

Thanks to Julian for it !!!

bye
viraj

[julian]

Originally posted by virajvk

Thanks to Julian for it !!!

For what? :-) I don't get it...

[virajvk]

dear Julian sir,

Long back i had problem droping a oracle user becaz a single snapshot was not getting dropped.
This happened becaz I renamed the table on which snapshot was created and then I dropped the table.
Then snapshot dropping was not taking place saying object does not exist.

My senior dba asked question related to this and u suggested create snapshot with pre buit option whch we implemented
and was successful

so Thanks to Julian


bye
viraj

[denevge]

About the original question :

Do not forget to fill the table with data before you create the MV.

Example

create materialized view T_MV
...
as select id,sum(amount) as s_a
from sales
group by id

equals

create table T_MV
as select is,sum(amount) as s_a
from sales
group by id
/
create materialized view T_MV on prebuilt table
...
as select id,sum(amount) as s_a
from sales
group by id
/

Limitation :
The table you use must have the same name as the MV.

Wrong example

create table T_MV ( id number, s_a number )
/
create materialized view T_MV on prebuilt table
...
as select id,sum(amount) as s_a
from sales
group by id
/

remark

If the materialized view is dropped, the preexisting table reverts to its identity as a table.



Hope this helps
Gert