Materialized View vs. Plain Old Table

We have a set of reports that have to run on live or "nearly live" data in our OLTP system. Nearly live means <1 minute lag time. In order to improve performance we are considering two options: A) Using join-style Materialized Views (MV) B) Maintaining a flat table to support the reports

Which one we choose probably depends on some technical issues: 1) Can we build indexes on a materialized view (esp bitmap indexes)? 2) How do MV's handle fast refresh on commit: does the transaction wait for the MV to be refreshed or just for the MV logs? 3) How do MV's interact when SQL with embedded PL/SQL functions are involved? Can we put those in the MV SQL or should we avoid this? 4) If the SQL in a report requires more data elements than those that are supplied by the MV, does the MV join like an ordinary table?

In option B, we are considering using advanced queueing so that the update to the flat table will happen aynchronously after the message enqueue trigger fires. The dequeuing would be done by separate processes that are 100% dedicated to maintaining the table. It's extra work to develop the synchronization mechanism that MV functionality provides as a built-in, but having a true table and having the refresh happen asynchronously both seem somewhat valueable. Is this a sensible approach? Received on Wed Mar 06 2002 - 23:14:41 CST