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
grk28
Messages: 38
Registered: June 2006
Member
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,
GRK
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
Littlefoot
Messages: 20897
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: Struggeling on query......
Next Topic: ORA-03120 ORA-03120 two-task conversion routine: integer overflow
Goto Forum:
  


Current Time: Wed Dec 07 02:52:14 CST 2016

Total time taken to generate the page: 0.05951 seconds