Well there were two issues that came up after I had a series of materialized views in production.
During a refresh the table is actually empty. Meaning if you did a select against the table, you got 0 rows returned. Some of my views took a substantial amount of time to refresh (querying 500k+ of rows). It seems like internally the views are working in the following process: 1. Delete all records. 2. Commit. 3. Insert from a select. 4. Commit. With a home grown solution atleast you can remove the commit from between delete and insert, so there is always data in the tables. Oracle infact told me that I wasn't using the views for thier intended purpose. That the materialized views should not have any SQL run against them during a refresh. The example given was at midnight you refresh the view to aggregate your daily sales numbers. Then in the morning when the accountants come to work they can run reports against the view, but never would they run reports at 12:30AM while the view was refreshing. In my scenario I do alot of caching on the web server so I just added a catch that if no records were returned the cache wouldnt be updated. What made me get rid of them all together was when they started crashing the database. Every once in a awhile, with no rhyme or reason, the materialized view would become invalid. The only way to bring the view back online was to drop it and recreate it. 2 times that database just locked up as a result. For some reason the jobs which run the refresh wouldnt invalidate (by default after 3 errors they should disable themselves). Once I converted everything to a home-grown solution all the problems disappeared and the system runs like a champ. Oracle's explanation was as typical as ever... they don't see any of these errors in 10g, so I should upgrade. (I'm running 9i for this project). This is what 100K+ of support each year from Oracle with buy you. -Adam On 9/1/05, Tanguy Rademakers <[EMAIL PROTECTED]> wrote: > > Because there are alot of bugs in how Oracle implements materialized > > views. They are extremely unstable and unpredictable. For instance, > > try querying a materialized view while it's in the middle of refresh. > > > > btw. Ian, in Oracle 8 they were called snapshots. > > I've used materialized views with great succes for over a year now - > admittedly on 9i and not 8i(i actually switched from a home grown solution > much like the one you described earlier). I use a "on commit refresh fast" > model - there aren't that many updates but there are a LOT of queries on the > mv - and i haven't seen any problems so far. Have i just been lucky? > > /t > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217203 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
