Home » SQL & PL/SQL » SQL & PL/SQL » materialized view hlp (ora9)
materialized view hlp [message #301344] Wed, 20 February 2008 02:33 Go to next message
dusoo
Messages: 41
Registered: March 2007
Member
Hi, is there a way to create materialized view that will be updated every hour from a select ?

thanks
Re: materialized view hlp [message #301345 is a reply to message #301344] Wed, 20 February 2008 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2063793

Regards
Michel
Re: materialized view hlp [message #301346 is a reply to message #301345] Wed, 20 February 2008 03:12 Go to previous messageGo to next message
dusoo
Messages: 41
Registered: March 2007
Member
ok, that was kind of helpfull, but have another question Smile

Now i know to create materialized view to be refreshed every hour.
But can i fill the MV with old data and once it is filled, i will use this MV to be refreshed every hour?

the problem is, when i query my View that is created as multiple join of three other views for a specific hour, i get the result in some (ok for me) time.
But sometimes i need to query that View for a specific range of days, or just a day - and this way it takes some long minutes, that is not ok for me. That's why i was thinking to create MV that will be updated every new hour, and later on if i query the MV for a bigger range i will get the result pretty fast.

But how do u populate the MV with old data first? And i cannot populate it in one shot, cos it will take days.

thanks for any ideas !
Re: materialized view hlp [message #301350 is a reply to message #301346] Wed, 20 February 2008 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read part relative to REFRESH FAST.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#sthref6880
Refer to your version documentation, restriction and usage are not exactly the same for each version.

Regards
Michel
Re: materialized view hlp [message #301419 is a reply to message #301344] Wed, 20 February 2008 09:37 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Also read the part about "ON PREBUILT TABLE".

But beware, there were bugs last time I used this. Certain dictionary objects were not being updated correctly when materialized view was dropped, if that mview was built on an existing table. This caused problems for other features of the database, particularly TRANSPORTABLE TABLESPACES which would not allow tablespaces to be transported anymore because a correct transport set could not be created. One part of Oracle saw that a regular table was part of some materialized view and wanted the MV to be in the transportable set, but another part could not get it there because it had been dropped (hehehe stupid database).

Good luck, Kevin
Previous Topic: Order By
Next Topic: ORA-00904: Entity.Record_type_code: invalid Identifier
Goto Forum:
  


Current Time: Sat Dec 10 12:56:06 CST 2016

Total time taken to generate the page: 0.08823 seconds