-
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
-
Just posting, to keep it in top
-
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
A Wise Man Knows How much he doesn't know !!!
-
Originally posted by virajvk
Thanks to Julian for it !!!
For what? :-) I don't get it...
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
A Wise Man Knows How much he doesn't know !!!
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|