Re: "Throttling" a session's IO

So the biggest problem it seems are not the MVs themselves but the fact that these are working at least a few minutes and stealing all the system resouces for that time.

So have you investigated resouce manager? As you've mentioned 9i here is url for 9.2 docs: http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/dbrm.htm#5080

Although I haven't really used resource manager unlike the two MVs aproach that was mentioned before :)

BTW dropping and recreating indexes might be faster than maintaining them on insert. You can of course measure that yourself but for us it really was better solution.

Also if you are referring directly to MVs but not using query rewrite you use synonym just switching it from the first to second and back. It seems even if a query is running in session X you can switch it to another object (in another session) and X succesfully continues to run till the end.

Gints Plivna
http://www.gplivna.eu

--
http://www.freelists.org/webpage/oracle-l