I was refering to the use of MV's in the scope of a reporting system that might not need "the most current information" so a time based refreshed would be applicable.

You would not get phantoms - you would get "stale" data "as of a point in time".

Of course if you need the most current information -- you need to use the "real tables" or a refresh on commit MV.

if you are creating the aggregates via "create table as select ..." or populating them via

truncate
disable indexes
insert /*+ APPEND */
rebuild indexes

they'll be "as fast" as an MV, since you are in effect rebuild the database.

If you have the horsepower, employ parallel processing, either in the form of parallel query OR in the form of "we'll do as much concurrent work as we can -- we'll build 5 of these aggregates at the same time"

umm, why would you do DML on it?

not possible to ROT (rule of thumb) something that is so dependent on

o size/spread of data in mv (refresh on commit for a SELECT COUNT(*) FROM T -- where everyone serializes on commit to update the count is very very different from select deptno, sum(sal) from dept where all serialize at the DEPTNO level)

o size/speed of machine

o complexity of the mv

for example

it is all about BENCHMARKING your situation (you know, *simulation*)

a query?


that is as real time as you get.

At the end of the day -- bit for bit, byte for byte -- the would be exactly the same on disk.

Where MV's are BETTER then DIY's (do it yourself's) is in the transparency.


Pretend for moment that after creating an index -- you had to go out and change application code to take advantage of it. You had to train the developes to use it directly in future code. You had to train end users to be aware of it in ad-hoc queries. Pretend further that next week you created an infinitely BETTER index and now you have to go recode, retrain, reimplement.

It would not be workable, no one would stand for it. Indexes should be transparent -- the best one used at the right times.


Well, MV's are the indexes of your data warehouse/reporting system. They should be transparent to the application. Application queries "details", system automagically rewrites queries to use summaries. Add a new summary and wa-lah, applications start taking advantage of it *transparently*. Just like an index. Additionally, just like an index -- you have dependencies set up in the data dictionary -- you can see what the MV relies on, is dependent on. You can see if it is STALE or FRESH. The system maintains it.

The advantages of MV's over DIY's are numerous -- from transparent performance benefit (no recode, no train/retrain), to maintainability, to documentation...

it is required by the fast refresh process, it is just a number, not very large. It is the way they programmed it.

it'll make the update go faster -- but the entire process would take longer .

if your goal was to make the update go faster -- that'll help.

so, basically, you need to make sure that only "N" of your database connections are active simultaneously and the other M-N connections are put on hold right?

Shared Server can be used to do that.

You can set up a pool of shared servers (say 10). At most 10 sessions can be active (and the other 40 would "wait")


OR

You can use AQ (advanced queues) and control the number of background processes that are available to process requests - your procedure would place a request in a queue, one of the "queue processing processes" you've started would pick up a request and run the stored proceudre -- placing the output in a response queue (or just a table) - which the original process can pick up.

OR

you can use the resource manager and limit the number of concurrent active sessions...

OR

you can use dbms_jobs to run the procedure (just like with AQ) and job queue processes would limit the number of procedures running...