Home » SQL & PL/SQL » SQL & PL/SQL » Need help in materialized views/snapshot
Need help in materialized views/snapshot [message #187797] Tue, 15 August 2006 12:19 Go to next message
Messages: 38
Registered: June 2006
Hi ,

I am having a materialized view/snapshot which contains a complex query as select stmt.So,if in any of my tables data gets inserted the view should be updated automatically but this is not happening with my view.What could be the problem ??

Here is the sample stmt which i am using to create a materialized view :

CREATE MATERIALIZED VIEW vw_store ENABLE QUERY REWRITE AS SELECT code,name,l.state_id,state_name
FROM table1 l ,table2 lps
WHERE l.state_id = lps.state_id

I am using enable query rewrite ,but once the data is inserted into the main tables the view is not getting updated.I am jst wondering what is happening.

I have also used options like Fast refresh,complete refresh etc but couldn't get..

At the same time what could be the best way to achieve my requirement.

Please help me out.

Thanks in Advance,
Re: Need help in materialized views/snapshot [message #187804 is a reply to message #187797] Tue, 15 August 2006 13:41 Go to previous message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In order to enable fast (incremental) refresh, you have to create a materialized view log. Did you do that? Also, did you try to use "REFRESH FAST ON COMMIT"?

Here is documentation about how to create a materialized view, and here about creating a materialized view log.
Previous Topic: simple plsql
Next Topic: how to call the below function in select query
Goto Forum:

Current Time: Tue Aug 22 15:44:35 CDT 2017

Total time taken to generate the page: 0.10346 seconds